|
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*loio81405e0d6ce21014a102e79b05b69c6c 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. |
|
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) );
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...
(28 Feb '20, 11:28)
Volker Barth
Replies hidden
(28 Feb '20, 13:08)
Breck Carter
|
|
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... 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.
dbdsn -y -wu "test17a" -c "UID=superuser;ServerName=mysqlaserver;ENP=002100c38337157a1cae07e853b929c6124f3ea266;Host=192.168.1.100:2638"
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).
(27 Nov '20, 13:01)
JBSchueler
|
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.