question

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.

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

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

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

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.