Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

How to troubleshoot SQL Server connectivity problems

We often get asked in the SQLTeam forums why they can't connect to a SQL Server instance.  They provide the typical connectivity error. 

In SQL Server 2000, the error is:

SQL Server does not exist or access denied.

In SQL Server 2005, the error is:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Both errors are misleading as they are not usually resolved by providing access or enabling remote connections.  In fact, I've never seen the SQL Server 2000 error to be as a result of lack of access.  If someone doesn't have access, they will get the "Login failed" error.

Here is an example thread where I helped the original poster and someone who hijacked the thread get connected to their respective SQL Server instances.  Here is another example. 

Recently, I found this MSDN blog post.  It has a bunch of typographical errors, but it is very detailed and should help resolve most connection problems.  The most important typo is netstat is spelled as netstate.  If the user doesn't realize that the command is netstat, they will get an error which could lead them in the wrong direction when troubleshooting this issue.  Hopefully the blog post will get corrected at some point.

Rather than linking to the SQLTeam threads I mentioned above when I see new threads with these errors, I will now be pointing them to this blog post.  Between the 3 links, the person should be able to resolve their problem. 

Print | posted on Friday, May 23, 2008 10:53 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: How to troubleshoot SQL Server connectivity problems

Great idea for a blog post ... very nice Tara! This question comes up so often that linking to this will be very handy.
5/24/2008 6:46 AM | Jeff S
Gravatar

# re: How to troubleshoot SQL Server connectivity problems

One occurence of the SQL Server 2005 error is not covered in the links. This is when trying to use ASP.NET 2.0 security with an enterprise SQL Server based aspnetdb database instead of having an SQL Express local database in the applications App_Data folder and with <roleManager enabled="true" /> set in the web.config file without reconfiguring the authorization manager to use the SQL Server database. This one drove me mad for weeks until finally figured it out.

The real problem is that 98% of all articles and books on security deal largely with authentication and only pay minor lip-service to authorization making it very very difficult, finding sample authorization schemas and configuration code without having to slog through a swamp of authentication coding to pick out the bits and pieces needed for putting together a valid authorization scheme.
5/28/2008 6:57 AM | Ron Kunce
Gravatar

# re: How to troubleshoot SQL Server connectivity problems

I disagree with the value of this article in and of itself being complete. It's a nice start. But, there's a much better resource when the problem is the target machine actively refused the connection (i.e., connected to machine, SQL Server rejected login) - it's also found in the msdn blogs, from 19 july 2007 and written by Ming Lu. Unfortunately, I didn't bookmark it, and the printed address on my printout is not to get you there directly: enoughblogs.msdn.com/sql_protocols/archieve/2007/7/19/tcp-provide... is not enough of an address
5/28/2008 2:53 PM | steve smith
Gravatar

# re: How to troubleshoot SQL Server connectivity problems

Steve Smith,

I believe you are referring to a different error than what is handled by my blog. The connection refused error is not the same as what this is about.
5/28/2008 2:58 PM | Tara
Gravatar

# re: How to troubleshoot SQL Server connectivity problems

Steve Smith,

I found the blog that you mentioned. It is indeed about an error that is different than what my blog is mentioning. Are you able to find anything wrong with my blog post that is related to the errors mentioned?

Here is what Steve was mentioning for those interested:
http://blogs.msdn.com/sql_protocols/archive/2007/07/19/tcp-provider-no-connection-could-be-made-because-the-target-machine-actively-refused-it.aspx
5/28/2008 4:26 PM | Tara
Gravatar

# re: How to troubleshoot SQL Server connectivity problems

I have fould couple of things which helped me any my customers as well.

Try to connect with TCP:<Server_name>\<Instance_naem/Port_number> or
NP::<Server_name>\<Instance_naem/Port_number>

If it did not work out then try to use the following steps and I am sure it should resolve the issue.


These are the steps:

Step1:

Look for the Machine.config file

Step 2:
Search for this part in the machine.config file:

<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;>
</connectionStrings>

Step 3:

Change it to this:
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString=".." providerName=".." />
</connectionStrings>

Restart and try again. It will work It worked for me.

5/29/2008 7:40 AM | Manoj
Gravatar

# re: How to troubleshoot SQL Server connectivity problems

Tara - I agree that the error messages are not identical. I also receive the error message you describe in your blog; solving the problem you described just led me to the problem I now face (plus some additional complications I discovered and/or created for myself late Friday).

However, the underlying issue is the same, and given the intentional ambiguity of security related diagnostics - not giving illegitimate users enough information to get in - those of us who are legitimately trying to connect are left hanging without knowing what we are doing wrong or what else we need to do in order to successfully connect our different servers together. SQL Server 2005 made great strides towards improving security and providing additional layers of protection rather than delegating the safety and security issues to an external environmental control. But that additional protection comes at a price. In the meantime, where can I go to get my head properly wrapped around these security issues so I can arrive at a working solution?
6/2/2008 6:55 AM | steve smith
Gravatar

# re: How to troubleshoot SQL Server connectivity problems

I have the same problem. My machine.config hasn't been changed for over 3 months but today I get the same error.

Craig
7/1/2008 5:28 AM | The Village Idiot
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET