如何在SQL Server中使用ADO（非ADO.NET）实现连接加密？

Great question—yes, you absolutely can enforce encrypted connections to SQL Server using classic ADO. The key is leveraging connection string parameters and configuring the underlying OLE DB provider correctly. Here's your step-by-step implementation guide:

Is This Feasible? #

Absolutely. Classic ADO relies on OLE DB providers to communicate with SQL Server, and both modern providers (like MSOLEDBSQL) and older legacy ones (like SQLOLEDB) support connection encryption via specific parameters.

Step-by-Step Implementation #

1. Pick the Right OLE DB Provider #

Stick to modern providers for better security and compatibility:

• MSOLEDBSQL: The current recommended OLE DB provider for SQL Server, supports latest encryption standards like TLS 1.2+
• SQLOLEDB: Legacy provider, still functional but lacks some modern security capabilities

2. Configure the Connection String #

Use encryption-specific parameters tailored to your chosen provider:

For MSOLEDBSQL Provider

Use Encrypt to enable encryption, and TrustServerCertificate to handle self-signed certificates (avoid this in production):

Dim adoConn
Set adoConn = CreateObject("ADODB.Connection")

' Production-ready (uses CA-signed SQL Server certificate)
adoConn.ConnectionString = "Provider=MSOLEDBSQL;Server=YourSQLInstance;Database=YourDatabase;Uid=YourUsername;Pwd=YourPassword;Encrypt=yes;TrustServerCertificate=no"

' Testing scenario (self-signed certificate)
' adoConn.ConnectionString = "Provider=MSOLEDBSQL;Server=YourSQLInstance;Database=YourDatabase;Uid=YourUsername;Pwd=YourPassword;Encrypt=yes;TrustServerCertificate=yes"

adoConn.Open

For SQLOLEDB Provider

Legacy provider uses the Use Encryption for Data parameter instead:

Dim adoConn
Set adoConn = CreateObject("ADODB.Connection")

adoConn.ConnectionString = "Provider=SQLOLEDB;Server=YourSQLInstance;Database=YourDatabase;Uid=YourUsername;Pwd=YourPassword;Use Encryption for Data=true;Trust Server Certificate=no"
adoConn.Open

3. Verify Encryption is Active #

To confirm your connection is encrypted, run this query on the SQL Server instance while your ADO connection is open:

SELECT 
    session_id, 
    encrypt_option, 
    auth_scheme 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

Look for encrypt_option = TRUE in the results—this confirms encryption is working as expected.

Key Notes #

• SQL Server Certificate Setup: Your SQL Server must have a valid SSL/TLS certificate installed (either CA-signed or self-signed). Without this, encryption will fail unless you set TrustServerCertificate=yes (not recommended for production due to man-in-the-middle risks).
• Windows Authentication: If using Windows auth, replace Uid/Pwd with Integrated Security=SSPI in your connection string.
• Provider Preference: Always prioritize MSOLEDBSQL over SQLOLEDB for better security and support for modern protocols.

内容的提问来源于stack exchange，提问作者Dawid Stec