SQL2022 TDS 8.0 Encryption=strict

Get-ChildItem Cert:\LocalMachine\My

#New-SelfSignedCertificate `
# -CertStoreLocation Cert:\LocalMachine\My `
# -Subject "CN=sqlserver01.example.local" `
# -DnsName "sqlserver01.example.local" `
# -FriendlyName "SQL Server TLS Certificate" `
# -KeyLength 2048 `
# -KeyAlgorithm RSA

[System.Net.Dns]::GetHostEntry($env:COMPUTERNAME).HostName

#AL02404368

New-SelfSignedCertificate `
 -CertStoreLocation Cert:\LocalMachine\My `
 -Subject "CN=AL02404368" `
 -DnsName "AL02404368" `
 -FriendlyName "SQL Server TLS Certificate" `
 -KeyLength 2048 `
 -KeyAlgorithm RSA

certlm.msc

$cert = Get-ChildItem Cert:\LocalMachine\My | Where-Object {$_.Subject -like "*AL02404368*"}

Export-Certificate `
  -Cert $cert `
  -FilePath C:\temp\sqlserver.cer

Import-Certificate `
  -FilePath C:\temp\sqlserver.cer `
  -CertStoreLocation Cert:\LocalMachine\Root


Write-Host "1. certlm.msc 에서 Personal\Certificates 에 생성된 인증서 확인"
Write-Host "2. SQL Server 서비스 계정에 private key 권한 부여"
Write-Host "3. SQL Server Configuration Manager 에서 해당 인증서 바인딩"
Write-Host "4. SQL Server 서비스 재시작"

 

 

using Microsoft.Data.SqlClient;
using System;
//using System.Data.SqlClient;


class Program
{
    static void Main()
    {

        while (true)
        {
           
            Console.WriteLine("연결 문자열을 선택하세요:");
            Console.Write("입력 (1 ~ 6): ");
            string input = Console.ReadLine();

            string connectionString;
            if (input == "1")
                connectionString = "Server=AL02404368;Database=master;User Id=hi;Password=jkfdsaju9834unif34jnjfjnk3okm;Encrypt=True;TrustServerCertificate=False;";
            else if (input == "2")
                connectionString = "Server=127.0.0.1;Database=master;User Id=hi;Password=jkfdsaju9834unif34jnjfjnk3okm;Encrypt=True;TrustServerCertificate=True;";
            else if (input == "3")
                connectionString = "Server=127.0.0.1;Database=master;User Id=hi;Password=jkfdsaju9834unif34jnjfjnk3okm;";
            else if (input == "4")
                connectionString = "Server=127.0.0.1;Database=master;User Id=hi;Password=jkfdsaju9834unif34jnjfjnk3okm;Encrypt=False;";
            else if (input == "5")
                connectionString = "Server=127.0.0.1;Database=master;User Id=hi;Password=jkfdsaju9834unif34jnjfjnk3okm;Encrypt=True;TrustServerCertificate=False;";
            else if (input == "6")
                connectionString = "Server=AL02404368;Database=master;User Id=hi;Password=jkfdsaju9834unif34jnjfjnk3okm;Encrypt=strict;";
            else
            {
                Console.WriteLine("잘못된 입력입니다. 1 또는 2를 입력하세요.");
                break;
            }

            try
            {
                Run(connectionString);
            }
            catch (Exception ex) { Console.WriteLine(ex.ToString()); }
        }

    }
    
    static void Run(string connectionString)
    {
        
        Console.Clear();
        Console.WriteLine(typeof(SqlConnection).Assembly.FullName);
        Console.WriteLine(connectionString);
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            string query = "SELECT @@VERSION";

            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                var result = cmd.ExecuteScalar();
                Console.WriteLine("SQL Server Version:");
                Console.WriteLine(result.ToString());
            }

            string connQuery = @"
SELECT
    session_id,
    encrypt_option,
    protocol_version,
    CONNECTIONPROPERTY('net_transport') AS NetTransport,
    CONNECTIONPROPERTY('protocol_type') AS ProtocolType,
    CONNECTIONPROPERTY('auth_scheme') AS AuthScheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID";

            using (SqlCommand cmd = new SqlCommand(connQuery, conn))
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                Console.WriteLine("\n[연결 정보]");
                while (reader.Read())
                {
                    Console.WriteLine($"  session_id      : {reader["session_id"]}");
                    Console.WriteLine($"  encrypt_option  : {reader["encrypt_option"]}");
                    Console.WriteLine($"  protocol_version: {reader["protocol_version"]}");
                    Console.WriteLine($"  NetTransport: {reader["NetTransport"]}");
                    Console.WriteLine($"  ProtocolType: {reader["ProtocolType"]}");
                    Console.WriteLine($"  AuthScheme: {reader["AuthScheme"]}");
                }
            }
        }
    }
}

 

sqlserver 2022 이상

using Microsoft.Data.SqlClient; // 5.1 이상

 

1. TCP Connection
2. TDS PreLogin (Tabular Data Stream)
3. Encryption negotiation
4. TLS handshake (optional) (Transport Layer Security)
5. Login packet
6. SQL session start

 

Encryption = strict 를 적용하면, TrustServerCertificate=True 옵션으로 통과할 수 없다.