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:
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).
To be open for further amendments I include another
subcategory call DBLibrary.
Now the Policies can be defined. The first policy will
allow you to select a network library.
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
» 
Comment and Contribute
