Always Encrypted SQL Server : How to insert / updated using C++ code?

Question

I have set the Always Encrypted option to my table columns. Now I need to insert or update my table from my C++ Client application. I know that we need to declare params and use them for Always Encrypted. From SSMS I am able to do so without any issues. However, when I try using C++ code, it fails.

Say, for example, my table contains two columns that have data type int. I connect to my database using the below connection string:

"Driver={ODBC Driver 17 for SQL Server};Server=192.122.200.200,1433;Encrypt=no;Trusted_Connection=no;ColumnEncryption=Enabled;DATABASE=AlwaysEncrypted;UID=sa;PWD=;";

I create the below SQL query string:

CString csQStrInsert = L"declare @val1 int = 3; declare @val2 int = 3; insert into [dbo].[Table_AlwaysEncrypted] ([col1], [col2]) values (@val1, @val2);";

And call pDatabase->ExecuteSQL(csQStrInsert);

However, this throws exception with the following info:

Encryption scheme mismatch for columns/variables '@val1'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '1' expects it to be DETERMINISTIC, or PLAIN TEXT.

Can someone help me out? There is mostly no help when it comes to C++ code for Always Encrypted.

[Trialed code that still fails in SQL Execute:]

SQLHENV henv = SQL_NULL_HENV;
    SQLHDBC hdbc1 = SQL_NULL_HDBC;
    SQLHSTMT hstmt = SQL_NULL_HSTMT;
    SQLRETURN rc = 0;
RETCODE retcode;
retcode = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &amp;henv);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
{
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER); // Notify ODBC that this is an ODBC 3.0 app.
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
    {
        retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &amp;hdbc1); // Allocate ODBC connection handle and connect. 
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
        {
            SQLCHAR retconstring[1024];
            SQLWCHAR connstr[] = L&quot;Driver={ODBC Driver 17 for SQL Server};Server=192.168.555.128,1433;Encrypt=no;Trusted_Connection=no;ColumnEncryption=Enabled;DATABASE=AEncrypted;UID=Supervisor;PWD=;&quot;;
            retcode = SQLDriverConnect(hdbc1, NULL, connstr, SQL_NTS, (SQLWCHAR*)retconstring, 1024, NULL, SQL_DRIVER_NOPROMPT);
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
            {               
                SQLWCHAR sqlstate[1024];
                SQLWCHAR message[1024];
                if (SQL_SUCCESS == SQLGetDiagRec(SQL_HANDLE_DBC, hdbc1, 1, sqlstate, NULL, message, 1024, NULL))
                {
                    CString csMsg = message;
                }
                retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &amp;hstmt); // Allocate statement handle. 
                if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
                {
                    //
                }
            }
        }
    }
}


std::string queryText = &quot;INSERT INTO [dbo].[Table_AEncrypted] ([col1], [col2]) VALUES (?, ?);&quot;;
rc = SQLPrepare(hstmt, (SQLWCHAR *)queryText.c_str(), SQL_NTS);

SQLINTEGER val1 = 123;
SQLINTEGER val2 = 456;
SQLINTEGER cbNull = SQL_NULL_DATA;

rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, SQL_DESC_PRECISION, 0, (SQLPOINTER)val1, 0, &amp;cbNull);
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, SQL_DESC_PRECISION, 0, (SQLPOINTER)val2, 0, &amp;cbNull);

rc = SQLExecute(hstmt);

// Clean up.  
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);

score 2 · Answer 1 · answered Oct 27 '22 at 01:11

Charlieface is correct, SSMS (with the options enabled) will parse and parameterize for you.

If you want to use C++ and ODBC (taken from your connection string) then you'll need to have a parameterized query and use the appropriate preparation. In this case it would be to use SQLBindParameter. There is an example at the bottom of the page which should help, additionally there is a developer resource on the SQL Docs page which can help and covers your exact scenario.

Much as Charlieface has already pointed out, Always Encrypted requires the client-side driver to do all the work. It would make sense to have a quick read up on it.

score 0 · Answer 2 · edited Nov 07 '22 at 15:11

There was an issue in my SQLDriverConnect. It is now working.

SQLHANDLE sqlenvhandle = 0;
SQLHANDLE sqlconnectionhandle = 0;
SQLHANDLE sqlstatementhandle = 0;
SQLHANDLE sqlstatementhandle2 = 0;
SQLRETURN retcode = 0;
SQLWCHAR retconstring[1024];
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlenvhandle);
retcode = SQLSetEnvAttr(sqlenvhandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle, &sqlconnectionhandle);
SQLWCHAR connstr[] = L"Driver={ODBC Driver 17 for SQL Server};Server=192.168.555.128,1433;Encrypt=no;Trusted_Connection=no;ColumnEncryption=Enabled;DATABASE=AEncrypted;UID=Supervisor;PWD=;";
retcode = SQLDriverConnect(sqlconnectionhandle, NULL, connstr, SQL_NTS, (SQLWCHAR*)retconstring, 1024, NULL, SQL_DRIVER_NOPROMPT);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle, &sqlstatementhandle2);
SQLWCHAR *query = _T("INSERT INTO Table_AEncrypted(col1, col2) VALUES (?, ?)");
retcode = SQLPrepare(sqlstatementhandle2, query, SQL_NTS);
SQLINTEGER val1 = 222;
SQLINTEGER val2 = 444;
retcode = SQLBindParameter(sqlstatementhandle2, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &val1, 0, NULL);
retcode = SQLBindParameter(sqlstatementhandle2, 2, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &val2, 0, NULL);
retcode = SQLExecute(sqlstatementhandle2);
SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle2);
SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle);
SQLDisconnect(sqlconnectionhandle);
SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);

Always Encrypted SQL Server : How to insert / updated using C++ code?

2 Answers2