Automate SQL Server Client-Network Configuration

December 1, 1999

The problem

When connecting to SQL Server the Client network configuration determines which network library will be used.

With SQL Server comes the Client Network Utility which allows the setting of the library and some other options. If you do not want to use the default settings (Named Pipes/Auto Ansi To OEM conversion), you can either manually change the settings on each computer or use a policy file.

A solution

In this article you will learn how to create a policy file to automatically configure the Network Library settings for computers in your domain You can then deploy that file on your domain controllers to automatically configure the clients upon logon. (Remember that this only updates the settings on the machine. This process cannot be used to install new network libraries on a client.)

Policy Files and Policy Templates

Policy files can be used for Windows 9x and Windows NT Clients. When logging on to a domain controller the information from the policy file will be applied to the local registry of the client.

With Windows NT and Windows 9x ship predifined Policy templates which allow the setting of many Operation System settings. This articles describes how to create a new Policy Template to create Policies that update the registry information for the SQL Server Network Library.

How to - Step by Step

Step 1:

Creating the Policy Template

The Client network configuration for SQL Server can be found in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\. For this example I will create policy entries for the network library, the "AutoANSIToOEM" option and the "Use international settings" option.

The Policy Template file (a normal text file) starts by specifying the relevant Section in the Registry. As these settings are machine related, the Class will be Machine:

CLASS MACHINE

For this class we create a new category called SQL Server. The exact string is specified a bit further down in the file (in the [strings] section).

CATEGORY !!SQLServer

To be open for further amendments I include another subcategory call DBLibrary.

CATEGORY !!DBLibrary

Now the Policies can be defined. The first policy will allow you to select a network library.

POLICY !!NetworkLibrary

The keyname specifies where the values are stored in the registry.

KEYNAME SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

The value for the network library will be stored under DSQUERY. The default value is Windows Sockets.

ACTIONLISTOFF
VALUENAME "DSQUERY" VALUE !!DBMSSOCN
END ACTIONLISTOFF

For this value a dropdown list is created. The entries define all possible Network libraries that exist. Just delete the entries you do not intend to use.

PART !!DSQUERY DROPDOWNLIST REQUIRED
    VALUENAME "DSQUERY"
    ITEMLIST
; Named Pipes
        NAME !!DSNAMEDPIPE VALUE !!DBNMPNTW
; Windows Sockets
        NAME !!DSSOCKETS VALUE !!DBMSSOCN
; Multiprotocol
        NAME !!DSMULTI VALUE !!DBMSRPCN
; NWLink SPX/IPX
        NAME !!DSNWLINK VALUE !!DBMSSPXN
; Apple Talk
        NAME !!DSAPPLE VALUE !!DBMSADSN
; Banyan Vines
        NAME !!DSBANYAN VALUE !!DBMSVINN
    END ITEMLIST
END PART
END POLICY

So that is the end of the first policy. In policy editor (POLEDIT.EXE) it will now look like this:

The following lines show, how the settings for AutoAnsiToOEM setting and the "Use international settings" can be set.

KEYNAME "SOFTWARE\Microsoft\MSSQLServer\Client\DB-Lib"
POLICY !!DBLIBOPTIONS
VALUENAME "AutoANSIToOEM"
    VALUEON "ON"
    VALUEOFF "OFF"
    PART !!AUTOAnsi_Tip1 TEXT END PART
    PART !!AUTOAnsi_Tip2 TEXT END PART
END POLICY

POLICY !!DBLIBOPTIONS2
    VALUENAME "UseIntlSettings"
    VALUEON "ON"
    VALUEOFF "OFF"
    PART !!INTLSett_Tip1 TEXT END PART
    PART !!INTLSett_Tip2 TEXT END PART
END POLICY
END CATEGORY
END CATEGORY ; SQLSERVER

These lines define the values for checkboxes. For SQL Server the values ON and OFF are used.

The last thing missing is the strings section. Here all the strings used in the policy file are defined.

[strings]
SQLServer="Microsoft SQL Server"
Client="Client-Settings"
NetworkLibrary="Network Library"
DSNAMEDPIPE="Named Pipes"
DSSOCKETS="Windows Sockets"
DSMULTI="Multiprotocol"
DSNWLINK="NWLink IPX/SPX"
DSAPPLE="Apple Talk"
DSBANYAN="Banyan VINES"
DBMSSOCN="DBMSSOCN"
DBNMPNTW="DBNMPNTW"
DBMSRPCN="DBMSRPCN"
DBMSSPXN="DBMSSPXN"
DBMSADSN="DBMSADSN"
DBMSVINN="DBMSVINN"
DSQUERY="Select a Network-Library: "
DBLIBOPTIONS="Auto ANSI to OEM Conversion"
DBLIBOPTIONS2="Use International settings"
DBLibrary="DB Library"
AUTOAnsi_Tip1="Use automatic ANSI to OEM conversion"
AUTOAnsi_Tip2="when connecting to SQL Server"
INTLSett_Tip1="Use international settings"
INTLSett_Tip2="when connection to SQL Server"

Step 2:

Save the Policy Template file

Now you can save the File as SQL.ADM in the INF directory (Under Windows NT).

Step 3:

Use the Template with Policy Editor

Then you can start Policy Editor and open the Template (Options, Template).

Once this is done, a Policy file can be created or modified.

Step 4:

Create a new policy file

To create a new policy file click on File, New.

Here you can either define the settings for all computers in your network or create entries for the computers that run SQL Server Client Software.

Unfortunately there is no option to group computers. The fastest way to specify the same settings to a group of computers is to add them one by one. Then change the settings of one of the computers, use the Copy command to copy your changes to the clipboard. Now you can select all the computers to which you want to apply the settings and then paste the information.

Step 5:

Save the Policy File

Save the file as NTCONFIG.POL in your NETLOGON share. (Remember to save the file under SYSTEM32\REPL\EXPORT\SCRIPTS if you use replication).

The next time a user logs on the NTCONFIG.POL file will be parsed and the registry will be modified.

To quick check whether your Policy Template works, open your local registry with POLEDIT, change the entries and check the effect either with the SQL Server Client Network utility or with REGEDIT.

A warning

If you use system policies the local registries will be modified when a user logs on to the network. There is NO way to undo these registry changes. So make sure you only specifiy the settings for the computers you wish to update!

Download Policy Template files

English Version: SQLE.ADM

German Version: SQLG.ADM

Please send comments

Automate SQL Server Client-Network Configuration

The problem

A solution

Policy Files and Policy Templates

A warning

Download Policy Template files

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos