Establish DB connection by using Microsoft OLE DB

问题:

I have created a sample app by using both the oledb provider(SQLOLEDB and SQL Native OLEDB 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 certificate is installed on server machine then the connection is secure regardless of enabling Encrypt=true and TrustServerCertificate=true from in connection string.

Case 2 : Provider = SQLNCLI10.1(SQL native client oledb provider)

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 certificate is installed on server machine then the connection is secure regardless of enabling Encrypt=true and TrustServerCertificate=true from in connection string.i.e Result is same as above.

In both the case i am getting same behavior.Am i missing something here?? Any Suggestion would be appreciated ?? Original question

---

回答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 same.Install the same certificate(present on server) on client machine's "truted root certificate authorities" folder.

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