Our client application is developed in C++ and uses ODBC Driver (Version 17) to connect to remote SQL database. We have enabled Always Encrypt option to encrypt the data. Using proper flags in connection string we are able to connect and perform SQL operations. Since Always Encrypt requires certificate for encryption/decryption, we have added the same in the certificate store of windows. Query is, is there a way to identify if the Always Encrypt certificate is missing in the client store, when using CDatabase::OpenEx or when executing query?
2 Answers
Query is, is there a way to identify if the Always Encrypt certificate is missing in the client store, when using CDatabase::OpenEx or when executing query?
In order to get information about the metadata for Always Encrypted, you'll need a connection and access to the database which is using it. This means you won't know before hand what you'll need, unless that metadata is somehow gathered and used in the app when it's implemented. I wouldn't go that route, but I guess you could.
You can certainly get the information from the catalog views of the database(s) in question via sys.column_master_keys , sys.column_encryption_keys , and sys.columns. Once the metadata about the keys and their storage are obtained, you can then determine the best method to check if those exist before doing anything further (for example, configuration checks for diagnostics or basic pre-checks for app endpoints).
answered Mar 2 at 14:52
-
With "select * from sys.column_master_keys" I should be able to get "key_path". But I am sort of stuck on how to get this value in a C++ application. Mar 3 at 8:51
-
-
Below works for me:
auto pDatabase = new CDatabase();
auto reply = pDatabase->OpenEx(connString, CDatabase::noOdbcDialog);
char buffer[500];
sprintf_s(buffer, "select key_path from sys.column_master_keys");
CString query = CString(buffer);
CRecordset recordset(pDatabase);
recordset.Open(CRecordset::forwardOnly, query);
CString keyPath;
while (!recordset.IsEOF())
{
recordset.GetFieldValue(_T("key_path"), keyPath);
keyPath.Replace(L"CurrentUser/my/", L"");
AfxMessageBox(keyPath);
recordset.MoveNext();
}
recordset.Close();