SQL Server 2005 Connectivity Problems

Last week, I have encountered a problem in connecting SQL Server 2005 from a remote machine, to a Server, in which Windows Vista Business Edition was the operating system.

After installing the SQL Server 2005 with SP2 in the Vista Business Machine, I started checking the SQL Server. It is working fine in local server. When I am trying to connect the same server from an Application (developed in VB.NET), I was unable to connect that.

My basic principle in diagnosing the problem is, always starting with zero. So I started ping the server IP and also the name of the Server. It was working fine.

Second I checked the Application, that the Server Name (and instance name in the correct format, ‘\’ should be used with instance name not the ‘/’) specified there is correct or not!? It was right!

Then I checked Server’s Surface area Configuration, whether the server is allowing remote connection or not! It is allowing! Then checked that the Protocols for Remote Connections are properly configured or not! That was also done! (As I knew the purpose of that Server earlier, after installing the SP2, I configured all these things earlier! Still I checked everything again!)

So everything is fine, and where lies the block!?

Then I started digging with error numbers in SQL Server Books Online and a lot of SQL Server related websites.

Most of the times I got the error was “Named Pipes Provider, error: 40 - Could not open a connection to SQL Server” . Best answer I have got on searching with this error is in SQLProtcols blogs. A must read blog for the every DBAs.( The last article published in that blog was July 2007)

Then I found that, SQL Browser Service was stopped and disabled! So I started with the following commands!( I typed in Command Prompt!)

sc config sqlbrowser start= auto
net start sqlbrowser

After starting the SQLBrowser, I was getting the Error (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

This error number is related to Firewall! Then I got that. I have to open the port ( the default port for my situation is the default port of SQL Server 1433) in firewall! The following link helped me! Step by step is explained there.

http://msdn2.microsoft.com/en-us/library/ms175043.aspx

Now it is working fine! Thank you MSDN!