使用Microsoft OLE DB提供程序和SQL Native OLE DB提供程序建立数据库连接

问题描述

我已经通过使用oledb提供程序（SQLOLEDB和SQL Native OLEDB提供程序）创建了一个示例应用程序。

案例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->打开（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->执行（query，& vaNoRecords，adCmdText）; 
 printf（connection has been established\\\
）; 
  

结果：如果证书安装在服务器机器上，则连接是安全的，无论是启用

情况2：Provider = SQLNCLI10.1（SQL本机客户端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->打开（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->执行（query，& vaNoRecords，adCmdText）; 
 printf（connection has been established\\\
）; 
  

结果：如果证书安装在服务器机器上，则连接是安全的，无论是启用
Encrypt = true和TrustServerCertificate = true从连接string.ie结果与上述相同。

在这两种情况下，我得到相同的行为。我在这里缺少一些东西？ ？
任何建议将不胜感激

---

解决方案

将连接字符串替换为

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

其余步骤将相同。在客户机的 truted根证书颁发机构文件夹。

如果服务器和客户端都将具有相同的证书，则将建立连接（SSL连接），否则会失败。 / p>

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 enablingEncrypt=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 enablingEncrypt=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

---

解决方案

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.

这篇关于使用Microsoft OLE DB提供程序和SQL Native OLE DB提供程序建立数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！