從遠端連接 SQL Server 2012(含 Express 版) 之 FAQ

本文將介紹從遠端連接 SQL Server 2012(含 Express 版) 常見問題的可能解決方式。

以下說明以 SQL Server 2012(含 Express 版)為例,就不再贅述版本名稱。

  • 為何只能使用 Windows 帳號連接 SQL Server?

預設 SQL Server(含 Express)安裝後採 Windows 驗證模式,要使用 SQL Server 帳戶登入,必須先將驗證模式改為混合模式

假設您在安裝的過程當中錯過了設定驗證模式(如下圖)。

 

image

 

您可以從【Object Explorer > Instance > 滑鼠右鍵 > Propertites】開啟 SQL Server 屬性視窗,點選【Security】頁籤,將驗證模式設定為【SQL Server and Windows Authentication Mode】後重新啟動 SQL Server 服務(如下圖)。


imageimage


或是執行下列的 T-SQL 來設定混合驗證。
 

   1:  USE [master]
   2:  GO
   3:  EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
   4:  , N'Software\Microsoft\MSSQLServer\MSSQLServer'
   5:  , N'LoginMode', REG_DWORD, 2
   6:  GO

  • 為何已經改成混合模式,仍無法使用 sa 登入 SQL Server?

sa 是 SQL Server 的特殊帳戶,其擁有 SQL Server 中完整的權限,因此在 SQL Server 安裝後預設停用該帳戶(如下圖)。


image

建議您在啟用 sa 帳戶時先將密碼設定為強式密碼,接著依照下列步驟來啟用 sa 帳戶:

   1:  USE [master]
   2:  GO
   3:   
   4:  ALTER LOGIN [sa] WITH PASSWORD=N'P@ssw0rd'
   5:  GO
   6:   
   7:  ALTER LOGIN [sa] ENABLE
   8:  GO
  1. 在【Object Explorer > Instance > Seecurity > Logins > sa > 滑鼠右鍵 > Properties】開啟【Login Properties】視窗(如下圖)。


    image
  2. 於 General 頁籤中輸入 Password 和 Confirm Password。

    image
  3. 點選 Status 頁籤後設定 Login 為 Enabled

    image
  4. 或是您也可以利用下列 T-SQL 來啟用 sa 帳戶。

USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'P@ssw0rd'
GO
ALTER LOGIN [sa] ENABLE
GO

  1. 啟用成功後您將會發現 sa 帳戶旁的向下箭頭消失,代表啟用成功。

    image

  • 為何只能從本機連接 SQL Server?

預設 SQL Server 的網路組態只啟用 Shared Memory(如下圖)。

image

 

要透過遠端連接 SQL Server,必須啟用 TCP/IP 通訊協定,以 SQL Server Express 為例,設定步驟如下:

  1. 開啟 SQL Server 組態管理員。

    image
  2. 展開【 SQL Server 組態管理員(本機)> SQL Server 網路組態 > SQLEXPRESS 的通訊協定 】於右側 TCP / IP 項目上按右鍵選擇啟用

    image
  3. 重新啟動 SQL Server Express 服務。

    image
  • 為何我的 SQL Server Express 已經啟用 TCP/IP 通訊協定,仍無法從遠端連接?

若您已經啟用 SQL Server Express 的 TCP/IP 通訊協定(如下圖)。


image

仍無法從遠端連接 SQL Server Express,由於預設 SQL Server Express 使用動態連接埠(如下圖)。

image


因此您在遠端嘗試利用下圖的方式來連接 SQL Server Express,由於未指定連接埠預設會嘗試連接 1433 PORT,而 SQL Server Express 預設又是動態連接埠(以上圖為例,使用 49277 PORT),因而造成無法順利連接。

image

解決方式有二,說明如下:

  1. 啟動 SQL Server Browser:讓 SQL Server 來接聽所有具名執行個體已經相對應連接埠。

    image
  2. 指定 SQL Server Express 使用 1433 PORT。
    • 開啟 SQL Server 組態管理員。

      image
    • 展開【SQL Server 組態管理員(本機)> SQL Server 網路組態 > SQLEXPRESS 的通訊協定 】於右側 TCP / IP 項目上按右鍵選擇內容

      image
    • 切換至 IP 位址頁籤,將 TCP 動態通訊埠設為空白(下圖3處),表示停用 TCP 動態通訊埠,並設定 TCP 通訊埠設為 1433(下圖4處)。

      螢幕截圖00158
    • 重新啟動 SQL Server 服務。
    • 接著您可以選擇使用下列任何一種方式連上 SQL Server EXPRESS。

      imageimage

  • 如何只針對 SQL Server Database Engine 通過防火牆?

以 Windows Server 2008 R2 為例,預設只開放某些特定的通訊埠,當您安裝 SQL Server 後,即使啟用 TCP/IP 通訊協定,若仍無法由遠端連接,可以嘗試設定具有進階安全性的 Windows 防火牆,步驟如下:

  1. 開啟具有進階安全性的 Windows 防火牆

    image
  2. 於【本機電腦上具有進階安全性的 Windows 防火牆 > 輸入規則 > 按滑鼠右鍵 > 新增規則】

    image
  3. 於【新增輸入規則精靈】視窗中選擇【程式】後按下一步。

    image
  4. 選擇【這個程式路徑】後按瀏覽來尋找 SQL Server 執行檔的路徑,依照預設 SQL Server 2012 路徑為【C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe】,SQL Server 2012 Express 則為【C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\sqlservr.exe】,輸入完畢之後按下一步。

    image
  5. 選擇【允許連線】後按下一步。

    image
  6. 選擇套用規則到【網域、私人或公用位置】後按下一步。

    螢幕截圖00172
  7. 輸入名稱後按完成。

    image

  • 如何使用非 SQL Server 預設的 1433 連接埠連接 SQL Server?

預設 SQL Server 預設執行個體透過 TCP 1433 PORT 來接受要求,若您為了安全性或其他原因想要改成其他 PORT ,可以依照下列步驟進行:

  1. 開啟 SQL Server 組態管理員。

    image
  2. 展開【 SQL Server 組態管理員(本機)> SQL Server 網路組態 > MSSQLSERVER 的通訊協定 】於右側 TCP / IP 項目上按右鍵選擇【內容】,於【TCP/IP 內容】視窗中將【已啟用】改為【】。

    image
  3. 將 SQL Server 伺服器對外的 IP 位址(下圖以 IP2 為例),設定【TCP 動態通訊埠】為空白、【TCP 通訊埠】本文以設定 8080為例、【已啟用】設定為【】。

    image
     
  4. 重新啟動 SQL Server 服務後,您就可以使用下圖的方式連接 SQL Server。

    image