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
Last activity: Mar 5, 2025, 07:25 AM