Connection Fail

Below are the steps to troubleshoot SQL Server connection problem:

SQL Server Running?
Check the sql server is running, by connecting to the sql server inside the server machine locally, with a sysadmin account.

Network problem?
ping -a SQL_Server_Machine_Name
ping -a SQL_Server_Machine_IP
nslookup SQL_Server_Machine_Name
nslookup SQL_Server_Machine_IP

SQL Server Configuration problem?
Check SQL Server Configuration Manager:
  -> for named instance -> SQL Server Services -> SQL Server Browser Running?
  -> SQL Server Network Configuration -> Protocols -> Named Pipes / TCP/IP Enabled?
Check sql server error log \Program Files\Microsoft SQL Server\MSSQLxxx\MSSQL\Log

Firewall problem?
Try turn off firewall to identify whether it's caused by the network traffic is blocked by firewall.

Client problem?
telnet SQL_Server_Machine_Name/IP TCP_Port
osql/sqlcmd/SSMS connect to sql server?
For ODBC, \windows\system32\odbcad32.exe

Protocol problem?
Try to modify the ConnectionString Data Source as TCP:sqlServerIP,instancePortNo

Application problem?
Check ConnectionString loginname & password in the application config file

Authenication problem?
SQL Server Windows Authenticaion only or Windows + SQL Authentication?
Login account has access permission on default database?

Service Principal Name (SPN) problem?
Check SPN registered or not:
create table #t (LogDate datetime, ProcessInfo nvarchar(100), [Text] nvarchar(4000))
insert #t exec sp_readerrorlog
select * from #t where [Text] like '%listening on%' OR [Text] like '%SPN%'
drop table #t
In command prompt -> setspn -L mssql_service_account
If not, add it manually :
setspn -A MSSQLSvc/server_name:port_no mssql_service_account
setspn -A MSSQLSvc/server_name:instance_name mssql_service_account

No comments:

Post a Comment