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).
- 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.
- 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.
- Go to the Start Menu -> All Programs -> Microsoft SQL Server 2005/2008 -> Configuration Tools -> SQL Server Configuration Manager.
- 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.
- 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.
- 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.