Create 32-Bit ODBC System DSN with Powershell

In my case I have 3 ODBC system DSNs that I need to setup as part of an install script for an application server. I want to automate the build of different environments.

So in the example below I am creating 3 System DSNs for a Test environment. They are called DB1_Test, DB2_Test and DB3_Test

I create an array $ODBCDSNsToCreate so I can go through a foreach loop to create them

To explain the Add-OdbcDsn cmdlet a bit , I am create 32 bit odbc DSNs, so have to use the -32-bit parameter. If you do not specify 32 bit , it will default to 64 bit.

For the  -SetPropertyValue parameter  It is an array to say what server to connect to, the description of the ODBC connection (which I add the environment variable into) and the database I want to connect to on the SQL server.

Why do I not need to select Username and password when creating it?

 

Write-host "Creating System DSNs" -ForegroundColor Green
#ODBC DSNs to create based on environment
$InstallEnvironment = "Test"
SqlServerToConnectTo = "Server1"
$ODBCDSNsToCreate = @("DB1_$InstallEnvironment","DB2_$InstallEnvironment",DB3_$InstallEnvironment")

#Create ODBC System DSNs
foreach ($dsn in $ODBCDSNsToCreate)
    {
    Write-host "Creating $DSN ODBC DSN"
    Add-OdbcDsn -Name $dsn -DsnType "System" -platform 32-bit -DriverName "SQL Server" -SetPropertyValue @("Server=$SqlServerToConnectTo","Description=Application $InstallEnvironment", "Database=$dsn")
}

 

 

3 thoughts on “Create 32-Bit ODBC System DSN with Powershell

    • Hi,

      I have not tried this but you may try to reference your server as “servername\instancename” or using the port number, try either “servername, 1433” or “servername\instancename, 1433” as your server address. e.g.
      SqlServerToConnectTo = “Server1\instancename”
      SqlServerToConnectTo = “Server1,1433”
      SqlServerToConnectTo = “Server1\instancename,1433”

Leave a comment

Your email address will not be published. Required fields are marked *

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