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
Whenever you run a script, you need to take care to ensure no unintentional damage is done to production systems. However, this month, we're going to be scripting changes to
(cue scary music)—the Registry! As such, an extra dose of caution is the order of the day. Nowhere is Windows more unforgiving of inadvertent slip-ups than in the Registry.
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.