Lotus Notes通过OLEDB/ODBC连接SQL Server 2016遇SSL错误求助

Hey there! As someone who's wrestled with Lotus Notes-SQL Server connection headaches before, let's break down your problem step by step.

1. Fixing the SSL Security Error with SQL Server 2016 #

You've already updated your OLEDB/ODBC drivers and enabled TLS 1.2—great first moves. Here are more checks to resolve the SSL issue:

• Verify TLS 1.2 is enabled on SQL Server: Open SQL Server Configuration Manager, go to SQL Server Network Configuration > Protocols for [Your Instance]. Right-click TCP/IP, navigate to the Flags tab, and set TLS 1.2 to "Enabled". Restart the SQL Server service after making changes!
• Trust the SQL Server certificate: If your server uses a self-signed certificate, import it into the Trusted Root Certification Authorities store on the machine running Lotus Notes. Without this, the driver will reject the untrusted certificate.
• Add explicit SSL parameters to your connection string: To temporarily rule out certificate validation as a blocker, add these parameters:
  • For ODBC: Encrypt=yes;TrustServerCertificate=yes
  • For OLEDB: Use Encryption for Data=True;Trust Server Certificate=True
    This skips strict checks to test if the core connection works (you can tighten security later once stable).
• Reinstall latest drivers: Even if you upgraded to ODBC 17/OLEDB 18, a corrupted install could cause issues. Grab the latest Microsoft ODBC Driver for SQL Server and do a clean reinstall.

2. Correct Provider Value for ODBC Connections #

Your original code uses SQLOLEDB—that's the old OLEDB provider, not for ODBC. For ODBC connections in Lotus Notes, you have two solid options:

• ODBC-OLEDB bridge provider: Use Provider=MSDASQL—this is the standard provider that lets you leverage ODBC drivers via OLEDB in Lotus Notes.
• Modern OLEDB provider (recommended): If you prefer OLEDB but need SQL Server 2016/TLS 1.2 compatibility, switch to Provider=MSOLEDBSQL. This is Microsoft's updated OLEDB driver that replaces outdated SQLOLEDB and supports newer SQL Server features.

3. Fixed Code Examples #

ODBC Connection (MSDASQL Provider) #

Choose between a pre-configured DSN or DSN-less string:

Dim con As New ODBCConnection
With con
    ' Option 1: Use a pre-set ODBC DSN
    .Provider = "MSDASQL"
    .ConnectTo("DSN=YourSQL2016DSN;UID=yourUsername;PWD=yourPassword;Encrypt=yes;TrustServerCertificate=yes")

    ' Option 2: DSN-less connection (uses ODBC Driver 17 directly)
    ' .ConnectTo("Driver={ODBC Driver 17 for SQL Server};Server=SQLServerName;Database=DatabaseName;UID=yourUsername;PWD=yourPassword;Encrypt=yes;TrustServerCertificate=yes")
End With

Modern OLEDB Connection (MSOLEDBSQL Provider) #

If you stick with OLEDB, this is the compatible, updated approach:

Dim con As New OLEDBConnection
With con
    .Provider = "MSOLEDBSQL"
    .ConnectTo("Server=SQLServerName;Database=DatabaseName;UID=yourUsername;PWD=yourPassword;Use Encryption for Data=True;Trust Server Certificate=True")
End With

Quick note: Your original code included .Metadata = "tbl_CURRENCY"—this isn't a valid property for Lotus Notes connection objects, so remove it. Once connected, use an ODBCQuery or OLEDBQuery object to access the table.

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