sql-server - 使用 Microsoft OLE DB 提供程序和 SQL Native OLE DB 提供程序建立 DB 连接

Question

我使用 oledb 提供程序(SQLOLEDB 和 SQL Native OLEDB 提供程序)创建了一个示例应用程序。

案例 1:提供者 = 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");

结果:如果服务器计算机上安装了证书，则无论是否启用，连接都是安全的连接字符串中的 Encrypt=true 和 TrustServerCertificate=true。

案例 2:提供程序 = SQLNCLI10.1(SQL native 客户端 oledb 提供程序)

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");

结果:如果服务器计算机上安装了证书，则无论是否启用，连接都是安全的连接字符串中的 Encrypt=true 和 TrustServerCertificate=true。即结果与上面相同。

在这两种情况下，我都得到了相同的行为。我在这里遗漏了什么吗？？任何建议将不胜感激？ Original question

---

Answer 1

将连接字符串替换为

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

其余步骤相同。在客户端计算机的“受信任的根证书颁发机构”文件夹中安装相同的证书(存在于服务器上)。

如果服务器和客户端都具有相同的证书，则将建立连接(SSL 连接)，否则失败。