Why TLS 1.2 Connections Fail with SQLNCLI10, and Migrating to MSOLEDBSQL 19

Applicability of This Document

Ready-to-Run Commands

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象インスタンスへの接続権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    @@VERSION                             AS version_string,
    SERVERPROPERTY('ProductVersion')      AS product_version,
    SERVERPROPERTY('ProductLevel')        AS product_level,
    SERVERPROPERTY('ProductUpdateLevel')  AS product_update_level,  -- 版によっては NULL
    SERVERPROPERTY('Edition')             AS edition,
    SERVERPROPERTY('MachineName')         AS machine_name;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    c.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,   -- 使用中のクライアントライブラリ
    s.client_version,
    c.net_transport,
    c.protocol_type,
    c.protocol_version,
    c.encrypt_option,          -- TRUE なら接続が暗号化されている
    c.auth_scheme,
    c.client_net_address,
    c.connect_time
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s
        ON c.session_id = s.session_id
WHERE s.is_user_process = 1
ORDER BY c.connect_time DESC;

-- 対象: Windows クライアント（OLE DB 接続）
-- 権限: アプリケーション構成の変更権限
-- 変更作業: あり（暗号化と証明書検証の挙動が変わる）
-- Production 実行: 検証環境での確認と変更管理の手順が前提

-- 1) 移行前（SQL Server Native Client 10.0 / 非推奨）
Provider=SQLNCLI10;Data Source=192.0.2.10,1433;Initial Catalog=SampleDB;User ID=sample_user;Password=********;

-- 2) 移行後（Microsoft OLE DB Driver 19 / 既定で Encrypt=yes）
Provider=MSOLEDBSQL19;Data Source=192.0.2.10,1433;Initial Catalog=SampleDB;User ID=sample_user;Password=********;Encrypt=yes;TrustServerCertificate=no;

-- 3) サーバー証明書を信頼できない場合の暫定回避（検証を弱めるため恒久運用には使わない）
Provider=MSOLEDBSQL19;Data Source=192.0.2.10,1433;Initial Catalog=SampleDB;User ID=sample_user;Password=********;Encrypt=yes;TrustServerCertificate=yes;

-- 4) 18 系以前の MSOLEDBSQL（既定は Encrypt=no のため従来の接続文字列がそのまま動く）
Provider=MSOLEDBSQL;Data Source=192.0.2.10,1433;Initial Catalog=SampleDB;User ID=sample_user;Password=********;

# 対象: Windows クライアント
# 権限: レジストリの参照権限（環境により管理者権限）
# 変更作業: なし（参照のみ）
# Production 実行: 可能

# インストール済みの SQL Server クライアントドライバーを一覧する
Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\*' |
    Where-Object {
        $_.DisplayName -like '*SQL Server*Native Client*' -or
        $_.DisplayName -like '*OLE DB Driver*for SQL Server*'
    } |
    Select-Object DisplayName, DisplayVersion |
    Sort-Object DisplayName

# OS の SCHANNEL プロトコル設定を参照する（変更はしない）
Get-ChildItem 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols' -Recurse |
    ForEach-Object {
        [PSCustomObject]@{
            Path     = $_.Name
            Settings = (Get-ItemProperty -Path $_.PSPath | Select-Object Enabled, DisabledByDefault)
        }
    }

-- 対象: Windows クライアント（OLE DB 接続）
-- 権限: テスト用の接続情報
-- 変更作業: なし（接続テストのみ）
-- Production 実行: 可能（テスト接続のみ）

-- .udl ファイルや接続テストツールで、以下の3パターンを順に試して切り分ける
-- A) 暗号化なし        : Encrypt=no
-- B) 暗号化あり・検証あり: Encrypt=yes;TrustServerCertificate=no
-- C) 暗号化あり・検証なし: Encrypt=yes;TrustServerCertificate=yes

-- A が成功し B が失敗 → 証明書の問題（サーバー証明書またはクライアントの信頼ストア）
-- A も B も失敗       → TLS バージョンまたはドライバー側の問題
-- B が失敗し C が成功 → サーバー証明書が信頼されていない（恒久対策は証明書の是正）

How to Read the Results

When to Use This

• A specific application stopped being able to connect right after TLS 1.0 / 1.1 was disabled on the server
• A communication-break error appears, such as "An existing connection was forcibly closed by the remote host"
• After swapping the driver to MSOLEDBSQL 19, a connection string that had been working started throwing a certificate error
• A client from the SQL Server 2008 generation cannot connect to a newly built instance on AWS
• A new client can connect to the same server, but an old client cannot

Possible Causes (Most Likely First)

1. 01

   SQLNCLI10 does not support TLS 1.2

   SQL Server Native Client 10.0 predates the widespread adoption of TLS 1.2. A TLS 1.2 connection requires support on both the OS and product side, and in some environments, even applying the relevant update does not fully meet the requirement. Verify whether an update applies using the target environment and the vendor's support information.

2. 02

   TLS 1.0 / 1.1 was disabled on the server or OS

   Disabling older protocols to meet security requirements leaves any client that can only speak those protocols unable to connect. If the timing of disabling the protocol lines up with when connections started failing, this is likely the cause.

3. 03

   MSOLEDBSQL 19's default changed to Encrypt=yes

   Driver version 19.x changed the connection-string default to encryption-on. In environments where the server certificate is not trusted, swapping the driver alone can cause failures even without changing the connection string.

4. 04

   The server certificate is not trusted

   This covers a self-signed certificate, a mismatch between the hostname and the certificate subject, or a missing certificate chain. If it fails with `Encrypt=yes;TrustServerCertificate=no` but succeeds with `TrustServerCertificate=yes`, this is the cause.

5. 05

   The SQL Server side is missing the TLS 1.2 support update

   Older versions of SQL Server need the relevant update applied to handle TLS 1.2. The applicable build varies by product, so cross-reference the build number from `SELECT @@VERSION` against the vendor's support information.

6. 06

   A setting in .NET or another client stack

   Sometimes the protocol in use is fixed by the application framework rather than the OLE DB driver. If updating the driver does not resolve it, check the client stack's settings as well.

Diagnostic Steps

1. 1

   Get the server's version and build

   Read-only

   Record the results of `SELECT @@VERSION` and `SERVERPROPERTY`, and cross-reference against the vendor's TLS 1.2 support information.

2. 2

   Enumerate connected client libraries

   Read-only

   Check `client_interface_name` via `sys.dm_exec_connections` and `sys.dm_exec_sessions` to identify connections from an old driver.

3. 3

   Check installed drivers on the client side

   Read-only

   Check which of SQLNCLI10 / SQLNCLI11 / MSOLEDBSQL (and which generation) is installed.

4. 4

   Check the OS's SCHANNEL settings

   Read-only

   Check whether TLS 1.0 / 1.1 / 1.2 are enabled or disabled. Read-only — no changes at this stage.

5. 5

   Run connection tests with and without encryption

   Low

   Compare the success/failure of `Encrypt=no` / `Encrypt=yes;TrustServerCertificate=no` / `Encrypt=yes;TrustServerCertificate=yes` to isolate whether the cause is the certificate or TLS itself.

6. 6

   Cross-reference the change history against when the failure started

   Read-only

   Check whether disabling a protocol on the server, swapping the driver, or renewing a certificate lines up with when connections stopped working.

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• The client OS's patch level

  Sometimes not just the driver but the OS's TLS implementation also needs an update.

• The application framework's protocol settings

  Check whether the protocol is fixed in something like .NET. Updating the driver alone does not always resolve this.

• Devices along the network path

  If TLS is terminated at a load balancer or proxy, its protocol settings are also worth checking.

• Connection errors in the SQL Server error log

  Check whether the server-side log recorded anything at the time of a connection failure, to see whether the disconnect happened before reaching the client.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• Cannot connect from SQL Server 2008 to AWS RDS
• Difference between SQLNCLI10 and MSOLEDBSQL19
• Cannot connect with TLS 1.2
• Getting a certificate error after updating the driver

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

A driver generation change typically surfaces as "something that had been working stops the moment a server setting changes." At GIIP, we periodically tally connected client libraries so we can list, at a glance, which systems still have connections coming in from an old-generation driver. Because changing TLS settings or swapping drivers has a broad scope of impact, these are excluded from automatic application — we handle identifying affected applications and organizing the migration order instead.

Related Services

• AWSマネージド運用の対応範囲
• 障害対応・火消し支援