Tuesday, January 24, 2017

Troubleshooting SQL Server Connections Issue

I came across a challenging issue to solve SQL Server Connection Issue, I want to share the quick steps to resolve the issue in structured way.

Note: This blog is not how to troubleshoot, its all about what to troubleshoot.

Target Audience: SQL Server DBA, Windows System Administrator

Error Message:  Cannot connect to \.
ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

Steps to Diagnose:

Server Machine:
At Configuration Level:
1) SQL Server Services should to up and running.
2) Enable the TCP Protocols via Configuration manager for required Instance
3) Make note or Configure port( default 1433) for TCP protocol
4) SQL Browser should be up and running.
5) Enable Firewall to allow connection on TCP Ports and Browser service ports.
At Database Instance Level:
Check following setting for required Instance using SQL Management Studio
1) Enable Mixed mode authentication(both Windows and SQL Authentication)
2) Ensure SA login is enabled
3) Ensure remote connections are allowed to connect 

Client Machine:
1) Ping the server using name or IP using Command prompt
2) Check is server machine file share is accessible
3) Ping the server with telnet command "telnet \  
4) Try with both windows and SQL authentication.

With all above list of action, i could able to resolve the connection issue in a timely fashion. 

Hope this helps!!