Advertisements

Can’t connect remotely to sql server 2008

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/ec77d275-99c7-4cc0-b60d-707bde6f8c67

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.

Devil 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.

Music Make sure if your remote sql Server Instance is a default instance, it must listen on tcp port 1433 and pipe \\.\pipe\sql\query.

Advertisements

5 Responses

  1. thank you

  2. argh, still not working 😦 I’m using 2 instances on my sql server – could that be part of it?

  3. was the firewall for me.

    Thanks.

  4. this is incorrect. Surface Area Configuration has been discontinued in SQL2008 onwards.

    http://www.mssqltips.com/sqlservertip/1673/where-is-the-surface-area-configuration-tool-in-sql-server-2008/

  5. For me, it was IPSEC rules configured on dedicated server by hosting company.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: