Normally this error message indicates that the Server Name & Instance Name combination does not exist or you have misspelt either of the 2. This can be a very time consuming error to track down and resolve.
To set the scene, While at a client’s site the other day I was asked to look into why the test connection from a udl file (Universal Data Link – for more information on Setting up and Configuring http://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx
) from a remote server to a SQL Server 2008 R2 server. This error was received so I thought I would share my experience with you in this Blog.
The setup of the UDL is as follows:
-
Configured to use the ‘Microsoft OLE DB Provider for SQL Server’
-
The Server Name and Instance was selected and configured by browsing for the Instance to connect to (so we know it exists and is spelt correctly)
-
The user account was checked to ensure it had appropriate permissions in the Instance and required database
-
The Windows Firewall is turned off on both the SQL Server and the remote server so we did not have to worry about configuring rules in the firewalls to gain access for SQL Server on the specified ports
-
The server was pingable
-
We could Telnet to the port without error either
Does any of this sound familiar? Now starting to get a little frustrated, I decided to see what I could find out there on Google. Nearly all of the responses I found all indicated that we had misspelt Server NameInstance Name and to put in correct details. Where to next?
I thought lets go back to the udl and try some different providers. First off we are failing using the ‘OLE DB provider for SQL Server’ so I next went back to look at the other available providers and selected the SQL Server native Client 10.0
Browse to the server and instance as previously tried and test the connection. Success.
I am not an expert by any means when it comes to connectivity so I do not have any explanations as to why the Microsoft OLE DB Provider for SQL Server was not connecting, but if you have the SQL Server Native Client 10.0 provider available then I suggest you use it.
The Latest client download is available from here – (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6c3e9ef-ba29-4a43-8d69-a2bed18fe73c)
I hope this helped you solve your connectivity issue.
Till next time……
Thanks. I was stuck and this has really helped me.
Life -> saver…