Saturday, December 19, 2009

"Specified SQL server not found" or "Error Locating Server/Instance Specified" On windows vista when connecting to sql server in remote server , .net

"Specified SQL server not found" or "Error Locating Server/Instance Specified" On windows vista when connecting to sql server in remote server

If you are trying to connect a client computer running windows vista or windows server 2008 to a named instance of sql server. In this scenario the connection may fail. If you are using windows data access component 6.0 to connect to named instance of sql server, you will receive the following error message.

[DBNETLIB]Specified SQL server not found.
[DBNETLIB]ConnectionOpen (Connect()).


And if you are using sql native instance to connect with named instance of sql server you will receive the following error message.

[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired.



In connecting the client sends a UDP request to the Ip address of the named instance. Then sql browser returns a Udp response which contains information about endpoints. However, in the UDP response packet, the source IP address may not be the IP address to which the UDP request packet was sent. If the named instance is a failover cluster instance, the source IP address is the IP address of the physical computer instead of the virtual IP address of the remote server. If the remote server has multiple IP addresses, the source IP address may be any of the IP addresses that are assigned to the remote server.

There are lots of ways to solve this error.

Specify the TCP portname or named pipename in the connection string

Some other ways also there to solve this error

In Windows Firewall with Advanced Security in Control Panel, create an outgoing rule for the application that connects to SQL Server. To do this, follow these steps:

1. Take the control pannel.

2.Double click on administrative tool

3. In the administrative tools double click Windows Firewall with Advanced Security.

4. In Windows Firewall with Advanced Security, click Outbound Rules, and then click New Rule.

5. Click Program, and then click Next.

6. Click This program path, specify the path of the application, and then click Next.

7. Click Allow the connection, and then click Next.

8. Complete the steps of the New Outbound Rule Wizard

In Windows Firewall with Advanced Security in Control Panel, create an incoming rule that allows for traffic from all possible IP addresses of the remote server or from all possible IP addresses that are configured for the failover cluster instance. To do this, follow these steps:

1. In Control Panel, double-click Administrative Tools.

2. In Administrative Tools, double-click Windows Firewall with Advanced Security.

3. In Windows Firewall with Advanced Security, click Inbound Rules, and then click New Rule.

4 Click Custom, and then click Next.

5. Click All programs, and then click Next.

6. In the Protocol type list, click Any, and then click Next.

7. Under Which remote IP addresses does this rule match, click These IP addresses, and then click Add.

8. In the IP Address dialog box, type one of the IP addresses under This IP address or subnet, and then click OK.

9. To add other IP addresses, repeat steps 7 through 8, and then click Next.

10. Click Allow the connection, and then click Next.

11. Complete the steps of the New Inbound Rule Wizard

7 comments:

  1. If you run into connection problems on a 64 bit (say your new Windows 7 system, using SQL BI Development Studio) add your aliases under SQL Native Client 10.0 Configuration (32 bit)

    Bill T

    ReplyDelete
  2. Bill

    You just saved me after 4 days of investigations of why my client wouldn't reconnect to my mirrored database (running a 64bits version of SQL server 2008).

    A big THANK YOU !!!!

    Manuel

    ReplyDelete
  3. Thanks Bill, I did a lot of reading on this connectivity problem with connecting from one win2008+sql2008 to another. SQLCMD would work but SSMS wouldn't.

    Thanks for pointing out the obvious (he says with the backing of hindsight) it saved me a lot of headaches.

    ReplyDelete
  4. but in my administrative tools there is no window firewall option and advancd security option.What to do?

    ReplyDelete
  5. but in my administrative tools there is no window firewall option and advancd security option.What to do?

    ReplyDelete
  6. Go to Start Menu then type in firewall on search field. You will see the firewall applications.

    ReplyDelete