How to connect to a named instance of SQL Server 2005 or SQL Server 2000 by using the client tools in the earlier version of SQL Server (265808)


The information in this article applies to:

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 6.5
This article was previously published under Q265808

SUMMARY

Microsoft SQL Server 2005 and Microsoft SQL Server 2000 introduce multiple instances. However, client tools such as Query Analyzer or ISQL/W, which are available with earlier versions of Microsoft SQL Server, may experience difficulty connecting to the non-default named instances. For example, if you try to connect to a named instance by using \\computer_name\instance_name, the client tools may return this error message:
Error: \\computer_name\instance_name
unable to connect server \\computer_name\instance_name
Server: Msg 67, Level 61, State 1 [Microsoft][ODBC SQL Server Driver] Client unable to establish connection.

MORE INFORMATION

You must have Microsoft Data Access Components (MDAC) version 2.6 or a later version to connect to a named instance by using the \\computer_name\instance_name format. We recommend that you upgrade the client tools and MDAC version to work with SQL Server 2005 or SQL Server 2000.

However, a workaround is possible by using server aliases on a client computer that only has client tools of earlier versions and MDAC installed. Be aware that this workaround provides limited functionality. You cannot use Microsoft SQL Server 7.0 Enterprise Manager to manage SQL Server 2005 or SQL Server 2000, although you can connect to a named instance of SQL Server 2005 or a named instance of SQL Server 2000 by using the SQL Server 7.0 client tools Query Analyzer and Osql.exe, or by using the Microsoft SQL Server 6.5 client tools ISQL/W and Isql.exe.

The following steps describe how to configure a server alias to use either TCP/IP sockets or Named Pipes to connect to a named instance of SQL Server 2000 or a named instance of SQL Server 2005 directly, without having to specify the named instance name.

Configure a server alias to use TCP/IP sockets

Note To configure a server alias to use TCP/IP sockets, you must provide the server name and the TCP/IP port number.
  1. Determine the TCP/IP port number of the instance of SQL Server.

    SQL Server 2005
    1. Open SQL Server Configuration Manager, and then expand SQL Server 2005 Network Configuration.
    2. Click Protocols for InstanceName, and then double-click TCP/IP in the right panel.

      Note InstanceName is a placeholder for the named instance of SQL Server 2005.
    3. On the Protocol tab, notice the value of the Listen All item.
    4. Click the IP Addresses tab:
      • If the value of Listen All is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll.
      • If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.
      Note If the value of the TCP Dynamic Ports item is not set, you must set it yourself. For more information about how to configure a server to listen on a specific TCP port, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn2.microsoft.com/en-us/library/ms177440.aspx
    5. Click OK.
    SQL Server 2000
    1. On the server that is running SQL Server 2000, start the Server Network Utility.
    2. Click the General tab, and then select the instance that you want from the Instances list.
    3. Click TCP/IP, and then click Properties. The TCP/IP port number for this instance is shown. Note this number for use later.
  2. Configure the server alias on the client computer.

    SQL Server 2000
    1. Start the Client Network Utility.
    2. On the General tab, verify that TCP/IP appears in the list under Enabled protocols by order.
    3. Click the Alias tab, and then click Add.
    4. Under Network libraries, select TCP/IP.
    5. In the Server name text box, type the IP address of the server that is running SQL Server 2005.

      Note The IP address that you type here is the one that is configured to use the TCP/IP port number.
    6. Click to clear the Dynamically determine port check box, and then type the port number of the instance of SQL Server 2005 in the Port number text box.
    7. Type a name in the Server alias text box, and then click OK.
    SQL Server 7.0
    1. Start the Client Network Utility.
    2. On the General tab of the Server Alias Configuration dialog box, click Add, and then click TCP/IP.
    3. Type an alias name in the Server Alias text box. The alias can contain any name.
    4. Type the SQL Server 2000 computer name or the SQL Server 2000 IP address in the Computer Name text box. Do not add an instance name.
    5. In the Port Number text box, type the port number that you recorded in step 1. Click OK two times.
    SQL Server 6.5
    1. Start the SQL Server Client Configuration Utility.
    2. Click the Advanced tab, and then click Add/Modify.
    3. Type an alias name in the Server text box.
    4. Select TCP/IP Sockets as the DLL name.
    5. In the connection string, type the name of the computer that is running SQL Server followed by a comma (",") and the port number you recorded in step 1. For example:

      ComputerA, 1523

      Do not add an instance name. Click Add/Modify.
    6. Click Done.

      Note SQL Server 6.5 does not support trusted connections with TCP/IP. Therefore, you might see the following error message when you use TCP/IP sockets to make a trusted connection to SQL Server 2000 by using SQL Server 6.5 client tools:
      Msg 18452, Level 14, State 1: Login failed for user 'user_name'. Reason: Not associated with a trusted SQL Server connection. DB-Library: Login incorrect.
      Use Named Pipes to make a trusted connection when you use SQL Server 6.5 client tools.
Now you can use the server alias to connect to a named instance of SQL Server 2005 or a named instance of SQL Server 2000 by using the SQL Server 7.0 client tools Query Analyzer and Osql.exe, or by using the SQL Server 6.5 client tools ISQL/W and Isql.exe.

Configure a server alias to use Named Pipes

Note To configure an alias to use Named Pipes, you must provide a server name and a pipe name.
  1. Determine the pipe name of the server instance.

    SQL Server 2005
    1. Open SQL Server Configuration Manager, and then expand SQL Server 2005 Network Configuration.
    2. Click Protocols for InstanceName, and then double-click Named Pipes in the rightmost panel.
    3. On the Protocol tab, notice the value of the Pipe Name item. The value of the Pipe Name item specifies the named pipe that SQL Server listens on.

      Note If the value of the Pipe Name item is not set, you must set it yourself. For more information about how to configure a server to listen on an alternate pipe, visit the following MSDN Web site: http://msdn2.microsoft.com/en-us/library/ms189321.aspx
    4. Click OK.
    SQL Server 2000
    1. On the server that is running SQL Server 2000, start the Server Network Utility.
    2. On the General tab, click the instance that you want in the Instances list.
    3. Click Named Pipes, and then click Properties. Note the pipe name for use later.
  2. Configure the server alias on the client computer.

    SQL Server 2000
    1. Start the Client Network Utility.
    2. On the General tab, verify that Named Pipes is in the list under Enabled protocols by order.
    3. Click the Alias tab, and then click Add.
    4. Under Network libraries, select Named Pipes.
    5. In the Pipe name text box, type the pipe name of the server that is running SQL Server 2005. Then replace the first dot (.) in the pipe name with the IP address of the server that is running SQL Server 2005. For example, if the pipe name that you obtain from the instance of SQL Server 2005 is \\.\pipe\MSSQL$Instance\sql\query, you must type \\IPAddess\pipe\MSSQL$Instance\sql\query.

      Note "IPAddress" is a placeholder for the IP address of the server that is running SQL Server 2005.
    6. Type a name in the Server alias text box, and then click OK.
    SQL Server 7.0
    1. Start the Client Network Utility.
    2. On the General tab of the Server Alias Configuration dialog box, click Add, and then click Named Pipes.
    3. Type an alias name in the Server Alias text box. The alias can contain any name.
    4. Type the pipe name that you recorded from step 1.
    5. Make sure that you change the "." (period) to the actual name of the computer that is running SQL Server. For example:

      \\ComputerA\pipe\MSSQL$s2000\sql\query

      Do not add the instance name. Click OK two times.
    SQL Server 6.5
    1. Start the SQL Server Client Configuration Utility.
    2. Click the Advanced tab.
    3. Type an alias name in the Server text box.
    4. Select Named Pipes as the DLL name.
    5. In the connection string, type the pipe name that you recorded in step 1.
    6. Make sure that you change the "." (period) to the actual name of the computer that is running SQL Server.

      For example:

      \\ComputerA\pipe\MSSQL$s2000\sql\query

      Do not add the instance name.
    7. Click Add/Modify, and then click Done.
Now you can use the server alias to connect to the instance by using by using the SQL Server 7.0 client tools Query Analyzer and Osql.exe, or by using the SQL Server 6.5 client tools ISQL/W and Isql.exe.
Modification Type:MajorLast Reviewed:6/30/2006
Keywords:kbinfo KB265808 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.