ODBC Connections in Powershell

Working with 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 system.data.odbc.odbcconnection 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.

Add-OdbcDsn -Name "MyPayroll" -DriverName "SQL Server Native Client 10.0" -DsnType "System" -SetPropertyValue @("Server=MyServer", "Trusted_Connection=Yes", "Database=Payroll")

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.

Get-OdbcDsn -Name "MyPayroll" -DsnType "System" -Platform "32-bit"

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.

Set-OdbcDsn -Name "MyPayroll" -DsnType "System" -SetPropertyValue "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.

Function Test-ODBCConnection {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$True,
                    HelpMessage="DSN name of ODBC connection")]
                    [string]$DSN
    )
    $conn = new-object system.data.odbc.odbcconnection
    $conn.connectionstring = "(DSN=$DSN)"
    
    try {
        if (($conn.open()) -eq $true) {
            $conn.Close()
            $true
        }
        else {
            $false
        }
    } catch {
        Write-Host $_.Exception.Message
        $false
    }
}

You can download this script from Microsoft Technet Gallery: https://gallery.technet.microsoft.com/Test-ODBCConnection-522fefaf

Query ODBC connection to get data with Powershell

function Get-ODBC-Data{
   param(
   [string]$query=$(throw 'query is required.'),
   [string]$dsn
   )
   $conn = New-Object System.Data.Odbc.OdbcConnection
   $conn.ConnectionString = "DSN=$dsn;"
   $conn.open()
   $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
   $ds = New-Object system.Data.DataSet
   (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
   $conn.close()
   $ds.Tables[0]
}

 

Query ODBC connection to set data with Powershell

function Set-ODBC-Data{
  param(
  [string]$query=$(throw 'query is required.'),
  [string]$dsn
  )
  $conn = New-Object System.Data.Odbc.OdbcConnection
  $conn.ConnectionString= "DSN=$dsn;"
  $cmd = new-object System.Data.Odbc.OdbcCommand($query,$conn)
  $conn.open()
  $cmd.ExecuteNonQuery()
  $conn.close()
}

 

Anders Rødland

Anders Rødland started his IT career in 2006. My main focus is MS Configuration Manager and client management, and I have passed 17 Microsoft certifications since then. My main expertise is on client management with Microsoft Endpoint Manager: Intune and Configuration Manager. I also do a lot of work on the security side with Microsoft Defender for Endpoint. In addition to my Microsoft certification, I also have an ITIL v3 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.

3 thoughts to “Working with ODBC Connections in Powershell”

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

      1. Thanks Anders, I’m am far from a Powershell developer; just looking at it now but I will work on trying to figure it out using the Get-OdbcDSN function.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.