Hey, Scripting Guy! Is it possible to create an ODBC Data Sources Name (DSN) on the fly and then remove it when done?

— KC

Hey, KC. Is it possible to create and then delete an ODBC DSN? To tell you the truth, we weren’t entirely sure. As it turns out, however the answer is this: as long as you’re willing to write a script that modifies the registry, then yes, you can programmatically create and delete DSNs.

Note. If you aren’t sure what a DSN is, it’s a way for you to connect to a database merely by referencing the DSN name, and without having to specify the entire path to the database. That’s particularly handy if you have a bunch of scripts that access this database. If you hardcode the path into the scripts and then move the database from one server to another, you’ll have to edit the path on each and every script. If you use a DSN, however, you won’t have to edit the scripts at all; instead, you just need to modify the DSN. That’s one change versus hundreds of changes (depending on how many scripts you have).

What we’re about to show you is a script that creates a DSN that uses SQL Server. Is that important? Yes. You can create DSNs that use other ODBC databases (Access, Oracle, FoxPro, whatever). However, the registry keys and values used when creating a DSN to access an Oracle database aren’t necessarily the same as those used to access a SQL Server database. If you’re using SQL Server, you can just copy our script and change the names of the database, the server, etc. If you’re using a different database, we’d recommend that you manually create a DSN. After you do that, fire up your favorite registry editor and look for the DSN here:

HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

And here:

HKLM\SOFTWARE\ODBC\ODBC.INI\Name You Gave the DSN

Take a look at the registry keys and values required for that type of database, and then plug that information into our script. If you aren’t sure how to do that, give us a week or two, and we’ll see what we can do about getting some sample scripts for different ODBC databases posted in the Script Center.

At any rate, here’s a script that creates a DSN that uses SQL Server:

Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = “.”
Set objReg=GetObject(“winmgmts:{impersonationLevel=impersonate}!\\” & _ 
    strComputer & “\root\default:StdRegProv”)
strKeyPath = “SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources”
strValueName = “Script Repository”
strValue = “SQL Server”
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strKeyPath = “SOFTWARE\ODBC\ODBC.INI\Script Repository”
objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
strKeyPath = “SOFTWARE\ODBC\ODBC.INI\Script Repository”
strValueName = “Database”
strValue = “Script Center”
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = “Driver”
strValue = “C:\WINDOWS\System32\SQLSRV32.dll”
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = “Server”
strValue = “atl-sql-01”
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = “Trusted_Connection”
strValue = “Yes”
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

Yes, we know, it’s a bit long, but that’s because there are a number of registry keys and values that have to be created. For the most part, though, the script is pretty straightforward. We start by setting the constant HKEY_LOCAL_MACHINE to &H80000002, the value required to connect to the HKLM portion of the registry. We then bind to the WMI service, and to the StdRegProv class (which – as we always remind you – happens to be in the root\default namespace).

From there we create a new registry value (HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository), with Script Repository the name we’re giving to our new DSN.We set the value of this, well, value to SQL Server, because that’s the type of database we’re connecting to.

Got that? After that we create a new registry key: HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository (note that the key name is the same as our DSN Name). We then need to configure the following registry values:

Database — Name of the actual database we want to connect to. In this example, the database is named ScriptCenter.

Driver — Path to SQL Server ODBC drive. In this example, that path is C:\WINDOWS\System32\SQLSRV32.dll.

Server — Name of the server where the database is found. In this case, the server name is atl-sql-01.

Trusted_Connection — Tells SQL Server to use our logon credentials when we access the database. This enables us to connect to the database without having to supply a user name and password.

That’s it; run the script, and you’ll have yourself a fully-functioning DSN.

And what about getting rid of that DSN once you’re finished with it? Well, that’s equally easy: after all, we just need to delete the registry keys and values our first script created. Here’s a script that will delete the DSN Script Repository :

Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = “.”
Set objReg=GetObject(“winmgmts:{impersonationLevel=impersonate}!\\” & _ 
    strComputer & “\root\default:StdRegProv”)
strKeyPath = “SOFTWARE\ODBC\ODBC.INI\Script Repository”
objReg.DeleteKey HKEY_LOCAL_MACHINE, strKeyPath
strKeyPath = “SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources”
strValueName = “Script Repository”
objReg.DeleteValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName

Note that what this script does is delete the first registry value we created (Script Repository), and then deletes the registry key we created. We don’t have to delete the individual registry values within that key, because deleting HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository will delete all the values as well.