SQL Server 연결시 SSL 오류 발생

1. 서버 환경

소스 시스템: Windows 2012 R2 , JDK 1.6 , JDBC4.0

타켓 시스템: Windows 2012 R2 , MS SQL 2012 SP4 아래와 같은 오류 메시지가 뜹니다.

 

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 드라이버가 SSL(Secure Sockets Layer) 암호화를 사용하여 SQL Sever로 보안 연결을 설정할 수 없습니다. 오류: "SQL Server에서 불완전한 응답을 반환했습니다. 연결이 닫혔습니다. Caused by: java.io.IOException: SQL Server에서 불완전한 응답을 반환했습니다. 연결이 닫혔습니다.

 

2. JDBC 4.0 은 지원 종료

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver15

Microsoft JDBC Driver for SQL Server support matrix - SQL Server

3. Windows 2016 버전에서는 파워쉘 스크립트로 반영

foreach ($CipherSuite in $(Get-TlsCipherSuite).Name)
{
    if ( $CipherSuite.substring(0,7) -eq "TLS_DHE" )
    {
       "Existing enabled TLS_DHE* cipher suite: " + $CipherSuite
    }
    else
    {
        "Existing enabled cipher suite will remain enabled: " + $CipherSuite
    }
} 

foreach ($CipherSuite in $(Get-TlsCipherSuite).Name)
{
    if ( $CipherSuite.substring(0,7) -eq "TLS_DHE" )
    {
       "Disabling cipher suite: " + $CipherSuite
       Disable-TlsCipherSuite -Name $CipherSuite
    }
    else
    {
        "Existing enabled cipher suite will remain enabled: " + $CipherSuite
    }
}

https://support.microsoft.com/en-us/help/4557473/errors-when-applications-try-to-connect-to-sql-server-in-windows

https://support.microsoft.com/en-us/help/4557473/errors-when-applications-try-to-connect-to-sql-server-in-windows

4. Windows 2012 R2에서 적용방법

Step 1) 실행창에서 gpedit.msc 수행

Step 2) Computer Configuration– Administrative Templates – Network– SSL Configuration Settings – [SSL Cipher Suite Order]

Step 3) 정책 Enabled 후 SSL Cipher Suites 항목에 아래의 Cipher suites List를 복사합니다.

 

TLS_RSA_WITH_AES_128_CBC_SHA256,

TLS_RSA_WITH_AES_128_CBC_SHA,

TLS_RSA_WITH_AES_256_CBC_SHA256,
TLS_RSA_WITH_AES_256_CBC_SHA,

TLS_RSA_WITH_3DES_EDE_CBC_SHA,

TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256,

TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P384,

TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA_P256,
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA_P384,

TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P256,
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P384,

TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256_P256,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256_P256,

TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384_P384,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384_P384,

TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA_P256,
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA_P384,

TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA_P256,
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA_P384,

SSL_CK_DES_192_EDE3_CBC_WITH_MD5,
TLS_RSA_WITH_NULL_SHA256,

TLS_RSA_WITH_NULL_SHA

 

아래 TLS_DHE* 5개의 Cipher suite 를 제거한 결과입니다.

 

TLS_DHE_DSS_WITH_AES_128_CBC_SHA256,

TLS_DHE_DSS_WITH_AES_128_CBC_SHA,

TLS_DHE_DSS_WITH_AES_256_CBC_SHA256,

TLS_DHE_DSS_WITH_AES_256_CBC_SHA,

TLS_DHE_DSS_WITH_3DES_EDE_CBC_SHA,