搜尋本站文章

2009-05-31

啟用 SQL Server 2008 遠端連線(Enable Remote Connection)


11_SSMS連線錯誤

12_VS連線錯誤



以 SQL Server 2008 的 Express、企業版180 天評估版(Enterprise Evaluation)、Developer Edition 為例,依據預設值,是停用「遠端連線(disable Remote Connection)」。

所以,應用程式也是無法使用 TCP/IP 的方式來與 SQL Server 進行連線。
若要能以 TCP/IP 方式來連線,就須需要設定啟用遠端連線(Enable Remote Connection)。




方法(一)

若您的環境是將 SQL Server 安裝為「具名執行個體」,例如,要與 SQL Server Express 連線字串之格式為:ComputerName\SQLEXPRESS,這類的連線字串,就表示此 SQL Server 是安裝為「具名執行個體(Named Instance)」,則其啟用  TCP/IP 通訊協定的方式如下:

工作1. 啟用 TCP/IP 通訊協定

1. 執行「開始」\「所有程式」\「Microsoft SQL Server 2008」\「組態工具」\「SQL Server 組態管理員」。

2. 在「SQL Server Configuration Manager」視窗,執行以下的選項:
在左邊窗格,點選「SQL Server 網路組態」。
點選「SQLEXPRESS 的通訊協定」。


01_選擇「SQL Server 網路組態」

在右邊的窗格,在「通訊協定名稱」方塊下,選擇「TCP/IP」。
滑鼠右鍵,選擇「啟用」。


02_啟用 TCPIP

在「警告」視窗,點選「確定」。

03_警告視窗

3. 重新啟動 SQL Server。

工作2. 啟動「SQL Server Browser」服務

1. 在「SQL Server Configuration Manager」視窗,執行以下的選項:
在左邊窗格,點選「SQL Server 服務」。
在右邊窗格,點選「SQL Server Browser」。
滑鼠右鍵,選擇「啟動」。


建議,可以設定「SQL Server Browser」服務為「自動」啟動模式。
完成上述的工作,就可以利用 TCP/IP 方式來與 SQL Server Express 進行遠端連線。




方法(二)

若不想啟動「SQL Server Browser」服務,可以改用以下的方式:

工作1. 指定此具名執行個體的「TCP/IP」通訊協定所使用之通訊埠  

1. 在「SQL Server Configuration Manager」視窗,執行以下的選項:
在左邊窗格,點選「SQL Server 網路組態」。
點選「SQLEXPRESS 的通訊協定」。 


在右邊的窗格,在「通訊協定名稱」方塊下,選擇「TCP/IP」。
滑鼠右鍵,選擇「內容」。


2. 在「TCP/IP 內容」視窗,執行以下的選項:
點選「IP 位址」頁面。 


在「IPALL」區域,清空「TCP 動態通訊埠」方塊。
在「TCP 通訊埠」方塊,填入指定的通訊埠編號。


04_設定通訊埠 

點選「確定」。

3. 在「警告」視窗,點選「確定」。
4. 重新啟動 SQL Server。

工作2. 修改連線字串,以 SSMS 管理工具、Visual Studio 應用程式為例

因為 TCP 通訊埠 1433 是內定通訊埠而且可省略,所以在與預設執行個體連線時,多半是省略輸入通訊埠的編號。

但若是要與非 1433 通訊埠的執行個體來連線時,請使用以下格式:
「伺服器名稱」加上「逗號」加上「通訊埠編號」


例如:
192.168.1.88\SQLEXPRESS,50001

以下為 SSMS 管理工具的連線字串之圖示:

05_SSMS

06_物件總管

以下為在 Visual Studio 內設定連線字串之圖示:

07_修改連接

08_進階屬性



若是不想修改連線字串(加上「逗號」與「通訊埠編號」),請啟用「SQL Server Browser」服務。




若您的環境是將 SQL Server 安裝為「預設執行個體(Default Instance)」,也就是 SQL Server 的伺服器與作業系統的名稱相同時,其啟用遠端連線的方法如下:

請參考前述的「工作1. 啟用 TCP/IP 通訊協定」。 

若使用的是預設 TCP/IP 通訊埠 1433,則無需啟用「SQL Server Browser」服務,也無須修改連線字串。





SQL Server Browser 完成下列動作:
  • 瀏覽可用伺服器的清單
  • 連接到正確的伺服器執行個體
  • 連接到專用管理員連接 (DAC) 端點
如果 SQL Server Browser 服務未執行,但您提供正確的通訊埠編號或具名管道,則仍然可以連接到 SQL Server。 

例如,您可以使用在通訊埠 1433 執行的 TCP/IP,來連接到 SQL Server 的預設執行個體。


在啟動時,SQL Server Browser 會啟動並要求 UDP 通訊埠 1434。
SQL Server Browser 會讀取登錄項目、識別電腦上的所有 SQL Server 執行個體,並記下它們使用的通訊埠與具名管道。

當伺服器擁有兩張或多張網路卡時,SQL Server Browser 會傳回 SQL Server 所遇到的第一個已啟用連接埠。SQL Server Browser 支援 ipv6 和 ipv4。

當 SQL Server 用戶端要求 SQL Server 資源時,用戶端網路程式庫會使用通訊埠 1434 傳送 UDP 訊息到伺服器。

SQL Server Browser 回應要求之執行個體的 TCP/IP 通訊埠或具名管道。
於是,用戶端應用程式上的網路程式庫會使用所要的執行個體的通訊埠或具名管道,將要求傳送至伺服器來完成連接。

如果 SQL Server Browser 服務未執行,則下列連接沒有作用:
  • 任何嘗試連接到具名執行個體卻未完整指定所有參數 (例如 TCP/IP 通訊埠或具名管道) 的元件。
  • 任何產生或傳遞伺服器/執行個體資訊的元件,稍後要重新連接的其他元件可使用此資訊。
  • 連接到具名執行個體但未提供通訊埠編號或管道。
  • 具名執行個體或預設執行個體 (若未使用 TCP/IP 通訊埠 1433) 的 DAC。
  • OLAP 重新導向程式服務。
  • 列舉 SQL Server Management Studio、Enterprise Manager 或 Query Analyzer 中的伺服器。
如果您停止或停用 SQL Server Browser 服務,則必須指派特定通訊埠編號給每一個執行個體,並將用戶端應用程式碼撰寫為永遠使用此通訊埠編號。 

此方式有下列問題:

  • 您必須更新或維護用戶端應用程式碼,才能確保它是連接到正確的通訊埠。
  • 您為每個執行個體選取的通訊埠可能正由該伺服器上的其他服務或應用程式使用中,導致 SQL Server 執行個體無法使用。


若您使用的版本為 SQL Server 2005,請參考以下的文件啟用遠端連接:

如何將 SQL Server 2005 設定為允許遠端連接 http://support.microsoft.com/default.aspx/kb/914277
在 SQL Server 2005 可以搭配「SQL Server 介面區組態」管理工具,來啟用遠端連線(Enable Remote Connection) 。

但在 SQL Server 2008 並沒有「SQL Server 介面區組態」管理工具。





本文未包含防火牆的設定之解說,請參考以下的文件:
將 Windows 防火牆設定成允許 SQL Server 存取 http://msdn.microsoft.com/zh-tw/library/cc646023.aspx



參考資料: 

SQL Server Browser 服務
http://msdn.microsoft.com/zh-tw/library/ms181087.aspx

SQL Server 組態管理員 http://msdn.microsoft.com/zh-tw/library/ms174212.aspx

教學課程:Database Engine 使用者入門 http://msdn.microsoft.com/zh-tw/library/ms345318.aspx

從另一部電腦連接到 Database Engine http://msdn.microsoft.com/zh-tw/library/ms345299.aspx

如何:設定伺服器接聽特定 TCP 通訊埠 (SQL Server 組態管理員) http://msdn.microsoft.com/zh-tw/library/ms177440.aspx

將 Windows 防火牆設定成允許 SQL Server 存取 http://msdn.microsoft.com/zh-tw/library/cc646023.aspx

如何將 SQL Server 2005 設定為允許遠端連接 http://support.microsoft.com/default.aspx/kb/914277

10 則留言:

  1. 陳老師好
    請問一下是否需要將「Facet 屬性」中的「RemoteAccessEnabled」設定為 True呢?
    看了幾篇在google找的資料都有寫要為True

    回覆刪除
  2. 您好:
    關於您所描述的問題:「是否需要將「Facet 屬性」中的「RemoteAccessEnabled」設定為 True呢?」

    筆者的想法是:
    「RemoteAccessEnabled」這項屬性,依據SQL Server 2008 線上叢書 (2009 年 1 月)的解釋,這應該是控制本機或遠端伺服器 (Microsoft SQL Server 執行個體的執行所在) 上執行的預存程序。將 remote access 設定為 1 (預設值) 可以授與權限以從遠端伺服器執行本機預存程序,或從本機伺服器執行遠端預存程序。將此選項設定為 0 可防止在遠端伺服器上執行本機預存程序,或在本機伺服器上執行遠端預存程序。

    remote access 只適用於使用 sp_addserver 新增的伺服器,而且是因為回溯相容幸而包含。

    下一版的 Microsoft SQL Server 將不再提供此功能。請勿在新的開發工作中使用此功能,並且儘速修改使用此功能的應用程式。 請使用 sp_addlinkedserver 來取代。

    參考文件:
    remote access 選項
    http://msdn.microsoft.com/zh-tw/library/ms187660.aspx

    回覆刪除
  3. SamChang1/6/09 12:20

    感謝您無私的分享
    讓我等級再提昇+1
    被google找到資料誤導,浪費時間啦

    回覆刪除
  4. 筆者在撰文時會盡量提供相關的參考文件,且以該軟體的官方文件為主。
    建議您可以多加參考,謝謝。

    回覆刪除
  5. 被騙+1,等級下降 - 10,網路上錯誤的資訊真是駭人
    看老師的文章,等級 +1,謝謝啦

    回覆刪除
  6. 老師, 請問sql server 2008 Express 可唔可以做到兩地的同步復制的?

    ps : 我想將兩邊既數據同步復制. 中間系用vpn連接的.

    回覆刪除
  7. 各位老師/高手,

    我有兩臺SERVER, 一臺是裝WINDOWS SERVER 2003, 一臺是裝WINDOWS SERVER2008的, 兩臺SERVER是通過VPN實際資源共用的. 現在兩邊都裝了 Microsoft SQL Server 2008 Express, 如果將兩邊的數據同步復制?

    請各位老師, 高手幫幫忙. 小弟感激不盡~~

    回覆刪除
  8. 謝謝你!這個文章真是幫助我太多了!讓我學到一招。我可以把你的文章轉回我的部落格嗎?

    回覆刪除
  9. 目前想利用JDBC進行連接MSSQL
    參考了MSSQL SERVER JDBC 驅動程式的說明頁
    於是以下是我的程式碼:
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    String url = "jdbc:sqlserver://140.124.181.233;" +
    "databaseName=GRANDEE;user=NTUT\\CSIE;password=\"\";";
    connms = DriverManager.getConnection(url);
    我的MSSQL資料庫名稱為GRANDEE
    使用者為:NTUT\CSIE,沒有密碼,是使用WINDOWS驗證

    但是執行後最出現以下錯誤
    com.microsoft.sqlserver.jdbc.SQLServerException: 使用者 'NTUT\CSIE' 的登入失敗。
    請問是哪邊出了問題呢?

    回覆刪除