ODBC System-DSN cannot store user password (Azure SQL)

Question

Hello,

we connect our application to a Azure SQL database using ODBC system dsn.
SQL-Authentication (user/Password) is in use.
I am not able store the user password. Everytime the app starts I need to enter the user password.
Help is apprectiated.

Kind regards,
Roland

Answer 1

Microsoft SQL Server ODBC drivers do not store passwords with the ODBC DSN entries for security reasons. You can, however, provide the desired userid and password in the connection string when connecting either with the DSN specification or or DSN-less connection string.

The credentials can be stored (securely) in a configuration file to avoid prompting upon each connection.

Answer 2

Good day Roland,

There is a bug in the forum and I cannot publish my answer directly. Even so it is short answer I still getting that it Exceeded the "Normal request", which obviously make no sense. This is a known bug which was reporterd and I reported it again directly to the team in-charge.

In the meantime, I uploaded the answer in the following link:

http://ariely.info/Blog/tabid/83/EntryId/259/Storing-Password-in-DSN-for-connecting-Azure-SQL-Database.aspx

i am sorry for the inconvenience. Please check the response and inform me if you need anything more

Answer 3

Hello pituach,

thank I have read your answer (link). I tried to provide the user and Password using a file-dsn but it did not work.
My App still asks for user an Password when connecting to my Azure SQL database.

I will try the solution you suggested and let you know.

kind regards,
Roland

Answer 4

Microsoft SQL Server ODBC drivers do not store passwords with the ODBC DSN entries for security reasons. You can, however, provide the desired userid and password in >the connection string when connecting either with the DSN specification or or DSN-less connection string.
The credentials can be stored (securely) in a configuration file to avoid prompting upon each connection.

Hello DanGuzman,

how can the credentials be stored (securely) in a configuration file to avoid prompting upon each connection?

kind regards,
Roland

Answer 5

how can the credentials be stored (securely) in a configuration file to avoid prompting upon each connection?

Below is a PowerShell example to save the password to a file:

# save encrypted password to file
Add-Type -AssemblyName System.Security
$filePath = "C:\secrets\password.txt"
$password = Read-Host "Enter password"
$passwordBytes = [System.Text.Encoding]::Unicode.GetBytes($password)
$protectedPasswordBytes = [System.Security.Cryptography.ProtectedData]::Protect( `
    $passwordBytes, $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser)
$protectedPasswordBase64String = [System.Convert]::ToBase64String($protectedPasswordBytes)
[void](New-Item -Path $filePath -ItemType File -Force)
$protectedPasswordBase64String | Out-File $filePath

And an example to decrypt the password and build an ODBC connection string for a DSN:

# get encrypted password from file and build connection string
Add-Type -AssemblyName System.Security
$filePath = "C:\secrets\password.txt"
$protectedPasswordBase64String = Get-Content -Path "C:\secrets\password.txt"
$password = [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect( `
    [System.Convert]::FromBase64String($protectedPasswordBase64String), $null, `
    [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
$connectionString = "DSN=YourDSN;UID=YourLogin;PWD=$password"

This can be translated into .NET code in the language of your choice.