Microsoft KB Archive/274383

= ACC2000: Cannot Connect to Instance of SQL Server 2000 =

Article ID: 274383

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.  Determine the SQL Server instance port number.

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

Write down this port number for later use.</ol> </li> Configure the server alias on the client computer. <ol style="list-style-type: lower-alpha;"> 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.</li> On the General tab of the Server Alias Configuration dialog box, click Add, and then click TCP/IP.</li> Enter an alias name in the Server Alias box. The alias can contain any name.</li> Enter the SQL Server 2000 computer name or IP address in the Computer Name box. Do not add an instance name.</li> In the Port Number box, enter the port number that you recorded in step 1. Click OK twice.</li></ol> </li></ol>

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.

<ol> Determine the server instance pipe. <ol style="list-style-type: lower-alpha;"> On the Microsoft SQL Server 2000 server, start the Server Network Utility.</li> On the General tab, select the instance that you want from the Instances menu.</li> Click Named Pipes, and then click Properties.

Write down the pipe name for later use.</li></ol> </li> Configure the server alias on the client computer. <ol style="list-style-type: lower-alpha;"> 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.</li> On the General tab of the Server Alias Configuration dialog box, click Add, and then click Named Pipes.</li> Enter an alias name in the Server Alias box. The alias can contain any name.</li> Enter the pipe name that you recorded in step 1.</li> Make sure that you change the &quot;.&quot; (period) to the actual SQL Server name, for example:

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

Do not add the instance name. Click OK twice.</li></ol>

You can now use the server alias to connect to the instance by using Microsoft Access.</li></ol>

<div class="references_section">