Fail connection via SQL Server Management Studio to an remote server (Windows Small Business Server 2003) that end with this error:
Cannot connect to 192.168.0.1.
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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476
Error Number: 2
Many articles point to Firewall / NAT issue and explain how to solve it but in many cases is much simple than that. May be the case when SQL is installed on Windows Small Business Server 2003 and the firewall is off but still connection error occurred. If this is the case the solution is simple. By default Microsoft SQL Server installs with TCP/IP protocol – Listen all : YES and detects all active IP on the server. If one of the IP addresses is no longer available the SQL Server will not start properly and direct remote connections to data base are not accepted for local and external interfaces.
- verify that your NAT / Firewall is configured to accept connections for 1433 port, create a new rule if there is none.
- open Computer Management > Services and Applications > SQL Server Configuration Manager > SQL Server Network Configuration > Protocols <your server name>
- open TCP/IP (in the right window section)
- on [Protocol] tab last position is [Listen All: YES] … change it in NO (see images)
- on [IP Addresses] make sure you have the right active IPs in IP1 -x sections and [TCP Port] set to 1433, confirm new settings by pressing OK and restart SQL Server only, no need to restart SQL Server Browser too.