Scripting DSN Creation

Mr. Script explains how you can edit the Registry with scripting to automate the creation of Data Source Names.

• By Chris Brooke
• November 01, 2004

Perhaps you have seen the Mr. Script version of the Standard Registry Warning: "Changes to the registry are dynamic. Any and all errors will result in unpredictable behavior including, but not limited to, failure of services, failure of components, failure of Windows to boot, complete erasure of your hard drive, and/or a rift in the space-time continuum causing the extinction of all life in the universe. Never make changes to the Registry. In fact, never even look at it in regedit. Ever. Forget that it even exists. Forget … forget … forget."

The reason we're going to ignore our own advice and edit the Registry is so that we can automate the creation of Data Source Names (DSNs). Now, as I mentioned last month, this is one of those tasks that is usually best left to the GUI tools. However, our reader Kevin Jones still needs an answer to his question asking for a way to do this via scripting. Mr. Script offers the following:

package>

CreateDSN.wsf
This script creates a System DSN on each
Computer listed in C:\computers.txt

 job>
 object
  id="objConn"
  progid="ADODB.Connection"
  reference
 />
 object
  id="objRS"
  progid="ADODB.Recordset"
  reference
 />
 script language="VBScript">
 'Declare Registry Constants
 Const HKEY_CURRENT_USER=&H80000001
 Const HKEY_LOCAL_MACHINE=&H80000002
 Const HKEY_USERS=&H80000003

 Dim objReg
 Dim strDSN, strDBName, strDescription
 Dim strDriverPath, strLastUser, strServer
 Dim strTrustedConn, strDriverName
 Dim strComputer, strPath

 'Assign DSN data to variables
 strDSN="MySQLDSN"
 strDBName="MyDatabase"
 strDriverPath="C:\WINNT\System32\sqlsrv32.dll"
 strLastUser="sa"
 strServer="MySQLServer"
 strTrustedConn="Yes"
 strDescription="My SQL Database"
 strDriverName="SQL Server"
 strPath="SOFTWARE\ODBC\ODBC.INI\" & strDSN

 'Get list of computers from ADO
 objConn.Open _
  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\;" & _
  "Extended Properties=""text; HDR=YES; FMT=
  Delimited"""

 objRS.Open "SELECT * FROM computers.txt", _
 objConn, adOpenStatic, adLockOptimistic, adCmdText

 'Connect to each computer in turn
 Do Until objRS.EOF
  strComputer=objRS.Fields.Item("Computer")

 Set objReg=GetObject _
  ("winmgmts:{impersonationLevel=impersonate}//" _
  & strComputer & "/root/default:StdRegProv")

 objReg.CreateKey _
  strComputer, HKEY_LOCAL_MACHINE, strPath

 objReg.SetStringValue HKEY_LOCAL_MACHINE, _
  strPath, "Database", strDBName
 objReg.SetStringValue HKEY_LOCAL_MACHINE, _
  strPath, strPath, "Description", strDescription
 objReg.SetStringValue HKEY_LOCAL_MACHINE, _
  strPath, strPath, "Driver", strDriverPath
 objReg.SetStringValue HKEY_LOCAL_MACHINE, _
  strPath, strPath, "LastUser", strLastUser
 objReg.SetStringValue HKEY_LOCAL_MACHINE, _
  strPath, strPath, "Server", strServer
 objReg.SetStringValue HKEY_LOCAL_MACHINE, _
  strPath, strPath, "Trusted_Connection", strTrustedConn

 'Ensure that the DSN gets listed in ODBC
 objReg.SetStringValue HKEY_LOCAL_MACHINE, _
  "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", _

  strDSN , strDriverName
 Set objReg=Nothing
 objRS.MoveNext
 Loop

 /script>
 /job>
/package>

The particularly observant will likely notice that I declared some constants that I did not use—specifically, WMI Registry key constants. I left those in the script in case you want to create User DSNs for yourself or a specified user, rather than System DSNs that apply to the entire computer. The good news is that once you change the "root" key, the path remains the same, except for HKEY_USERS—where you would need to make sure you add the path to the appropriate user at the beginning of the path string (the rest remains the same).

Before You Run It
In order to answer Kevin's question completely, we configured this script to use Active Data Objects (ADO) to read a list of computer names from a text file: computers.txt, which it expects to find in the root of C:\. It then cycles through the list, connects to each computer in turn, and uses Windows Management Instrumentation (WMI) to create the appropriate Registry entries for the system DSN.

Another important distinction is that the DSNs created are pointing to a SQL Server database. I did this for two reasons. First, if you're creating DSNs on multiple machines, odds are better than even-money that the data will (or, at the least, should) reside on a SQL Server. Second, SQL Server is the most straightforward DSN connection. By contrast, DSNs that point to Access, dBase, Excel or even text files require an additional "Engines" key containing the appropriate engine settings. If you need to create DSNs that point to non-SQL databases, create some first using the graphical ODBC applet in Control Panel, then view the resultant Registry keys. That will give you a sense for all the necessary entries that should be included when you go to write your script.

Relatively Safe
The good thing about this script is that it only creates keys. Assuming that you have no DSNs already using the names we used above, running this script should have no detrimental effects to your system (even if you're using bogus DSN names for debugging purposes). We therefore avoid the risk of deleting any vital Registry keys and completely disabling our system. Even so, the standard Registry warning applies. Be careful out there.