Dec
20
2011

SQL Server 2005 is inaccessible remotely – error:26-Error locating server

Today while working with one Veritas cluster server, faced following error (one of the common error – error:26),

There are few basic troubleshooting steps a normal DBA follow,

1. Under ‘SQL Server Surface Area configuration manager’, go to ‘Surface Area configuration for services and connections’, go to ‘Server instance’ > ‘Database Engine’ > ‘Remote connections’

2. In remote connections, first and foremost, your ‘local and remote connections’ should be enabled in case you would like to reach to your SQL server from outside network, if not enabled then ‘Local connections only’ would be there. If that is the case, you have nailed down the issue, select ‘local and remote connections’ and opt for ‘TCP/IP’ or ‘using both TCP/IP and named pipes’

3. It will prompt that changes will take effect once SQL services are restarted, so restart them and try to connect remotely

4. Now if above is not the case and already ‘local and remote connections’ is selected then dig further and go to ‘SQL Server Browser’ > ‘Service’, it should be automatic and started (cumpulsary in case of named instance to be searchable – although there are security issues if you enabled browser service, check microsoft article on same). If disabled or stopped then enable it and start, then try to connect, it should solve the issue

Now, if you didn’t get success after above steps like in my case, then you need to go for following option (including above), which is alias configuration,

5. Go to ‘SQL Server configuration manager’, go to ‘SQL Server 2005 Network Configuration’ > ‘Protocol for < instance name >‘ > ‘select TCP/IP’ (double click) > go to ‘IP Addresses’ tab > scroll down to ‘IPAll’ section, cut the port number from ‘TCP dynamic port’ and paste it in ‘TCP port’ section

6. it will prompt,

Select OK

7. Now in same console, go to ‘SQL Native client configuration’ > ‘Aliases’ > Right click and create ‘new alias’ >, in ‘Alias name’ insert the full instance name < servername (virtual in case of cluster) >\< instance name >

8. In ‘port number’, insert the same port number as done in Step 5

9. In ‘protocol’, select TCP/IP

10. In ‘server’, give the same name as in Step 7 above, i.e. < servername (virtual in case of cluster) >\< instance name >

If exists ‘SQL Native Client Configuration (32 bit)’ then create alias in that as well following above steps 7 to 10.

11. Select Apply and then OK and restart SQL services through ‘Surface Area configuration for services and connections’ or ‘SSMS’

12. Now try to connect SQL server, it should connect.

If you still face issue then check the firewall ports (to check firewall settings, go to Start> Run > type ‘firewall.cpl’) in case the port number is blocked to receive incoming connections, etc.

For needy ones, hope above solution will work as it worked for me in many cases especially in Veritas/Windows cluster cases where naming convention is complex and cause remote connections to fail.

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 239 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

October 2019
M T W T F S S
« Nov    
 123456
78910111213
14151617181920
21222324252627
28293031  

View Post by Categories

%d bloggers like this: