How to connect to SQL Server by using an earlier version of SQL Server

Article translations Article translations
Article ID: 265808 - View products that this article applies to.
This article was previously published under Q265808
Expand all | Collapse all

On This Page

Summary

Microsoft SQL Server 2005 and Microsoft SQL Server 2000 introduced multiple instances. However, client tools such as Query Analyzer or ISQL/W may have problems connecting to the nondefault named instances.

Note Query Analyzer and ISQL/W are available in earlier versions of Microsoft SQL Server.

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 following format:
\\computer_name\instance_name
We recommend that you upgrade the client tools and the MDAC version to work with SQL Server 2005 or with 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 has 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. However, you can connect to a named instance of SQL Server 2005 or a named instance of SQL Server 2000 by using the following tools:
  • SQL Server 7.0 client tools Query Analyzer together with Osql.exe
  • Microsoft SQL Server 6.5 client tools ISQL/W together with Isql.exe

The following steps describe how to configure a server alias to use either TCP/IP sockets or Named Pipes to connect directly to a named instance of SQL Server 2000 or to a named instance of SQL Server 2005 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. To do this, use one of the following methods, depending on which version of SQL Server that you are running.

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

      Note InstanceName is a placeholder for the named instance of SQL Server 2005.
    3. On the Protocol tab, note the value of the Listen All item. See image
    4. Click the IP Addresses tab. See image
      • 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 Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager) Microsoft Developer Network (MSDN) website.
    5. Click OK.

    SQL Server 2000
    1. On the server that is running SQL Server 2000, start the Server Network Utility. To do this, click Start, click All Programs, click Microsoft SQL Server, and then click 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 so that you can use it later.
  2. Configure the server alias on the client computer. To do this, use one of the following methods, depending on your version of SQL Server.

    SQL Server 2000
    1. Start the Client Network Utility. To do this, click Start, click Run, type cliconfg.exe, and then press Enter.
    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. To do this, click Start, click Run, type cliconfg.exe, and then press Enter.
    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 Client Network Utility. To do this, click Start, click Run, type cliconfg.exe, and then press Enter.
    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 together. Make sure that the computer name is followed by a comma (,). Then, type the port number that you recorded in step 1. For example, type the following computer name and port number:
      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 following tools:
  • SQL Server 7.0 client tools Query Analyzer together with Osql.exe
  • Microsoft SQL Server 6.5 client tools ISQL/W together with 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. To do this, use one of the following methods, depending on which version of SQL Server that you are running.

    SQL Server 2005
    1. Open SQL Server Configuration Manager, and then expand SQL Server 2005 Network Configuration. See image
    2. Click Protocols for InstanceName, and then double-click Named Pipes.
    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. See image

      Note If the value of the Pipe Name item is not set, you must set it. For more information about how to configure a server to listen on another pipe, visit the Configure a Server to Listen on an Alternate Pipe MSDN website.
    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 so that you can use it later.
  2. Configure the server alias on the client computer. To do this, use one of the following methods, depending on which version of SQL Server that you are running.

    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 the following:
      \\IPAddess\pipe\MSSQL$Instance\sql\query

      Note IPAddess 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
      Notes
      • Do not add the instance name.
      • In this example, ComputerA replaces the period.
    6. 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
      Notes
      • Do not add the instance name.
      • In this example, ComputerA replaces the period.
    7. Click Add/Modify, and then click Done.
Now you can use the server alias to connect to the instance by using the SQL Server 7.0 client tools Query Analyzer together with Osql.exe or by using the SQL Server 6.5 client tools ISQL/W together with Isql.exe.

Properties

Article ID: 265808 - Last Review: July 12, 2013 - Revision: 11.1
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 Edition
  • 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 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
Keywords: 
kbsqlsetup kbinfo KB265808

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com