With Powershell 4 on Windows 8 and 2012, they’ve improve the support for ODBC connections. You just need to import the Wdac module to be able to use the different ODBC functions.
Get-Command *ODBC* shows the following functions:
Add-OdbcDsn
Disable-OdbcPerfCounter
Enable-OdbcPerfCounter
Get-OdbcDriver
Get-OdbcDsn
Get-OdbcPerfCounter
Remove-OdbcDsn
Set-OdbcDriver
Set-OdbcDsn
For instance, if you were to create a System ODBC connection, with a 32 bit SQL driver, use the following code:
Import-Module Wdac
$OdbcDriver = Get-OdbcDriver -Name *SQL* -Platform 32-bit
If(!$OdbcDriver.Count) # Only continue if 1 SQL ODBC driver is installed
{ Add-OdbcDsn -Name "ODBC Connection Name" -DriverName $OdbcDriver.Name -Platform 32-bit -DsnType System -SetPropertyValue @("Server=SQL ServerName\SQL Server Instance", "Trusted_Connection=Yes","Database=SQL DatabaseName") }
Powershell Administrator Blog 
Yo, Mr Powershell Admin, I added a link to your site on my weblog’s usefull links section. Care to return the favor?
LikeLike
Can I use the Add-ODBCdsn command to add a connection to a remote computer? If so, how? I can’t seem to get it to work.
LikeLike
I’ve not ran this against a remote computer myself, but according to documentation provided by MS, you should use the -CimSession switch: -CimSession
Runs the cmdlet in a remote session or on a remote computer. Enter a computer name or a session object, such as the output of a New-CimSession or Get-CimSession cmdlet. The default is the current session on the local computer.
(see: https://technet.microsoft.com/en-us/library/hh771022%28v=wps.630%29.aspx?f=255&MSPPError=-2147217396)
So you should be able to just enter the remote computer name after this command and if the account you’re running the PS script from has the correct access rights on the remote computer, it should create it.
Sometimes working against a remote computer will return a different result than when you run it locally.
So there’s also this option: create a new- PSSession to the remote computer (In this case you can also specify the account you’d want the script to be ran as, which can be very handy if you need to use another user account). Then you can use Invoke-Command to locally run Add-ODBCdsn on the remote computer through a ps remote session. Don’t forget to remove the session in the end by using Remove-PSSession. It’s a little bit more code, but it’ll run on the remote computer’s CPU and I myself believe this is a more stable way of executing code against a remote computer.
Documentation for Invoke-Command can be found here: https://technet.microsoft.com/en-us/library/hh849719(v=wps.630).aspx
And documentation for New-PSSession (and others) can be found here: https://technet.microsoft.com/en-us/library/hh849717(v=wps.630).aspx
LikeLike
Would you know how to add-odbsdsn with a username/password included in the property values? i tried to look it up in regedit, looks like “lastuser” is the user name, but password is not there.. is it PWD?
LikeLike
Can you specify a little bit more? Your question isn’t completely clear.
But, if it’s an SQL ODBC connection you’re talking about: the “Trusted_Connection=Yes” will use your logged in Windows account for authentication, but this is an ODBC SQL speciic setting, I don’t think this will work on other ODBC types. It seems that your ODBC connection uses only a username and no password, otherwise it would be listed in the registry, this is a non-recommended use as integrated security is the way to go (using the windows account and its credentials). Maybe it’s an option to change your SQL security to allow Windows Authentication. MS documentation on Add-OdbcDsn can be found here: https://technet.microsoft.com/en-us/library/hh771022(v=wps.630).aspx
LikeLike