ACC2000: Cannot Connect to Instance of SQL Server 2000

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: 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.

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.

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.

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