任何一個比較大一點的系統都會需要使用到SQL，而以.Net開發為主最長用的資料庫基本上都是MSSQL。
不過MSSQL不一定是我們裝的，因此常遇到的問題就是給的資訊不夠完整，或者裝的時候缺少一些步奏，導致花費很多時間在找出為什麼MSSQL連線不到。
這篇希望提供一個故障排除指南，方便未來如果又連不到的時候可以依照這個項目來排除問題。
排除清單 - TL;DR
以下清單提供一步一步的排除步奏，基本上如果都做完之後連線基本上沒有什麼問題。
| 檢查項目 | 檢查方式 | 是否通過？ |
|---|---|---|
| 檢查服務是否有啟動？ | 透過Sql Server Configuration Manager -> Sql Server Services 確認狀態是Running。 | |
| 確定連線的DB Server Name | 有兩種Instance名稱：
| |
| 用local檢查tcp是否有通 | 使用Sql Server Management Studio（以下簡稱SSMS）連線的時候，在 例如：假設Server Name是 如果連線不到， | |
| 用local檢查Database的port | 可以先透過local連線過去（不要用tcp模式），然後檢查log看看目前在聽那個port
如果要調整port可以透過： 如果不是 | |
| 用local檢查sql帳號登入是否通過 | 有幾個部分可以檢查：
| |
| 用remote測試連線 - 防火墻 | 使用遠端要連到db的那台測試 - 可以使用 注意防火墻設定，要開啟上面找到的port |
排除問題的詳細步奏
接下來將會對於每一個步奏會有更加詳細的介紹。
檢查服務是否有啟動？
從開始裡面找到安裝的SQL Server版本，展開之後選擇Configuration Tools ->Sql Server Configuration Manager
從左邊選取SQL Server Services，然後檢查右邊SQL Server的狀態處於Running。
確定連線的DB Server Name
需要先確認DB的Instance名稱。
從上一個步奏確認服務是否有啟動的部分，可以看到在括弧裡面的就是DB的Instance名稱。以剛剛那個為例子，就是SQLEXPRESS。
Instance名稱有分為2種：
- Default Instance - MSSQLSERVER 是Default Instance
- Named Instance - 其他設定都是Named Instance
如果是Default Instance，那麼連線的DB Server Name就是{電腦名稱}，如果是Named Instance，那麼就是{電腦名稱}\{Instance名稱}
以剛剛那個instance為例，因為它是Named Instance，因此它的Server Name是localhost\SQLEXPRESS
Network:用local檢查tcp是否有通
當上面步奏連線成功的時候，走的是Shared Memory的通道，因此不表示tcp連線（因為從另外一台連過來走的通道）是可以通。
為了避免其他network的問題，可以先在local（DB在的那台）測試tcp通道是否有問題。
做法很簡單，就是把上一步取得的DB Server Name的前面加上tcp:即可，因此最後的結果是：tcp:localhost\SQLEXPRESS
如果連線失敗，用Sql Server Configuration Manager -> Sql Server Network Configuration 裡面的 Protocols for {instance name}
確定TCP/IP處於Enabled的狀態。
Network: 用local檢查Database的port
如果上一個步奏TCP/IP已經是Enabled的情況但是還是連不到，檢查一下port是否正確。
預設port是1433，如果DB不在預設port，那麼連線的時候需要打上port號。
先用local的方式連到DB（不要加上tcp的部分）去檢查log看看目前在聽那個port：
- 選擇
Management - 選擇
SQL Server Log - 選擇最新的一筆
- 從跳出的畫面選擇
Filter - 在
Message Contains Text輸入：server is listening on - 勾起來
Apply Filter - 選擇ok
- 檢查結果，可以看到這個聽的port是50021
修改port號可以透過Sql Server Configuration Manager 工具的：
Sql Server Network Configuration->Protocols for {instance name}- 對
TCP/IP點右鍵然後選擇內容 - 切換到頁簽
IP Address，並且卷到做下面，檢查IP All的TCP port是不是1433。
,{port號}即可使用哪個port做連線。例如假設port號是50021，那麼連線就是：tcp:localhost\SQLEXPRESS,50021
用local檢查sql帳號登入是否通過
一般來說連線的時候都是使用Sql帳號（而不是Windows驗證），因此要用local測試使用Sql帳號是否能夠登入。
假設出現了登入錯誤，Sql Error 18456，表示沒有開啟Mixed Mode，解決方式：
- 先用Windows驗證連到DB並且對DB點右鍵，選擇
Properties - 切換到
Security的左邊menu - 選擇
Sql Server and Windows Authentication Mode- 並且記得重啟DB的Service
用remote測試連線 - 防火墻
基本上如果上面都做完，基本上連線就不會有什麼問題，剩下可能會擋的部分就剩下防火墻。
可以先嘗試把DB那台和需要連到DB的那台的防火墻先關掉做測試，如果可以連，在把它打開，然後設定允許DB的port連線。
如果AP那台沒有連線工具可以連到DB做測試，那麼可以建立udl檔案做連線測試。
詳細做法可以參考：使用udl檔案測試DB連線是否成功
結語
DB連線不到的問題可能牽扯到很多不同部分的設定，因此希望透過這一篇提供的一步一步排除步奏能夠在未來如果遇到連線不到的情況能夠快速找到問題。
參考資料
- 官方的troubleshooting guide
- Troubleshoot Connecting to the SQL Server Database Engine
- 介紹設定防火墻
- Configure a Windows Firewall for Database Engine Accessä
- 另外一個的troubleshooting guide
- Steps to troubleshoot SQL connectivity issues
沒有留言 :
張貼留言