III. Troubleshooting Tips:
Assumption: your SQL Server was installed on remote server and behind firewall; SQL Instance was started; and your client app specify correct remote sql instance name.
On your server side:
[1] Enable remote named pipe or tcp: All programs | Microsoft SQL Server 2005| Configuration Tools | SQL Server Surface Area Configuration | Configuration for Services and Connections | Remote Connections, choose either enable TCP or Named Pipe or both.
[2] Sql Instance was restarted successfully, check Server ErrorLog, find which tcp port or pipe name server is listening on.
[3] netstate -ano | findstr <portnumber> if server enable TCP, and make sure server is listening on the correct port.
[4] go to services.msc, find service “SQL Server Browser”, enable it and restarted, also, go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active.
[5] Enable “Fire and Printer Sharing” in Firewall exception list.
Add TCP port or sqlservr.exe to Firewall exception list, either add “..\Binn\sqlsevr.exe” or add port.
If your server was not started successfully by any reason, it is very helpful to collect info from server logs; also, you can get clear picture of protocols that server is listening on, for eg, if TCP was enabled, you should be able to see which port server is listening on, and if Np was enabled, you can make connection throgh the pipe name.
[7] Add Sql Browser service to Firewall exception list, you can either add program ” C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe” or add UDP port 1434.
Make sure if your remote sql Server Instance is a default instance, it must listen on tcp port 1433 and pipe \\.\pipe\sql\query.
Filed under: Applied IT | 5 Comments »
