question

RolandSchmid-5350 avatar image
RolandSchmid-5350 asked ·

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

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

8845-cannot-store-password.png


azure-sql-database
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
DanGuzman answered ·

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.

Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
pituach answered ·

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


Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RolandSchmid-5350 avatar image
RolandSchmid-5350 answered ·

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

1 comment Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hi,

My App still asks for user an Password when connecting to my Azure SQL database.

Have you read this in my post?

"Even if you modified your registry information to store the password ODBC would not read that information"

I told in the post that it is not supported and the tool will continue ask for the password, but I also said that you can read the registry in your application (not the odbcad32.exe tool) and get the password from there.

0 Votes 0 · ·
RolandSchmid-5350 avatar image
RolandSchmid-5350 answered ·

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

Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
DanGuzman answered ·

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.






Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.