Add an ODBC SQL Server connection with a specific user with Powershell

Question

Hello,

i wan't to add an ODBC connection automatically with Powershell. I wan't to set the access data for the SQL-Server in the script.
UID and PWD is this possible?

My script:

Add-OdbcDsn -Name "xxx" -DriverName "SQL Server" -DsnType System -Platform 32-bit -SetPropertyValue @("Server=xxx", "Database=xxx", "UID=xxx", "PWD=xxx")

I get an error that i should not set the unencryptet password in the script. What can I do?

I hope you can help me.
Jannik Scheich

Answer 1

My understanding is that it isn't supported for system DSNs. The credentials aren't stored as part of the connection and therefore you have to specify the information at runtime. See here.

But some folks have found luck doing manual registry manipulation after the fact and using WDAC and other ways.

Answer 2

Hi @Scheich, Jannik ,

Add-OdbcDsn would not take credentials, this article provides a possible approach:
Creating an ODBC Connection With PowerShell Using a Specific Account

You can consider using encrypt passwords to solve your error, for example, use a 256-bit AES key file and a password file to store the username/password securely.

Create the ODBC Connection:
create a file and save it in the same location at your password files, make sure the edit the variables to match your environment

$odbcname="YourOdbcName"  
$sqlserver="Server\Instance"  
$sqldb="DbName"  
$OdbcDriver = Get-OdbcDriver -Name *SQL* -Platform 32-bit  
   
Add-OdbcDsn -Name $odbcname -DriverName $OdbcDriver.Name -Platform 32-bit -DsnType System -SetPropertyValue @("Server=$sqlserver", "Trusted_Connection=Yes","Database=$sqldb")  

Creating ODBC as a Specific User:
to allow for this, create a file with the same requirements as the above step

$filepath="\\your\file\path\here"  
$Account = "domain\user"  
$Key = Get-Content "$filepath\AES_KEY_FILE.key"  
$cred = New-Object System.Management.Automation.PSCredential($Account,(Get-Content "$filepath\AES_PASSWORD_FILE.txt" | ConvertTo-SecureString -Key $Key))  
   
start-process powershell -credential $cred -workingdirectory $filepath .\createodbc.ps1  

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.