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

Roland Schmid 1 Reputation point
2020-05-29T07:40:02.043+00:00

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
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Dan Guzman 9,201 Reputation points
    2020-05-29T11:21:52.177+00:00

    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.

    0 comments No comments

  2. Ronen Ariely 15,096 Reputation points
    2020-05-29T19:42:27.603+00:00

    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

    0 comments No comments

  3. Roland Schmid 1 Reputation point
    2020-05-29T21:05:40.88+00:00

    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


  4. Roland Schmid 1 Reputation point
    2020-05-29T21:24:32.507+00:00

    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

    0 comments No comments

  5. Dan Guzman 9,201 Reputation points
    2020-05-29T22:44:44.117+00:00

    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.

    0 comments No comments