Can I Create and Delete a DSN Using a Script?

Can I Create and Delete a DSN Using a Script?

  • Comments 14
  • Likes
Hey, Scripting Guy! Question

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

-- KC

SpacerHey, Scripting Guy! AnswerScript Center

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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Using delete script to delete odbc still leave odbc setting when you go to data source in control panel. Is there any way to clean it up as well

  • If the client computer has never had any ODBC data sources, it is necessary to create the "ODBC Data Sources" key or the script won't work:

    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"

    objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath

  • yes, it is important that the "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources" key exists!

  • Is there a way to specify alternate domain credentials for an ODBC DSN for SQL Server? For example, I'm logged into domain mycompany.com, but the database is on a sql server in the domain yourcompany.org. I have user accounts on both domains. If I try to create a trusted connection, the DSN fails because I'm not logged into the same domain as the sql server.

  • If SQLSerever is set to use Integrated Windows Authentication then you need the trust to work and your local account must have a login on SQLServer.

    You can test this with the DSN builder.  Just enter the foreign username and passord as well as the server and database and click the test button.

  • Can I change the default database setting as part of this DSN setup?

  • A blog is not really a good place to answer questions.  The Scrpting Guy's Blog has a companion website/forum with numerous (1000's) of scripts and the forum has many excellent moderators who can answer your questions to any level of detail.

    Forums: social.technet.microsoft.com/.../ITCG

    Script Repository: gallery.technet.microsoft.com/scriptcenter

  • Of course, you can add / delete the DSN via some registry functions. However, there is a much simpler method available in Windows 8 (Release Preview version) and Windows Server 2012 (Release Candidate version).

    You can use the Get-OdbcDsn to retrieve the list of all ODBC DSN installed in the system.

    And you can use Add-OdbcDsn and Remove-OdbcDsn to add / remove ODBC DSN.

    You can also use Set-OdbcDsn to modify an ODBC DSN easily.

    Try it!!

    See the blog article for more detail: blogs.msdn.com/.../odbc-dsn-management-in-the-next-release-of-windows-code-named-windows-8-and-windows-server-8.aspx

    Thanks,

    Ming.

    WDAC Team, Microsoft.

    (This post include information about a pre-release windows and is subject to change in future releases.)

  • The blog article for the PowerShell Cmdlet on Windows 8 (Release Preview version) and Windows Server 2012 (Release Candidate version) is actually:

    blogs.msdn.com/.../odbc-dsn-management-in-the-next-release-of-windows-code-named-windows-8-and-windows-server-8.aspx

    There is some copy-and-paste problem so that the link does not appear correct before.

    Thanks,

    Ming.

    WDAC Team, Microsoft.

    (This post include information about a pre-release windows and is subject to change in future releases.)

  • how to add username and password ?

  • works for me!!..
    but this is creating It in SYSTEM DSN. Any advice on how to change it to USER DSN.

  • ok figured it.. Just change HKEY_LOCAL_MACHINE to HKEY_CURRENT_USER
    Also &H80000001

  • The ODBC driver I'm using is: ODBC Driver 11 for SQL Server and has a Failover Partner = 'Server1', FailoverPartnerSPN = 'MSSQLSvc/Server1.contoso.com:Server1', ConnectRetryCount = 3, ConnectRetryInterval = 30. How would I add this to this script to make this work. FYI - I've installed x64 drivers on x64 machines and x86 on x86 machines.
    Thanks,
    SQL4Life

  • Anyone get the following errors:
    Ampersand not allowed. The & operator is reserved for future use; use "&" to pass ampersand as a string
    Unexpected token '&' in expression or statement
    Missing closing ')' in expression.