Jun
7
2012

SQL 2005/2008/R2 N/W connection error troubleshooting steps

You may see 1001 n/w connection error when trying to connect remote sql server from ssms, I have compiled some best troubleshooting steps I am aware and few collected from various references,
 
First try to use one of five ways to connect SQL Server using SSMS,
(for e.g. let’s say server name is ngmssql and instance name is sql001 and domain is ad.in.com and port is 1433 and ip address is 10.26.27.28)
 
• Connect via SQL Server Name i.e. <Server_name\Instance_name> for e.g. ngmssql\sql001
• Connect via SQL Server Fully qualified name i.e. <Server_name.Domain extention\instance_name> for e.g. ngmssql.ad.in.com\sql001
• Connect via port number i.e. <Server_name\Instance_name,port_number> for e.g. ngmssql\sql001,1433
• Connect via IP address i.e. <ip_address of server\instance_name> for e.g. 10.26.27.28\sql001
• Using DAC (Dedicated Administrator Connection) i.e. <dac:server_name\instance_name> –DAC should be enabled first to use this for e.g. dac:ngmssql\sql001
 
Note: If instance name is default then replace “\instance_name” in all of above syntaxes for e.g. ngmssql or ngmssql,1433 or dac:ngmssql, etc.
 
———–
 
1. Make sure your database engine is configured to accept remote connections
• Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
• Click on Surface Area Configuration for Services and Connections
• Select the instance that is having a problem > Database Engine > Remote Connections
• Enable local and remote connections
• Restart instance
 
2. Check the SQL Server service account
• If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding
 
3.You may need to create an exception on the firewall for the SQL Server instance and port you are using
• Start > Run > Firewall.cpl
• Click on exceptions tab
• Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn), and port (default is 1433)
• Check your connection string as well
 
4. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser
 
5. Check that you have connectivity to the SQL Server. Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
• Start > Run > cmd
•netstat -ano| findstr 1433
•telnet myserver 1433
•ping -a myserver
 
———-
 
 
Cause Resolution
Server name was typed incorrectly. Try again with the correct server name.
The SQL Server service on the server is not running. Start the instance of SQL Server Database Engine.
The TCP/IP port for the Database Engine instance is blocked by a firewall. Configure the firewall to permit access to the Database Engine.
Database Engine is not listening on port 1433 because it has been changed, or because it is not the default instance, and the SQL Server Browser service is not running. Either start the SQL Server Browser service, or connect specifying the TCP/IP port number.
The SQL Server Browser service is running but UDP port 1434 is blocked by a firewall. Either configure the firewall to permit access to the UPD port 1434 on the server, or connect specifying the TCP/IP port number.
The client and server are not configured to use the same network protocol. Using SQL Server Configuration Manager, confirm that both the server and the client computers have at least one enabled protocol in common.
The network cannot resolve the server name to an IP address. This can be tested using the PING program. Fix the computer name resolution problem on your network or connect using the IP address of the server. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.
The network cannot connect using the IP address. This can be tested using the PING program. Fix the TCP/IP problem on your network. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.

 

 
Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 179 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

June 2019
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930

View Post by Categories

%d bloggers like this: