Powershell: Create/Add Windows System DSN via .CSV Import

powershell-create-system-dsn-via-csv-import.md

Powershell: Create/Add Windows System DSN via .CSV Import

You can create/add/update/get a Windows System DSN pretty easily with Powershell.

Example dsn.csv file

Database, SystemDSN, Server
AAA, AAA_DSN, SomeIP\User
BBB, BBB_DSN, AnotherIP\AnotherUser

Powershell Script

Import-Csv .\dsn.csv | foreach{Add-OdbcDsn -Name $_.SystemDSN -DriverName "SQL Server" -DsnType "System" -Platform "32-bit"  -SetPropertyValue @("Server=$_.Server",  "Database=$($_.Database)") -PassThru}

You could modify as described at https://docs.microsoft.com/en-us/powershell/module/wdac/add-odbcdsn?view=windowsserver2019-ps.

Remove DSN Example

Import-Csv .\dsn.csv | foreach{Remove-OdbcDsn -Name $_.SystemDSN -DsnType "System" -Platform "32-bit" -PassThru}

Note

For security reasons, Microsoft does not allow you to set the UID or PWD in this way. If you attempt to do so, an error will inform you that the keys should not be stored in the registry for security reasons and to provide the credential information at runtime via SQLDriverConnect, SQLConnect or SQLBrowseConnect.

It's not working...
@{Database=AAA; SystemDSN=AAA_DSN; Server=SomeIP\User}.server - it puts that string in server in odbs