0

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:

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:

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:

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:

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[256];
    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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Amal Jesudas
  • 69
  • 1
  • 7

3 Answers3

5

You appear to be a bit confused about how TLS and certificates are supposed to work with SQL Server.

  • In your client connection string:
    • Encrypt=no means don't enforce encryption, but do accept it if forced by the server.
    • Encrypt=yes or mandatory means enforce encryption even if the server doesn't enforce it.
    • TrustServerCertificate=yes means ignore the SAN name in the certificate. This is obviously insecure, and not much better than not encrypting at all.
      Without this option, the name you connect with must match the SAN in the certificate, which in your case is an IP address for some reason.
    • Encrypt=strict means enforce the SAN name matches (or supply HostNameInCertificate for a different one). It also enforces TDS 8.0 and only works on SQL2022+.
      TrustServerCertificate=yes is ignored with this option. You can provide ServerCertificate=pathOfCertificate to provide an actual certificate to match, but this can be quite unwieldy.
  • In your server config:
    • Force encryption means all connections must be encrypted. Whether the client trusts this server's certificate depends on their own connection string as above.
    • Force Strict Encryption means all connections must use strict encryption as above, and therefore those clients cannot use TrustServerCertificate=yes.

So in your case, you have a self-signed certificate. This means you must use TrustServerCertificate=yes and you can either use Encrypt=yes or no, but not strict. The server side can use Force encryption but not Force Strict Encryption.

Or you can export the self-signed certificate to the client (you can only do this with your own self-signed certificate, not with the automatic certificate). Then you still use strict encryption, and use ServerCertificate=pathOfCertificate to provide its path on the client. This can get very impractical with many clients to update.

You would be much better off with a properly signed certificate, by either a public CA or a private trusted CA. Then you can enforce strict encryption. Connect using a DNS name not an IP address, as no CA worth its salt will give you an IP-based certificate.

For more info, see the docs here, here and here.

Also, you are connecting using an instance name and a port number. This is non-standard: the instance name will be ignored and only the port number will be used. Use one or the other only.

Charlieface
  • 17,078
  • 22
  • 44
1

I believe the issue is your connection string, where it says Encrypt=strict. The only valid values for the Encrypt attribute are no, yes, false, and true, as far as I can tell:

  1. ConnectionStrings.com uses yes for every connection string example that uses encryption. Here's the direct link to the basic encryption example. (Not to be confused with Always Encrypted, a different feature of SQL Server.)
  2. The Microsoft docs on Connection Strings mention using true or yes in the Enable Encryption section.
  3. Also, the docs on Special cases for encrypting connections to SQL Server use Encrypt=yes in every example.

My guess would be you try Encrypt=yes and that the server determines what type of encryption is being applied (e.g. strict) based on how you configured the server.

Note the above is only true on ODBC drivers from version 17 and older. strict is valid on version 18+.

J.D.
  • 40,776
  • 12
  • 62
  • 141
1

Finally I found a possible solution to the issue. As mentioned in the query, CRecordset::Open() was causing the issue. I just went through the parameters supported and saw that the third parameter has a possible value executeDirect. The comment for the same mentions "Directly execute SQL rather than prepared execute". I tried using the same and was able to fetch data using CRecordset. userRecords.Open(CRecordset::forwardOnly, SQLString, CRecordset::executeDirect);. Will close this query marking this as answer if I am able to get some Microsoft documentation to confirm.

Amal Jesudas
  • 69
  • 1
  • 7