Alan Tsai 的學習筆記


學而不思則罔,思而不學則殆,不思不學則“網貸” 記錄軟體開發的點點滴滴 著重於微軟技術、網頁開發、DevOps:C#, Asp .net Mvc、Azure、Docker、Data Science

[SQL]為什麼連不到MSSQL資料庫 - 故障排除指南

2017-11-30 星期四
image
圖片來源:https://pixabay.com/en/key-tag-security-label-symbol-2114047/

任何一個比較大一點的系統都會需要使用到SQL,而以.Net開發為主最長用的資料庫基本上都是MSSQL。

不過MSSQL不一定是我們裝的,因此常遇到的問題就是給的資訊不夠完整,或者裝的時候缺少一些步奏,導致花費很多時間在找出為什麼MSSQL連線不到。

這篇希望提供一個故障排除指南,方便未來如果又連不到的時候可以依照這個項目來排除問題。

關鍵字:troubleshooting guide for MSSQL Database connection problem.

排除清單 - TL;DR

以下清單提供一步一步的排除步奏,基本上如果都做完之後連線基本上沒有什麼問題。

檢查項目 檢查方式 是否通過?
檢查服務是否有啟動? 透過Sql Server Configuration Manager -> Sql Server Services 確認狀態是Running
確定連線的DB Server Name

有兩種Instance名稱:

  1. Default Instance - MSSQLSERVER - 連線就是 {computername}
  2. Named Instance - 例如 SQLEXPRESS - 連線就是 {computername}\SQLEXPRESS
用local檢查tcp是否有通

使用Sql Server Management Studio(以下簡稱SSMS)連線的時候,在Server Name之前加上tcp:

例如:假設Server Name是localhost\SQLEXPRESS,那麼連線就是用tcp:localhost\SQLEXPRESS

如果連線不到,Sql Server Configuration Manager -> SQL Server Network Configuration - 確定TCP/IP是啟動的

用local檢查Database的port

可以先透過local連線過去(不要用tcp模式),然後檢查log看看目前在聽那個port 如果要調整port可以透過:Sql Server Configuration Manager -> SQL Server Network Configuration -> TCP/IP 右鍵 -> 內容確認 IP Addresses頁簽裡面最後一項 IP AllTCP Port數字。

如果不是1433,就在DB Server Name後面加上,{port},例如假設port是50021,那麼整個就是tcp:localhost\SQLEXPRESS,50021

用local檢查sql帳號登入是否通過 有幾個部分可以檢查:
  1. 如果出現18456 - 表示沒有開啟Mixed mode(允許sql帳號和windows帳號登入)
  2. 如果出現 預設db 開啟不了 - 檢查這個使用者的權限
用remote測試連線 - 防火墻

使用遠端要連到db的那台測試 - 可以使用.udl檔案來做

注意防火墻設定,要開啟上面找到的port

排除問題的詳細步奏

接下來將會對於每一個步奏會有更加詳細的介紹。

檢查服務是否有啟動?

開始裡面找到安裝的SQL Server版本,展開之後選擇Configuration Tools ->Sql Server Configuration Manager

image   
開啟Sql Server Configuration Manager

從左邊選取SQL Server Services,然後檢查右邊SQL Server的狀態處於Running

image   
確認Sqlexpress這個instance狀態處於Running

確定連線的DB Server Name

需要先確認DB的Instance名稱。

從上一個步奏確認服務是否有啟動的部分,可以看到在括弧裡面的就是DB的Instance名稱。以剛剛那個為例子,就是SQLEXPRESS

Instance名稱有分為2種:

  1. Default Instance - MSSQLSERVER 是Default Instance
  2. Named Instance - 其他設定都是Named Instance

如果是Default Instance,那麼連線的DB Server Name就是{電腦名稱},如果是Named Instance,那麼就是{電腦名稱}\{Instance名稱}

以剛剛那個instance為例,因為它是Named Instance,因此它的Server Name是localhost\SQLEXPRESS

image   
測試的連線畫面

Network:用local檢查tcp是否有通

當上面步奏連線成功的時候,走的是Shared Memory的通道,因此不表示tcp連線(因為從另外一台連過來走的通道)是可以通。

為了避免其他network的問題,可以先在local(DB在的那台)測試tcp通道是否有問題。

做法很簡單,就是把上一步取得的DB Server Name的前面加上tcp:即可,因此最後的結果是:tcp:localhost\SQLEXPRESS

image   
測試tcp連線的畫面

如果連線失敗,用Sql Server Configuration Manager -> Sql Server Network Configuration 裡面的 Protocols for {instance name}

確定TCP/IP處於Enabled的狀態。

image   
確認TCP/IP是啟用的狀態

Network: 用local檢查Database的port

如果上一個步奏TCP/IP已經是Enabled的情況但是還是連不到,檢查一下port是否正確。

預設port是1433,如果DB不在預設port,那麼連線的時候需要打上port號。

先用local的方式連到DB(不要加上tcp的部分)去檢查log看看目前在聽那個port:

image   
檢查步奏
  1. 選擇Management
  2. 選擇SQL Server Log
  3. 選擇最新的一筆
  4. 從跳出的畫面選擇Filter
  5. Message Contains Text輸入:server is listening on
  6. 勾起來Apply Filter
  7. 選擇ok
  8. 檢查結果,可以看到這個聽的port是50021

修改port號可以透過Sql Server Configuration Manager 工具的:

  1. Sql Server Network Configuration -> Protocols for {instance name}
  2. TCP/IP右鍵然後選擇內容
  3. 切換到頁簽IP Address,並且卷到做下面,檢查IP All的TCP port是不是1433。
image   
檢查port的方式
如果有改TCP Port,記得要重啟DB Service才會有作用。
假設port有修改,連線的時候在後面加上,{port號}即可使用哪個port做連線。例如假設port號是50021,那麼連線就是:tcp:localhost\SQLEXPRESS,50021
image   
連線畫面

用local檢查sql帳號登入是否通過

一般來說連線的時候都是使用Sql帳號(而不是Windows驗證),因此要用local測試使用Sql帳號是否能夠登入。

假設出現了登入錯誤,Sql Error 18456,表示沒有開啟Mixed Mode,解決方式:

  1. 先用Windows驗證連到DB並且對DB點右鍵,選擇Properties
  2. 切換到Security的左邊menu
  3. 選擇Sql Server and Windows Authentication Mode - 並且記得重啟DB的Service
image   
設定畫面

用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
2017-11-30 星期四
comments powered by Disqus