PowerShell – Working with ODBC DSN Connection

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

Advertisements

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.

6 Responses to PowerShell – Working with ODBC DSN Connection

  1. Chris says:

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

  2. Norbin Cruz says:

    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:

    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. 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:

    @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:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s