Select the product you need help with

PRB: Configuring DSNs with SQL Server Net-Libraries

Article ID: 289573 - View products that this article applies to.

This article was previously published under Q289573

SYMPTOMS

When you create or configure ODBC data source names (DSNs) using the Microsoft SQL Server ODBC driver, issues such as the following may occur:

Switching of Net-Library protocols.
A new DSN's protocol is used for older DSNs, or vice versa.

Back to the top | Give Feedback

If you use the server name (for example, the SQL Server instance name) for creating multiple DSNs, modifying the network protocol for any one of them will affect all the DSNs that are using this name.

Also, from Microsoft Data Access Components (MDAC) 2.6 and later, the default network library is TCP/IP. If you use a server name (in other words, you do not create a server alias), even if you configure your DSN to use named pipes, it will revert back to TCP/IP. To resolve this problem, follow the steps given in the "Workaround" section.

Back to the top | Give Feedback

RESOLUTION

Create and use server aliases with the required Net-Library settings using the ODBC Administrator or the SQL Server Client Network Utility, Cliconfg.exe. See the "More Information" section for details.

WORKAROUND

In the Client configuration utilities for SQL Server, create an alias using a different name than the server name.
Create or modify a DSN, and for the server name, choose the alias (not the server) from step 1.
Save the DSN and reopen.

Note that this time the network library persists.

Back to the top | Give Feedback

MORE INFORMATION

When you specify or select the server name in the Server select box in the Microsoft SQL Server DSN Configuration dialog box from the ODBC Administrator during DSN creation, it creates a server alias with the specified name. This is actually a server alias name rather than the server name itself. If multiple DSNs use the same name, they all use the latest protocol settings of that server alias.

The configuration information of the server aliases is stored in the registry under the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

ODBC Administrator

To add a server alias using the ODBC Administrator, follow these steps:

In Control Panel, browse to the ODBC Administrator.
Click Add and select SQL Server as the driver. Then enter a name and description for the DSN. In the Server select box, specify a new server alias name and then click Next.
Click the Client Configuration button. Enter the SQL Server name in the Server name text box under Connection parameters. Also, select the required Net-Library under Network Libraries. Then click OK and complete the steps to create the DSN.

Client Network Utility

The Client Network Utility, Cliconfg.exe, ships with several products, including Microsoft SQL Server and Microsoft Data Access Components (MDAC) version 2.1 SP2 and later.

This utility is typically placed in the C:\WINNT\SYSTEM32 folder on Microsoft Windows 2000 and Microsoft Windows NT systems and in the C:\WINDOWS\SYSTEM folder on Microsoft Windows 9x systems.

To start this utility, follow these steps:

If the SQL Server client is installed, on the Start menu, point to Programs, point to Microsoft SQL Server, and then click Client Network Utility.
Otherwise, on the Start menu, click Run.
Type cliconfg in the Open text box and then click OK.

To add a server alias, follow these steps:

Click the Alias tab and then click Add.
Type a new alias name in the Server alias text box and choose the required Net-Library under Network libraries.
Enter the SQL Server name in the Server name text box under Connection parameters. You can also configure the additional parameters based on the Net-Library. Then click OK.

NOTE: You can use the Remove and Edit buttons under Alias tab to delete and modify the existing server aliases.