ACC2000: Cannot Connect to Instance of SQL Server 2000 (274383)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q274383
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

Microsoft Access may experience difficulty connecting to nondefault named instances of SQL Server 2000. For example, if you try to connect to a named instance by using the \\computer_name\instance_name format, you may see one of the following error messages:
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.

-or- 

Microsoft SQL Server Login

Connection failed:
SQL State: '01000'
SQL Server Error: 67
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen(CreateFile()).
Connection failed:
SQL State: '08001'
SQL Server Error: 67
[Microsoft][ODBC SQL Server Driver]Client unable to establish connection

-or- 

Microsoft Data Link Error

Test connection failed because of an error in initializing provider.  Client unable to establish connection.
NOTE: The actual error message you see may differ somewhat from those listed.

You see this problem whether you are connecting from an Access project (.adp) or establishing a Data Source Name to connect that uses ODBC.

CAUSE

In order to use the \\computer_name\instance_name format to connect to SQL Server, you must have Microsoft Data Access Components (MDAC) version 2.6 or later installed.

RESOLUTION

Microsoft recommends that you upgrade your system to work with Microsoft SQL Server 2000 by updating to the latest version of MDAC. However, to connect to a nondefault instance of SQL Server 2000, you can use the following workaround.

The following steps describe how to configure a server alias to use either TCP/IP sockets or Named Pipes to connect to a SQL Server directly without using the \\computer_name\instance_name format.

Configure a Server Alias to Use TCP/IP Sockets

NOTE: To configure an alias to use TCP/IP, you must provide the server name and TCP/IP port number.
  1. Determine the SQL Server instance port number.

    To find the SQL Server instance port number:
    1. On the Microsoft SQL Server 2000 server, start the SQL Server Network Utility.
    2. Click the General tab, and then select the instance that you want from the Instances menu.
    3. Click TCP/IP, and then click Properties. The port number for this instance is displayed.

      Write down this port number for later use.
  2. Configure the server alias on the client computer.
    1. Start the Client Network Utility. If you have installed the SQL Server client tools, or if you have installed MDSE, this utility is available as a shortcut on your Start menu. If not, you can start it directly. The file name is Cliconfg.exe, and it is usually stored in the System folder.
    2. On the General tab of the Server Alias Configuration dialog box, click Add, and then click TCP/IP.
    3. Enter an alias name in the Server Alias box. The alias can contain any name.
    4. Enter the SQL Server 2000 computer name or IP address in the Computer Name box. Do not add an instance name.
    5. In the Port Number box, enter the port number that you recorded in step 1. Click OK twice.

Configure an Alias to Use Named Pipes

NOTE: In order to configure an alias to use Named Pipes, you must provide a server name and a pipe name.

  1. Determine the server instance pipe.
    1. On the Microsoft SQL Server 2000 server, start the Server Network Utility.
    2. On the General tab, select the instance that you want from the Instances menu.
    3. Click Named Pipes, and then click Properties.

      Write down the pipe name for later use.
  2. Configure the server alias on the client computer.
    1. Start the Client Network Utility. If you have installed the SQL Server client tools, or if you have installed MDSE, this utility is available as a shortcut on your Start menu. If not, you can start it directly. The file name is Cliconfg.exe, and it is usually stored in the System folder.
    2. On the General tab of the Server Alias Configuration dialog box, click Add, and then click Named Pipes.
    3. Enter an alias name in the Server Alias box. The alias can contain any name.
    4. Enter the pipe name that you recorded in step 1.
    5. Make sure that you change the "." (period) to the actual SQL Server name, for example:

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

      Do not add the instance name. Click OK twice.
    You can now use the server alias to connect to the instance by using Microsoft Access.

REFERENCES

For additional information about this subject, click the article number below to view the article in the Microsoft Knowledge Base:

265808 INF: How to Connect to an SQL Server 2000 Named Instance with the Previous Version's Client Tools

Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbfix kbprb KB274383
©2004 Microsoft Corporation. All rights reserved.