Establish a database connection using the Microsoft OLE DB Provider and the SQL Native OLE DB Provider

Question

I created an example application using both the oledb provider (SQLOLEDB and the NED OLEDB SQL provider).

Case 1: Provider = SQLOLEDB

hr = ::CoInitializeEx(NULL, COINIT_MULTITHREADED); hr = cADOConnection.CreateInstance(__uuidof(Connection)); CString con_string = "provider=SQLOLEDB;server=MYPC;Database=MyDB"; CString SSlcon_string = "provider=SQLOLEDB;Encrypt=true;TrustServerCertificate=true;server=MYPC;Database=MyDB"; CString userName = "sa"; CString Password = "sa"; BSTR bsConnection = /*con_string*/SSlcon_string.AllocSysString(); BSTR uName = userName.AllocSysString(); BSTR uPassword = Password.AllocSysString(); hr = cADOConnection->Open(bsConnection, uName, uPassword, adConnectUnspecified); printf("connection has been established"); VARIANT vaNoRecords; memset(&vaNoRecords, 0, sizeof vaNoRecords); CString sql = "SELECT * FROM salary"; BSTR query = sql.AllocSysString(); _RecordsetPtr rs; rs = cADOConnection->Execute(query, &vaNoRecords, adCmdText); printf("connection has been established\n"); 

Result: if the certificate is installed on the server, then the connection is protected regardless of the inclusion of Encrypt = true and TrustServerCertificate = true from the connection string.

Case 2: Provider = SQLNCLI10.1 (Client Provider for Local SQL Client)

 HRESULT hr; hr = ::CoInitializeEx(NULL, COINIT_MULTITHREADED); hr = cADOConnection.CreateInstance(__uuidof(Connection)); CString con_string = "provider=SQLNCLI10.1;server=MYPC;Database=MyDB"; CString SSlcon_string = "provider=SQLOLEDB;Encrypt=true;TrustServerCertificate=true;server=MYPC;Database=MyDB"; CString userName = "sa"; CString Password = "sa"; BSTR bsConnection = con_string/*SSlcon_string*/.AllocSysString(); BSTR uName = userName.AllocSysString(); BSTR uPassword = Password.AllocSysString(); hr = cADOConnection->Open(bsConnection, uName, uPassword, adConnectUnspecified); printf("connection has been established"); VARIANT vaNoRecords; memset(&vaNoRecords, 0, sizeof vaNoRecords); CString sql = "SELECT suppliernumber, name1 FROM zrs_supplier"; BSTR query = sql.AllocSysString(); _RecordsetPtr rs; rs = cADOConnection->Execute(query, &vaNoRecords, adCmdText); printf("connection has been established\n"); 

Result: if the certificate is installed on the server, the connection is protected regardless of the inclusion of Encrypt = true and TrustServerCertificate = true from in the string.ie connection. The result is the same as above.

In both cases, I get the same behavior. Am I missing something here? Any suggestion would be appreciated ?? Original question

---

Answer 1

Replace the connection string with

 CString SSlcon_string = "provider=SQLOLEDB;Use Data For Encryption=True;server=MYPC;Database=MyDB"; 

The remaining steps will be the same. Install the same certificate (present on the server) on the client computer "folder truted root certificate ".

If the server and client both have the same certificate, then the connection will be established (SSL connection), otherwise it will fail.