如何在不开启SQL Server强制加密时实现VB6与MSSQL的加密连接？

Let's break down your problem and fix this step by step. The core issue here is that different OLE DB/ODBC providers handle encryption parameters differently—especially legacy drivers like SQLOLEDB or the old ODBC {SQL Server} driver, which don't properly support active encryption when the server isn't enforcing it, or fail to handle self-signed certificates correctly.

Key Provider Details to Understand #

• SQLOLEDB (Legacy OLE DB Provider)：This is an outdated driver. Its Encrypt parameter only works if the server has forced encryption enabled, and it doesn't support the TrustServerCertificate parameter at all. That's why your first connection string didn't result in an encrypted connection—it won't initiate encryption unless the server demands it.
• MSOLEDBSQL (Microsoft OLE DB Driver for SQL Server)：This is Microsoft's recommended modern replacement for SQLOLEDB. It fully supports both Encrypt and TrustServerCertificate parameters. When Encrypt=YES is set, it will actively initiate an encrypted connection even if the server doesn't enforce it, and TrustServerCertificate=YES will skip certificate chain validation for your self-signed certificate.
• SQLNCLI11 (SQL Server Native Client 11.0)：While this driver supports encryption parameters, it's in maintenance mode. MSOLEDBSQL is the better long-term choice.
• Old ODBC {SQL Server} Driver：This driver has poor support for modern encryption parameters, which is why you got SSL errors—it can't properly handle the TrustServerCertificate setting for self-signed certificates.

Step-by-Step Solution #

1. Install the MSOLEDBSQL Driver：Make sure your client machine has the Microsoft OLE DB Driver for SQL Server installed (match the version to your system and SQL Server instance).
2. Use the Correct Connection String：Leverage the MSOLEDBSQL provider with the right encryption parameters.
3. Verify Encryption Status：Confirm the connection is encrypted using a SQL Server system view.

Adjusted Code Example #

Private Sub Command1_Click()
    Dim sConnectionString As String
    Dim strSQLStmt As String
    
    '-- Use MSOLEDBSQL with proper encryption settings
    sConnectionString = "Provider=MSOLEDBSQL;Data Source=192.168.27.91\MIB14;Initial Catalog=EHSC_SYM_Kings_Development;User Id=userid;Password=password;Encrypt=YES;TrustServerCertificate=YES"
    strSQLStmt = "select * from dbo.patient where pat_pid = '1001'"
    
    'DB WORK
    Dim db As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim result As String
    
    On Error GoTo ConnectionError 'Add error handling for easier debugging
    db.ConnectionString = sConnectionString
    db.Open 'Open connection
    
    '-- Optional: Verify if the connection is encrypted
    Dim verifyRs As New ADODB.Recordset
    verifyRs.Open "SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID", db
    If Not verifyRs.EOF Then
        MsgBox "Connection Encryption Status: " & verifyRs.Fields(0).Value 'Should show TRUE
    End If
    verifyRs.Close
    Set verifyRs = Nothing
    
    With cmd
        .ActiveConnection = db
        .CommandText = strSQLStmt
        .CommandType = adCmdText
    End With
    
    With rs
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmd
    End If
    
    If Not rs.EOF Then
        rs.MoveFirst
        result = rs.Fields(0).Value
    End If
    
    'Clean up connections
    rs.Close
    db.Close
    Set db = Nothing
    Set cmd = Nothing
    Set rs = Nothing
    Exit Sub
    
ConnectionError:
    MsgBox "Connection Error: " & Err.Description
    If Not db Is Nothing And db.State = adStateOpen Then
        db.Close
        Set db = Nothing
    End If
End Sub

Additional Notes #

• If you must use SQLNCLI11 due to legacy constraints, your original connection string for it should work—just ensure the SQL Server Native Client 11.0 is installed on the client machine.
• Double-check that your self-signed certificate is correctly installed in the server's certificate store (configured via SQL Server Configuration Manager) to avoid unexpected connection issues, even with TrustServerCertificate=YES.

内容的提问来源于stack exchange，提问作者Sai Manibalan