HomeMS SQL

Automate SQL Server Client-Network Configuration

Sven Hammesfar
By Sven Hammesfar

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

Sven Hammesfar
Sven Hammesfar
Previous article
DTSBrowse Utility (VB)
Next article
DTS How to…dynamically change…Connections

Related Articles

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

Advertisers

Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.

Advertise with Us

Menu

Our Brands

Property of TechnologyAdvice.
© 2024 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.