Password encryption for ODBC

[former_SQLA_member1694937]

Hello

In our system a SQL Anywhere 17 service is running on a server. An application on another machine connects to the database through a SQL Anywhere client via ODBC. The logged in Windows user must not have access to the database other than through the application which has separate user management with separate login.

During connecting the client via ODBC, the application has to give the database user password to the ODBC API in plain text. I would see this as a security risk.

I already found the ENP connection parameter. But even if the encryption level is 2 or 3, the logged-in Windows user or even all Windows users on this machine can decrypt the password and get access to the database. See here in the forum https://sqlanywhere-forum.sap.com/questions/6636/decrypt-dsn-password-enp-to-pwd and also in the SQL Anywhere Help, "EncodedPassword (ENP) connection parameter" in the Caution box http://dcx.sap.com/index.html#sqla170/en/html/81405e0d6ce21014a102e79b05b69c6c.html*loio81405e0d6ce2...

Question: How can I avoid to give the database password as plain text to a public API like ODBC, without making this password available to a Windows user on that machine?

Thanks for your help.

[VolkerBarth]

As to the first link you mention from 2011, note that this behaviour was changed a while ago (with 16.0.0.2041) and does not apply to v17. See that snippet from the latest 16.0.0.2798 readme file for Engineering Case #773529:

...
    The ODBC Data Source Administrator dialog is changed as follows:
     - The Encrypt password option is no longer a checkbox but is now used to 
    select from different encryption options including none, for use on any computer, 
    for use on this computer only, and for use on this computer and this user 
    only.
     - The dialog can no longer be used to change the level of password encryption 
    for an existing password, unless it was previously unencrypted. If the level 
    of encryption is to be changed, then the password must be reentered.
...

AFAIK, the method to "decrypt" (rather to de-obfuscate) an ENP parameter by modifying the registry entry mentioned in the link is no more available.

[jack_schueler]

Here is one way you can hide the password in your ODBC application. It makes the executable somewhat impervious to the "strings" tool. It assembles the connection string from pieces, some of which are stored in binary and converted to strings by the code. This is a 64-bit solution but the principle applies everywhere. I used dbdsn to generate the encoded password for the user ID. Note, however, that this code is still susceptible to hacking using a debugger.

char    *UserID="Browser";
void    *EncodedPassword = {(void*)0x0028e0df0824c1d0};
char *Fmt1 = "%16.16p";
...
    strcpy( dsn, "Driver=SQL Anywhere 17" );
    strcat( dsn, ";UID=" );
    strcat( dsn, UserID );
    strcat( dsn, ";ENP=" );
    sprintf( part, Fmt1, EncodedPassword );
    strcat( dsn, &part[2] );
    ...
    retcode = SQLDriverConnect( dbc, (SQLHWND)NULL,
                dsn, SQL_NTS,
            scso, sizeof(scso)-1,
            &cbso, SQL_DRIVER_NOPROMPT );
    memset( dsn, 0, sizeof(dsn) ); 
    memset( part, 0, sizeof(part) );

[VolkerBarth]

Wow! A solution instead of the expected "Don't store passwords anywhere" claim...

Instead of memset, I'd prefer SecureZeroMemory() to securely erase the memory afterwards, at least when using Windows. It should help to prevent memory dumps from containing the computed ENP value...

[Breck_Carter]

[former_SQLA_member1694931]

I don't understand your question...

Since being introduced to SQL programming, we've set up our SQL client applications using a 'functional' id, and storing the encrypted password either in a text file in the same folder as the executable, windows registry, or even burned into the application code itself.

Then, when connecting to the database, the client application internally decrypts the password, using the encrypted password option in the ODBC connection, which then sends an encrypted password to the SA server using an algorithm unknown to anyone other than the engineers at Sybase...

[jack_schueler]

So this is one way that I would implement your idea, which I believe is similar to the suggested solution. In this scenario, a common user ID and password are used. I do the following steps.

1. create a test data source so that I can get the encrypted form of the original password "SuperTest$123".

dbdsn -w test17a -pet a -c "Host=192.168.1.100:2638;Server=mysqlaserver;UID=superuser;PWD=SuperTest$123"

1. read back the encrypted form (ENP)

dbdsn -g test17a -cm

dbdsn -y -wu "test17a" -c "UID=superuser;ServerName=mysqlaserver;ENP=002100c38337157a1cae07e853b929c6124f3ea266;Host=192.168.1.100:2638"

1. Bury an encrypted form of this encrypted password string 002100c38337157a1cae07e853b929c6124f3ea266 in my application.

2. At connect time, decrypt the encrypted string back to 002100c38337157a1cae07e853b929c6124f3ea266 and then use this with ENP to connect to my database server. After I connect, I wipe the string from memory.

This will mean that this string "002100c38337157a1cae07e853b929c6124f3ea266" is not stored in plain text somewhere in the application (i.e., a strings tool would not reveal it).