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") }
Pingback: Changing Username and Password with a System DSNBritV8 | BritV8
Hi, how It would look like if I want to change server port number?
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”