0

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?

0

2 Answers 2

1

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).

3
  • 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, 2023 at 8:51
  • You'd have to query it via the ODBC interfaces you're already using. Mar 3, 2023 at 14:51
  • Is there any method to get the same information for a Windows user? Apr 25, 2023 at 9:55
0

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.