Automate SQL Server Client-Network Configuration

				
--**************************************
-- Name: Automate SQL Server Client-Network Configuration
-- Description: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. (http://www.itrain.de/sql/knowhow/setup/autosqlclient.asp)
-- By: Found on the World Wide Web
--**************************************

<table>
 <tr>
<td colSpan="2" width="80%"><b><font face="Verdana">A solution</font></b></td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%"><font face="Verdana">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.)<br>
 <br>
 </font></td>
 </tr>
 <tr>
<td colSpan="2" width="80%"><b><font face="Verdana">Policy Files and Policy
 Templates</font></b></td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%">
 <p><font face="Verdana">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. </font></p>
 <p><font face="Verdana">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.<br>
 <br>
 </font></p>
</td>
 </tr>
 <tr>
<td colSpan="2" width="80%"><b><font face="Verdana">How to - Step by Step</font></b></td>
 </tr>
 <tr>
<td vAlign="top" width="63"><font face="Verdana">Step 1:</font></td>
<td width="80%">
 <h3><font face="Verdana">Creating the Policy Template</font></h3>
</td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%">
 <p><font face="Verdana">The Client network configuration for SQL Server
 can be found in the registry under <b>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\</b>.
 For this example I will create policy entries for the network library, the
 "AutoANSIToOEM" option and the "Use international
 settings" option.</font></p>
 <p><font face="Verdana">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:</font></p>
 <table class="Example">
<tbody>
 <tr>
<td width="100%"><font face="Verdana"> <code>CLASS MACHINE</code></font></td>
 </tr>
</tbody>
 </table>
 <p><font face="Verdana">For this class we create a new category called SQL
 Server. The exact string is specified a bit further down in the file (<i><a href="#strings">in
 the [strings] section</a></i>). </font></p>
 <table class="Example">
<tbody>
 <tr>
<td width="100%"><code><font face="Verdana">CATEGORY !!SQLServer</font></code></td>
 </tr>
</tbody>
 </table>
 <p><font face="Verdana">To be open for further amendments I include
 another subcategory call DBLibrary.</font></p>
 <table class="Example">
<tbody>
 <tr>
<td width="100%"><font face="Verdana"> <code>CATEGORY !!DBLibrary</code></font></td>
 </tr>
</tbody>
 </table>
 <p><font face="Verdana">Now the Policies can be defined. The first policy
 will allow you to select a network library. </font></p>
 <table class="Example">
<tbody>
 <tr>
<td width="100%"><code><font face="Verdana">POLICY !!NetworkLibrary</font></code></td>
 </tr>
</tbody>
 </table>
 <p><font face="Verdana">The keyname specifies where the values are stored
 in the registry.</font></p>
 <table class="Example">
<tbody>
 <tr>
<td width="100%"><font face="Verdana"> <code>KEYNAME
 SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo</code></font></td>
 </tr>
</tbody>
 </table>
 <p><font face="Verdana"><br>
 The value for the network library will be stored under DSQUERY. The
 default value is Windows Sockets.</font></p>
 <table class="Example">
<tbody>
 <tr>
<td width="100%"><code><font face="Verdana">ACTIONLISTOFF<br>
     VALUENAME "DSQUERY" VALUE !!DBMSSOCN<br>
 END ACTIONLISTOFF</font></code></td>
 </tr>
</tbody>
 </table>
 <font face="Verdana"> </font>
 <p><font face="Verdana">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.<br>
 <br>
 </font>
 <table class="Example">
<tbody>
 <tr>
<td width="100%"><code><font face="Verdana">PART !!DSQUERY
 DROPDOWNLIST REQUIRED<br>
     VALUENAME "DSQUERY"<br>
     ITEMLIST<br>
 ; Named Pipes<br>
         NAME !!DSNAMEDPIPE
 VALUE !!DBNMPNTW<br>
 ; Windows Sockets<br>
         NAME !!DSSOCKETS VALUE
 !!DBMSSOCN <br>
 ; Multiprotocol<br>
         NAME !!DSMULTI VALUE !!DBMSRPCN<br>
 ; NWLink SPX/IPX<br>
         NAME !!DSNWLINK VALUE
 !!DBMSSPXN<br>
 ; Apple Talk<br>
         NAME !!DSAPPLE VALUE !!DBMSADSN<br>
 ; Banyan Vines<br>
         NAME !!DSBANYAN VALUE
 !!DBMSVINN <br>
     END ITEMLIST<br>
 END PART<br>
 END POLICY</font></code></td>
 </tr>
</tbody>
 </table>
 <b><font face="Verdana"><br>
 </font></b>
 <p><font face="Verdana">So that is the end of the first policy. In policy
 editor (POLEDIT.EXE) it will now look like this:</font></p>
 <p><font face="Verdana"><img border="0" src="http://www.itrain.de/sql/knowhow/setup/Poledit3.gif" width="385" height="445"></font></p>
 <p><font face="Verdana">The following lines show, how the settings for
 AutoAnsiToOEM setting and  the "Use international settings"
 can be set.</font></p>
 <table class="example">
<tbody>
 <tr>
<td width="100%"><code><font face="Verdana">KEYNAME
 "SOFTWARE\Microsoft\MSSQLServer\Client\DB-Lib"<br>
 POLICY !!DBLIBOPTIONS<br>
  VALUENAME "AutoANSIToOEM"<br>
     VALUEON "ON"<br>
     VALUEOFF "OFF"<br>
     PART !!AUTOAnsi_Tip1 TEXT END PART<br>
     PART !!AUTOAnsi_Tip2 TEXT END PART<br>
 END POLICY<br>
 </font>
 <p><font face="Verdana">POLICY !!DBLIBOPTIONS2<br>
     VALUENAME "UseIntlSettings"<br>
     VALUEON "ON"<br>
     VALUEOFF "OFF"<br>
     PART !!INTLSett_Tip1 TEXT END PART<br>
     PART !!INTLSett_Tip2 TEXT END PART <br>
 END POLICY<br>
 END CATEGORY<br>
 END CATEGORY ; SQLSERVER</font></code></p>
</td>
 </tr>
</tbody>
 </table>
 <p><font face="Verdana">These lines define the values for checkboxes. For
 SQL Server the values ON and OFF are used.</font></p>
 <p><font face="Verdana"><a name="strings"></a>The last thing missing is
 the strings section. Here all the strings used in the policy file are
 defined. </font></p>
 <table class="Example">
<tbody>
 <tr>
<td width="100%"><code><font face="Verdana">[strings]<br>
 SQLServer="Microsoft SQL Server"<br>
 Client="Client-Settings"<br>
 NetworkLibrary="Network Library"<br>
 DSNAMEDPIPE="Named Pipes"<br>
 DSSOCKETS="Windows Sockets"<br>
 DSMULTI="Multiprotocol"<br>
 DSNWLINK="NWLink IPX/SPX"<br>
 DSAPPLE="Apple Talk"<br>
 DSBANYAN="Banyan VINES"<br>
 DBMSSOCN="DBMSSOCN"<br>
 DBNMPNTW="DBNMPNTW"<br>
 DBMSRPCN="DBMSRPCN"<br>
 DBMSSPXN="DBMSSPXN"<br>
 DBMSADSN="DBMSADSN"<br>
 DBMSVINN="DBMSVINN"<br>
 DSQUERY="Select a Network-Library: "<br>
 DBLIBOPTIONS="Auto ANSI to OEM Conversion"<br>
 DBLIBOPTIONS2="Use International settings"<br>
 DBLibrary="DB Library"<br>
 AUTOAnsi_Tip1="Use automatic ANSI to OEM conversion"<br>
 AUTOAnsi_Tip2="when connecting to SQL Server"<br>
 INTLSett_Tip1="Use international settings"<br>
 INTLSett_Tip2="when connection to SQL Server"</font></code></td>
 </tr>
</tbody>
 </table>
 <p><font face="Verdana"> </font></p>
</td>
 </tr>
 <tr>
<td vAlign="top" width="63"><font face="Verdana">Step 2:</font></td>
<td width="80%">
 <h3><font face="Verdana">Save the Policy Template file</font></h3>
</td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%"><font face="Verdana">Now you can save the File as SQL.ADM in
 the INF directory (Under Windows NT).<br>
 <br>
 </font></td>
 </tr>
 <tr>
<td vAlign="top" width="63"><font face="Verdana">Step 3:</font></td>
<td width="80%">
 <h3><font face="Verdana">Use the Template with Policy Editor</font></h3>
</td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%">
 <p><font face="Verdana">Then you can start Policy Editor and open the
 Template (<u>O</u>ptions, <u>T</u>emplate).</font></p>
 <p><font face="Verdana"><img border="0" src="http://www.itrain.de/sql/knowhow/setup/Poledit1.gif" width="317" height="197"></font></p>
 <p><font face="Verdana">Once this is done, a Policy file can be created or
 modified.</font></p>
 <p><font face="Verdana"> </font></p>
</td>
 </tr>
 <tr>
<td vAlign="top" width="63"><font face="Verdana">Step 4:</font></td>
<td width="80%">
 <h3><font face="Verdana">Create a new policy file</font></h3>
</td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%"><font face="Verdana">To create a new policy file click on
 File, New.</font>
 <p><font face="Verdana">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. </font></p>
 <p><font face="Verdana">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.<br>
 </font></p>
</td>
 </tr>
 <tr>
<td vAlign="top" width="63"><font face="Verdana">Step 5:</font></td>
<td width="80%">
 <h3><font face="Verdana">Save the Policy File</font></h3>
</td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%">
 <p><font face="Verdana">Save the file as NTCONFIG.POL in your NETLOGON
 share. (Remember to save the file under SYSTEM32\REPL\EXPORT\SCRIPTS if
 you use replication).</font></p>
 <p><font face="Verdana">The next time a user logs on the NTCONFIG.POL file
 will be parsed and the registry will be modified.</font></p>
 <p><font face="Verdana">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.</font></p>
 <p><font face="Verdana"> </font></p>
</td>
 </tr>
 <tr>
<td colSpan="2" width="80%"><b><font face="Verdana">A warning</font></b></td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%"><font face="Verdana">If you use system policies the local
 registries will be modified when a user logs on to the network. There is <b>NO</b>
 way to undo these registry changes. So make sure you only specifiy the
 settings for the computers you wish to update!</font>
 <p><font face="Verdana"> </font></p>
</td>
 </tr>
 <tr>
<td colSpan="2" width="80%"><b><font face="Verdana">Download Policy Template
 files</font></b></td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%"><font face="Verdana">English Version:   <a href="http://www.itrain.de/sql/knowhow/setup/sqle.adm">SQLE.ADM</a></font>
 <p><font face="Verdana">German Version:  <a href="http://www.itrain.de/sql/knowhow/setup/sqlg.adm">SQLG.ADM</a></font></p>
</td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%"></td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%"><font face="Verdana">Please send comments to <a href="mailto:svenh@itrain..de">svenh@itrain.de</a></font></td>
 </tr>
 <tr>
<td width="63"></td>
<td width="80%"><font face="Verdana">Visit my homepage (German) under <a href="http://www.itrain.de">www.itrain.de</a></font></td>
 </tr>
</table>