Working with ODBC Connections in Powershell

ODBC Connections in Powershell

This post explains how to work with ODBC connections in Powershell. Powershell 4 introduced new cmdlets that make it very easy to create and manage ODBC connections. We use the .NET assembly to use ODBC connections present on the system to query the database.

ODBC connections in Powershell 4.0 and higher

Powershell 4 introduced these 4 cmdlets to create, manage and remove ODBC connections. If you for some reason still are on Powershell 3.0, I recommend you to upgrade to Powershell 5.0 which is the newest version at the time of this blog post. Managing ODBC connections in Powershell 3 or older is not fun and requires you to either modify registry or use an .exe file to manage them for you.

  • Add-OdbcDsn
  • Get-OdbcDsn
  • Remove-OdbcDsn
  • Set-OdbcDsn

Add a new ODBC connection with Powershell

Use the the cmdlet Add-OdbcDsn to create a new ODBC connection. The example code creates a new ODBC connection named MyPayroll.

Get an ODBC connection with Powershell

Use the the cmdlet Get-OdbcDsn to get a Powershell object of an ODBC connection. The following code example returns an object containing the 32-bit ODBC configuration named MyPayroll.

Change properties on an ODBC conenction with Powershell

Use the cmdlet Set-OdbcDsn to change the properties of an existing ODBC connection. The following code example change the ODBC connection named MyPayroll to use the database Payroll.


Testing an ODBC connection with Powershell

This is the source code for the function Test-ODBCConnection which test if the ODBC connection can connect to the database. The function returns $true if a successful connection is made, and $false if it cannot connect. It will print an error message to the console if there is an error somewhere, like the ODBC connection do not exist.

You can download this script from Microsoft Technet Gallery:

Query ODBC connection to get data with Powershell


Query ODBC connection to set data with Powershell


About the Author

Anders Rodland
Anders Rødland started his carrer as an IT consultant in 2006 and now works as a Senior IT-Specialist and Service Owner for Atea, one of the major IT companies in Scandinavia. My main focus is on Microsoft System Center, and I currently have more than 13 Microsoft certifications. Certified on Windows Server, Windows Client, SQL, Exchange and System Center Configuration Manager. Anders Rodland also holds an ITIL Foundation certification. This is my private blog and do not represent my employer. I use this to share information that I find useful. Sharing is caring.
  • Giuseppe Toscano

    Is there a way to use Powerscript against my PC to check out ALL ODBC set up? I have over 100 and testing each one individually is painstakingly long.