SQL Management Studio Problem Connecting to WSS 3.0 Database

Today I came across the following error (which I have encountered many a time previously) but I totally forgot how I resolved it the last time. I hit the error most typically when I’m accessing a WSS 3.0 deployment using the Windows Internal Database. I’ve also encountered it when I access some SQL Server deployments.

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)

I feel absolutely stupid for continuously hitting this same issue and forgetting how I resolved it previously so I’ve decided to write a quick blog post on steps to follow next time it happens (this assumes I remember that I blogged about the issue).

  1. If its a local Windows Internal Database you’re accessing, make sure you’re accessing the right address. So many times I’ve assumed the address is going to be localhost/MICROSOFT##SSEE when in fact the address you need to be accessing is \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query. This has tripped me up so many times its embarrassing.
  2. If you’re absolutely sure you got the right address, check TCP/IP and Named Pipes have been enabled for the SQL Server instance. You can do this by doing the following steps on the machine hosting the SQL Server instance.
    1. Go to the Start Menu -> All Programs -> Microsoft SQL Server 2005/2008 -> Configuration Tools -> SQL Server Configuration Manager.
    2. Once the Sql Server Configuration Manager has loaded, check under SQL Native Client 10.0 Configuration (32bit) -> Client Protocols and SQL Native Client 10.0 Configuration -> Client Protocols that TCP/IP and Named Pipes are both enabled.
    3. Check the protocols allowed, for the instance you wish to access, under the SQL Server Network Configuration section. Again make sure that TCP/IP and Named Pipes are enabled.
    4. You will need to restart the SQL Server instance if you’ve had to make any of the changes above.

These two steps are usually the only steps I need to perform before I realise whats wrong. I’ll add more steps if I come across any other things that trip me up in the future or if anyone else has any to contribute.

3 thoughts on “SQL Management Studio Problem Connecting to WSS 3.0 Database

  1. Hello thank you this fixe my issue, I had wss 3.0 windows internal database:
    “localhost/MICROSOFT##SSEE when in fact the address you need to be accessing is \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query”
    Why does windows show the prior name instead of the latter when i click browse for databases on the network, this is why I assumed the prior name was the one I should be using, but thanks to your article I know now. Is this how to access windows internal databases?

    • Hi A,

      Yes this seems to be the way to access the Windows Internal DBs. Unfortunately I’m not entirely sure why it shows the a different name – it’s what really caused me a bit of confusion to begin with. I imagine it’s because the Windows Internal DB is not accessible via TCP and the “\\.\pipe” is communicating to the SQL connector that it needs to use a named pipe – but this is just pure speculation on this. I’d like to hear from anyone, that reads this, if they know the exact reason.

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