BUG: Cannot connect to a clustered named instance through a firewall
This article was previously published under Q318432
BUG #: 356696 (SHILOH_BUGS)
If you try to connect a clustered named instance of SQL Server through a firewall, and you use only the instance name (for example, SQL_Virtual_Name\Instance_Name) in the connection string, the connection fails and you may receive one of the following error messages:
Error message 1
Specified SQL server not found.
Error message 2
SQL Server does not exist or access denied.
If you run a network trace on the server, you can see that the server actually receives the query from the client computer on UDP port 1434 for the virtual server IP address; however, the answer is sent with the physical IP address of the cluster node that is currently running SQL Server.
When a client computer connects to a computer that is running a clustered instance SQL Server, if the connection string does not specify the destination TCP port, the client library queries the server on port UDP 1434 to collect information about the instance.
When the server returns the information, the network frame contains the IP address of the physical node instead of the IP address of the virtual server. Depending on the firewall configuration, this network packet may be dropped, and the client may not receive any answer.
To work around this behavior you can either:
- Specify the TCP port number in the connection string. For example, if your virtual SQL Server server is named VSERVER1, and it is listening on TCP/IP port number 2433, your connection string will contain the following attributes.
Data Source=VSERVER1\instancename,2433;Network Library=dbmssocn
- Create a SQL Server alias on each client by using the Client Network Utility. In the alias, specify the Net-Library to use (TCP/IP), and the port number. Use this alias in your connection string, and do not specify the Network Library attribute in your connection string.
You can also automate the creation of the SQL Server alias on each client by using a Group Policy object (GPO) on the domain controller. Assume that your virtual instance of SQL Server is named VSERVER1\MyInstance. The virtual instance of SQL Server is listening on TCP/IP port number 2433. The name of the SQL Server alias that you want to create is MyAlias. To automate the creation of the SQL Server alias, follow these steps:
- On the domain controller, create a .reg file that contains the registry entry for the SQL Server alias.
For example, create a Myreg.reg file that contains the following information.
Windows Registry Editor Version 5.00
- To create an organizational unit (OU), follow these steps:
- Open Active Directory Users and Computers, right-click DomainName, point to New, and then click Organizational Unit.
- In the New Object - Organizational Unit dialog box, enter a name for the OU, and then click OK.
- Add the users and the computers to which you want to apply the GPO to the OU. To do this, drag the users and the computers to the OU that you created in step 2.
- Add a GPO to the OU. This GPO applies a user logon script or a computer startup script to import the .reg file. To do this, follow these steps:
- Right-click the OU that you created in step 2, and then click Properties.
- In the Properties dialog box, click the Group Policy tab.
- On the Group Policy tab, click New, and then click Edit to open the Group Policy Object Editor window.
- If you want to apply this GPO to users or to groups, follow this step:
If you want to apply this GPO to computers, follow this step:
- Expand User Configuration, expand Windows Settings, click Scripts (Logon/Logoff), and then double-click Logon.
- Expand Computer Configuration, expand Windows Settings, click Scripts (Startup/Shutdown), and then double-click Startup.
- In the Properties dialog box, click Show Files to open the folder that contains the script files.
Note A Universal Naming Convention (UNC) path appears in the Address bar of the folder window.
- Put the Myreg.reg file into the folder that you opened in step 4e.
- In the same folder, create a .bat file that contains the following information. Assume that the file name is Mybat.bat.
Regedit /s <Path>\Myreg.reg
- <Path> represents the UNC path of the folder.
- The /s switch specifies to import the .reg file in silent mode.
- In the Properties dialog box, click Add.
- In the Add a Script dialog box, click Browse.
- In the Browse dialog box, locate the Mybat.bat file, and then click Open.
- In the Add a Script dialog box, click OK.
- In the Properties dialog box, click OK.
- Make sure that the users and the computers that you added in step 3 have the Read permission and the Apply Group Policy permission to the GPO. to do this, follow these steps:
- In Active Directory Users and Computers, right-click the OU that you created in step 2, and then click Properties.
- In the Properties dialog box, click the Group Policy tab.
- On the Group Policy tab, click the GPO that you created in step 4c, and then click Properties.
- In the Properties dialog box, click the Security tab.
- On the Security tab, make sure that the users and the computers to which the GPO applies are listed under Group or user names.
- Make sure that the Read permission and the Apply Group Policy permission are specified for the users and the computers.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
This problem is fixed in the following cases:
- Microsoft SQL Server 2008 installed on Windows Server 2008
- Microsoft SQL Server 2005 that co-exists together with Microsoft SQL Server 2008 on Windows Server 2008
This problem occurs in Microsoft SQL Server 2008 on Windows Server 2003.
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
FIX: Error message when you connect to a named instance of SQL Server on a client computer that is running Windows Vista or Windows Server 2008: "Specified SQL server not found" or "Error Locating Server/Instance Specified"
List of known issues when you install SQL Server 2005 on Windows Server 2008
Article ID: 318432 - Last Review: May 5, 2009 - Revision: 3.0
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Enterprise Edition