Can you change an ODBC DSN driver in Powershell?

Question

We are in the process of locking down our networks to the TLS 1.2 protocol. This requires an updated Sql Server driver. We've installed the updated driver, however, now I have to update DSNs that are used by the analysis team.

I have seen where I can use the Wdac module and its functions to get, add, remove, etc. DSNs and their properties. What I have not found and not been able to do through testing is to simply change the driver for existing DSNs.

Is that possible?

TIA Richard

Answer 1

As noted in this answer, ODBC DSN entries are stored in the Windows Registry. Updating DSNs to use a new driver involves:

1. Updating the individual DSNs so their Driver= value points to the location of the new driver's DLL, and
2. Updating the entries under the "ODBC Data Sources" key to list the new driver name.

I'm certainly no PowerShell expert but the following seems to work in converting 64-bit System DSNs to use "ODBC Driver 17 for SQL Server" instead of the ancient "SQL Server" driver:

$old_driver_name = "SQL Server"
$old_driver_path = "C:\WINDOWS\system32\SQLSRV32.dll"
$new_driver_name = "ODBC Driver 17 for SQL Server"
$new_driver_path = "C:\WINDOWS\system32\msodbcsql17.dll"

foreach ($key in Get-ChildItem -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI)
{
    if ($key.Name -eq "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources")
    {
        Write-Output "[ODBC Data Sources]"
        Push-Location
        $key | Set-Location
        Get-Item . | Select-Object -ExpandProperty property | ForEach-Object -Process {
            $dsn = New-Object psobject -Property @{
                “property”=$_;
                “value” = (Get-ItemProperty -Path . -Name $_).$_
            }
            $driver_name = $dsn.value
            if ($driver_name -eq $old_driver_name)
            {
                Write-Output "    $_"
                Set-ItemProperty -Path . -Name $_ -Value $new_driver_name
            }
        }
        Pop-Location
    }
    else
    {
        $driver = $key | Get-ItemProperty -Name Driver
        if ($driver.Driver -eq $old_driver_path)
        {
            Write-Output "DSN: $key"
            $key | Set-ItemProperty -Name Driver -Value $new_driver_path
        }
    }
}

Notes:

• The above script must be run as Administrator to update System DSNs.
• 32-bit System DSNs are in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI
• User DSNs are in HKEY_CURRENT_USER\Software\ODBC\ODBC.INI