PowerShell – Working with ODBC DSN Connection

Posted on June 7, 2009 by maxt2posh

How can we add ODBC connections without opening the Windows “ODBC Administrator” application?

Works in Windows Vista and Windows 7 RC.   We are going from this:

To this:

PS C:\> Add-ODBCconnection2HKCU.ps1

Here’s my sample of my registry before I run my PowerShell script:

I’m going to add an SQL ODBC connection to my local SQL Server “Developer” database which can be use in an MS Access and/or Excel application.

First, let us take a look at a VBScript sample that does this process.

(VBScript code modify from the Microsoft Scripting Center: http://tinyurl.com/eyk9t )

VBScript Code “AddODBCline.vbs” – (see how many lines of code?)

I’m not going to dissect VBScript, but this code works fine. Now, let take a look at PowerShell script that will do the same thing… and you guessed right!! You end up using less code and easy to read.

I basically study the VBScript work and then I found some blogs about build a registry keys using PowerShell script.

Using PowerShell make it easy for administrators to access and work with Registry keys. Use the CmdLet “Get-PSDrive” to see all predefined drive you can directly navigate and work. One of these is the Registry drives: “HKCU:” and “HKLM” drives.

Then, what’s needed to build these script?

  1. Create a variable to hold the HKCU path (not really require)
  2. Use the following DOS-like commands: “CD”, “MD”.
  3. Use the PowerShell Cmdlet: “Set-ItemProperty” (use the Help to list available parameters)
  4. Then, save everything in *.ps1 file.
  5. Additional considerations:
    1. Folder names with spaces, should be enclosed in single quotes.
    2. Run scripts as an “Administrator”

PowerShell – Add-ODBCConnect2HKCU.ps1 (how many lines of code?)

Last check both the Registry and your Windows ODBC Administrator application to see the added connection,

Now, you can use this connections in your Office application. If you need to remove the ODBC connection(s) then use the following script:

Remove-ODBCConnection2HKCU.ps1

Of course, you can keep enhancing these scripts and make them more powerful. 

Have fun with PowerShell!!

Download all scripts here:http://cid-a034d6a0ddc4e64e.skydrive.live.com/embedrow.aspx/PowerShellRegistryScripts2009

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in PowerShell. Bookmark the permalink.

7 Responses to PowerShell – Working with ODBC DSN Connection

  1. Chris says:
    June 10, 2011 at 3:29 am

    The link to the scripts is broken.
    Too bad, thank you for the screenshots and explanations anyways! Very helpfull!

  2. Norbin Cruz says:
    August 10, 2011 at 3:40 pm

    Thanks for sharing, I was able to get it to work. I had to type it from the picture file, but hey it works. Now, I need to learn how to specify a SQL credential for a connection.

  3. Pak-Ming Cheung (WDAC Team, Microsoft) says:
    June 27, 2012 at 1:39 am

    Of course, you can add a 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).

    For example, the example task can be done easily with the following single-line cmdlet call:

    Add-OdbcDsn LocalDeveloper –DriverName “SQL Server” -DsnType User -Platform 32-bit -SetPropertyValue @(‘Description=Local Developer Database’, ‘Server=Max-PCWIN1’, ‘Database=Developer’, ‘Trusted_Connection=Yes’)

    Explore more possibility with the family cmdlet set!!

    See the blog article for more detail: http://blogs.msdn.com/b/data/archive/2012/04/06/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.)

  4. David Howell (@davedecahedron) says:
    April 22, 2016 at 12:46 am

    Add-OdbcDsn is awesome, highly recommended. Works for any driver, although the possible values for
    -SetPropertyValue

    are not well defined so have to do some trial and error to find their names.

  5. Pak-Ming Cheung says:
    April 22, 2016 at 12:42 pm

    @David, You may want to create a DSN on a test machine with the GUI, and then use the Get-OdbcDsn to retrieve the desired properties key-value pairs. And then you can use Add-OdbcDsn to add DSN on other remote machines……

  6. Brian Watts says:
    January 5, 2017 at 8:59 am

    Hoping to find a version of your script that can first *export* existing DSN settings so that I can easily *import* them to a new machine I’m setting up

  7. 86Luca says:
    August 27, 2017 at 6:28 am

    I see you don’t monetize your blog, don’t waste your traffic, you can earn extra bucks every month
    because you’ve got high quality content. If you want to know how to make extra $$$, search for:
    best adsense alternative Wrastain’s tools

Leave a comment