Sample Header Ad - 728x90

Force Strict Encryption from a C++ Application

0 votes
3 answers
225 views
I am using SQL Server 2022 Standard Edition. It has an additional option of setting *Force Strict Encryption*, which I have set on the server side. Using SSMS on a client machine, I am able to connect and access a user table. But I am failing to do the same from a C++ application. Below is the sample code I am trying:
-cpp
auto pDatabase = new CDatabase();
CString connString = L"DRIVER={ODBC Driver 18 for SQL Server};Network=DBMSSOCN;DATABASE=TESTTDE;Encrypt=strict;TrustServerCertificate=no;HostNameInCertificate=10.100.200.300;Mars_Connection=yes;SERVER=10.100.200.300\\TDE,2144;UID=Supervisor;PWD=password;";
auto reply = pDatabase->OpenEx(connString, CDatabase::noOdbcDialog);
CStringArray userNameList;
CString strUserName;
CStringW strUserNameW;
CString SQLString = L"select * from TESTTDE.dbo.UserTable;";
CRecordset userRecords(pDatabase);
userRecords.Open(CRecordset::forwardOnly, SQLString, CRecordset::readOnly);
while (!userRecords.IsEOF())
{
	userRecords.GetFieldValue(L"Name", strUserNameW);
	strUserName = CW2A(strUserNameW.Trim());
	userNameList.Add(strUserName.Trim());
	userRecords.MoveNext();
}
userRecords.Close();
userRecords.Open generates an exception and I am unable to access the database. Can someone shed some light on this? What could I try to make this work? --- I am using a self-signed certificate. It works if I use *Force Encryption* without enabling *Force Strict Encryption* at the server. Exception at client side:
-none
The incoming tabular data stream (TDS) protocol stream is incorrect.
The stream ended unexpectedly.
State:28000,Native:4002,
Origin:[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Server log:
-none
The SQL Server or the endpoint is configured to accept only 
strict (TDS 8.0 and above) connections.
The connection has been closed.
Is there anything that prevents self-signed certificate usage with *strict* encryption? --- Some additional observations: Using low-level SQL APIs from a sample application I was able to fetch data from the server even when Force Strict Encryption was enabled. The code is below:
-cpp
SQLHANDLE env;
SQLHANDLE dbc;
SQLHANDLE stmt;
SQLRETURN ret;

SQLWCHAR* connStr = (SQLWCHAR*)L"Driver={ODBC Driver 18 for SQL Server};Server=10.100.200.300\\TDE;Database=TESTTDE;Uid=Supervisor;Pwd=password;Encrypt=strict;";

// Allocate environment handle
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
if (ret == SQL_ERROR) {
	std::wcerr << L"Error allocating environment handle." << std::endl;
	return;
}

// Set the ODBC version environment attribute
ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
if (ret == SQL_ERROR) {
	checkDiagnostic(env, SQL_HANDLE_ENV);
	SQLFreeHandle(SQL_HANDLE_ENV, env);
	return;
}

// Allocate connection handle
ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
if (ret == SQL_ERROR) {
	checkDiagnostic(env, SQL_HANDLE_ENV);
	SQLFreeHandle(SQL_HANDLE_ENV, env);
	return;
}

// Connect to the data source
ret = SQLDriverConnectW(dbc, NULL, connStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if (ret == SQL_ERROR) {
	checkDiagnostic(dbc, SQL_HANDLE_DBC);
	SQLFreeHandle(SQL_HANDLE_DBC, dbc);
	SQLFreeHandle(SQL_HANDLE_ENV, env);
	return;
}

// Allocate statement handle
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
if (ret == SQL_ERROR) {
	checkDiagnostic(dbc, SQL_HANDLE_DBC);
	SQLDisconnect(dbc);
	SQLFreeHandle(SQL_HANDLE_DBC, dbc);
	SQLFreeHandle(SQL_HANDLE_ENV, env);
	return;
}

// Execute a query
SQLWCHAR* query = (SQLWCHAR*)L"SELECT * FROM UserTable";
ret = SQLExecDirectW(stmt, query, SQL_NTS);
if (ret == SQL_ERROR) {
	checkDiagnostic(stmt, SQL_HANDLE_STMT);
}
else
{
	SQLWCHAR name;
	while (SQLFetch(stmt) == SQL_SUCCESS) {
		ret = SQLGetData(stmt, 1, SQL_C_WCHAR, name, sizeof(name), NULL);
		if (SQL_SUCCEEDED(ret)) {
			AfxMessageBox((LPCTSTR)name);
		}
	}
}

// Clean up
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
I suspect that MFC class API CRecordset::Open() has some issue in supporting TDS 8.0.
Asked by Amal Jesudas (69 rep)
Feb 26, 2025, 11:56 AM
Last activity: Mar 5, 2025, 07:25 AM