openssl로 생성한 인증서를 SQL Server의 암호화 인증서로 설정하는 방법
우선, SQL Server의 암호화 인증서 설정 방법은 아래의 글에서 설명합니다.
Configure SQL Server Database Engine for encrypting connections
; https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption
관련해서는 저도 예전에 소개한 적이 있는데요,
SQL 서버 역시 SSL 3.0/TLS 1.0만을 지원하는 듯!
; https://www.sysnet.pe.kr/2/0/1835
이때 가장 애먹는 부분이 바로 SQL 서버가 요구하는 인증서 형식이 좀 까다롭다는 점입니다. 물론 이것 역시 문서로는 나와 있습니다.
Certificate requirements for SQL Server
; https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-requirements
사실, 암호화 통신을 위해 굳이 사용자 인증서를 설치할 필요는 없습니다. 왜냐하면, 기본적으로 SQL 서버는 self-signed 인증서를 할당해 주기 때문입니다.
어떤 인증서를 할당해 주는지 혹시 확인할 수 있을까요? ^^
이에 대해 아래의 Q&A에서 언급하고 있는,
OpenSSL: Fetching SQL Server public certificate
; https://stackoverflow.com/questions/34981859/openssl-fetching-sql-server-public-certificate
get_tds_cert.py
; https://gist.github.com/lnattrass/a4a91dbf439fc1719d69f7865c1b1791
get_tds_cert.py 스크립트를 이용해 다음과 같은 출력을 얻을 수 있습니다.
// HTTPS의 경우 openssl s_client ...
c:\temp> python get_tds_cert.py win2008r2 1433
# get_tdspacket: 0, tdspacket len: 43
# Header: {'type': 4, 'status': 1, 'length': 43, 'channel': 0, 'packet': 1, 'window': 0}
# Remaining tdspbuf length: 0
# Starting TLS handshake loop..
# Shaking (0/5)
# get_tdspacket: 0, tdspacket len: 828
# Header: {'type': 18, 'status': 1, 'length': 828, 'channel': 0, 'packet': 0, 'window': 0}
# Remaining tdspbuf length: 0
# Shaking (1/5)
# get_tdspacket: 0, tdspacket len: 115
# Header: {'type': 18, 'status': 1, 'length': 115, 'channel': 0, 'packet': 0, 'window': 0}
# Remaining tdspbuf length: 0
# Handshake completed, dumping certificates
-----BEGIN CERTIFICATE-----
MIIB+zCCAWSgAwIBAgIQWY2JBmfPFotGxQeMgb0jDzANBgkqhkiG9w0BAQUFADA7
MTkwNwYDVQQDHjAAUwBTAEwAXwBTAGUAbABmAF8AUwBpAGcAbgBlAGQAXwBGAGEA
bABsAGIAYQBjAGswIBcNMjMwNjEwMTYxMzI0WhgPMjA1MzA2MTAxNjEzMjRaMDsx
OTA3BgNVBAMeMABTAFMATABfAFMAZQBsAGYAXwBTAGkAZwBuAGUAZABfAEYAYQBs
AGwAYgBhAGMAazCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA0TyjQzvnDIl6
a+kuBjWWTWjpqK+h7y/Q6Fu490Ik0w4zC/r6e1CN9rmKij+NV/LyisVTNvpnZSR4
0f6KTLMaSUEmYXT2BzGAcUyXb/IOIS/q5CQx/nT0xjKiK/Wmeaf9eEmS1mWlKN+x
ASks8DQnCjCP9aalpFrPrgcFBLzLDX8CAwEAATANBgkqhkiG9w0BAQUFAAOBgQDN
I+pwPdws9zaB8D4Q5nm78mDh/8ovvrpknDVKC6pi7PAo+UEcPxoaZHl90YgW4/5n
FOHb1SoKXaybP9oMJjPl4Q2/84d3vhpjTIRGlBOyyL41zGl2I1SL57UTzRx9TgD2
pxAhWTlTrYlrRsXd8e+4XWvmCZVP3N8nOP+WHvjZPA==
-----END CERTIFICATE-----
출력 결과에서 마지막 BEGIN/END CERTIFICATE를 test.crt 파일로 저장 후 윈도우 탐색기에서 더블 클릭해 보면 인증서 정보를 대략 확인할 수 있습니다.
위의 인증서는 SQL Server 2008 R2로부터 구한 것인데 기본적으로 키 길이가 1024비트임을 알 수 있습니다.
이제 기본 인증서를 길이가 2048인 걸로 늘려 대체해 볼까요? 이를 위해 우선 openssl을 이용해 다음과 같이 인증서를 생성할 수 있습니다.
// Windows 환경의 경우 openssl은 vcpkg를 이용해 빌드 가능, 혹은 winget을 이용해 설치
// Root CA Key/Cert
c:\temp> openssl genrsa -out win2008r2_ca.key 2048
c:\temp> openssl req -x509 -new -nodes -key win2008r2_ca.key -sha256 -days 3650 -out win2008r2_ca.crt -subj "/CN=win2008r2_ca"
// Key/Cert
c:\temp> openssl genrsa -out win2008r2.key 2048
C:\temp> type win2008r2.cnf
FQDN = win2008r2
ALTNAMES = DNS:win2008r2
[ req ]
default_bits = 2048
default_md = sha256
prompt = no
encrypt_key = no
distinguished_name = dn
req_extensions = req_ext
[ dn ]
CN = $FQDN
[ req_ext ]
extendedKeyUsage = serverAuth
subjectAltName = $ALTNAMES
keyUsage = digitalSignature,keyEncipherment
[ x509_ext ]
extendedKeyUsage = serverAuth
authorityKeyIdentifier = keyid,issuer
subjectAltName = $ALTNAMES
keyUsage = digitalSignature,keyEncipherment
c:\temp> openssl req -key win2008r2.key -new -out win2008r2.csr -config win2008r2.cnf
c:\temp> openssl x509 -req -days 3650 -in win2008r2.csr -CA win2008r2_ca.crt -CAkey win2008r2_ca.key -out win2008r2.crt -extfile win2008r2.cnf -extensions x509_ext // https://www.sysnet.pe.kr/2/0/12570
c:\temp> openssl pkcs12 -legacy -export -out win2008r2_legacy.pfx -inkey win2008r2.key -in win2008r2.crt // # legacy 옵션
위의 예제는 SQL 서버가 설치된 컴퓨터의 이름이 "win2008r2"라고 가정해 작성한 것이므로 이것은 여러분들의 환경에 맞게, 일례로 아래의 결과로 나오는 이름을 cnf 파일의 FQDN의 값으로 설정해야 합니다.
C:\temp> echo %COMPUTERNAME%
testpc
C:\temp> type win2008r2.cnf
FQDN = testpc
ALTNAMES = DNS:testpc
...[생략]...
(FQDN으로 결국 설정되는) DN 섹션의 CN 값이 중요한데요, 저 값이 틀리면 "SQL Server Configuration Manager"의 "SQL Server Network Configuration" / "Protocols for [YOUR_SQL_SERVER_INSTANCE]" 노드의 속성 창 / "Certificate" 목록에 인증서가 열거되지 않습니다.
또한, 해당 SQL Server가 Active Directory에 속해 있다면 CN 값에도 반영해야 합니다. 예를 들어, "testad.com" AD에 참여한 "testpc"라는 멤버에 SQL 서버가 설치돼 있다면 "CN"은 반드시 "testpc.testad.com"으로 설정해야 합니다.
가장 실수하기 쉬운 부분이 CN 값이어서 위에서 강조를 했지만, "
Certificate requirements for SQL Server" 문서에는 다음의 3가지 사항도 명시하고 있습니다.
- Server Authentication
- KeySpec option of AT_KEYEXCHANGE
- the certificate's key usage property (KEY_USAGE) also includes key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE) and a digital signature (CERT_DIGITAL_SIGNATURE_KEY_USAGE)
자신이 생성한 인증서가 Server Authentication, AT_KEYEXCHANGE, CERT_KEY_ENCIPHERMENT_KEY_USAGE, CERT_DIGITAL_SIGNATURE_KEY_USAGE 속성을 가지고 있는지 확인하고 싶다면 certutil.exe를 이용하시면 됩니다.
c:\temp> certutil -dump -v win2008r2_legacy.pfx
Enter PFX password:
================ Certificate 0 ================
================ Begin Nesting Level 1 ================
X509 Certificate:
Version: 3
Serial Number: 13d377df00963a5c88937bb712de529f455e790f
Signature Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.11 sha256RSA
Algorithm Parameters:
05 00
Issuer:
CN=win2008r2_ca
NotBefore: 2023-06-13 오후 11:06
NotAfter: 2033-06-10 오후 11:06
Subject:
CN=win2008r2.testad.com
Public Key Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.1 RSA (RSA_SIGN)
Algorithm Parameters:
05 00
Public Key Length: 2048 bits
Public Key: UnusedBits = 0
0000 30 82 01 0a 02 82 01 01 00 a6 ec 93 9a e1 40 d9
0010 85 b4 3c 4a 12 2e 32 ae 40 08 d8 bd d5 eb 5e d0
0020 d1 5a 4f 4d 16 b9 15 e9 5c c1 8c f8 45 3b fc b9
0030 95 d8 7a 5f 5d dd 56 45 3f b0 32 28 58 7c 0f 7d
0040 8f e3 2a 5f 83 dd 39 84 3e 89 be 62 de d3 72 13
0050 d9 5d 94 22 a2 a3 72 d6 05 e1 05 d1 d6 db 35 20
0060 40 34 17 4d d5 f8 17 99 9f 39 07 f3 e2 c8 18 b5
0070 e5 02 3e f9 96 ed 0c 73 6f 35 d8 a9 da bc 82 5f
0080 0c d7 63 6b 92 98 61 26 b7 07 c7 2e 80 a8 f5 7a
0090 ef 9d 4a 0a 56 75 d5 83 c4 4e b6 38 e9 f8 93 14
00a0 91 40 d0 a6 2c 74 2d 9a 2a 39 f6 c9 59 4b 15 05
00b0 c9 84 91 0f 49 71 05 82 4a 7e 4e 92 af 28 79 71
00c0 98 0d 43 da 6f 43 bc 1e 6a 3c 30 75 39 e6 a8 d9
00d0 28 56 24 62 d4 70 cb 2a 18 76 a3 ac 0f 2e 1e 4a
00e0 43 0f d3 ed 31 4e 7e d9 39 d4 51 4c 05 21 60 63
00f0 91 68 ca fb 5d 78 63 61 4c 60 67 a7 e8 54 da 58
0100 6d 81 b8 15 8a be 41 41 77 02 03 01 00 01
Certificate Extensions: 5
2.5.29.37: Flags = 0, Length = c
Enhanced Key Usage
Server Authentication (1.3.6.1.5.5.7.3.1)
2.5.29.35: Flags = 0, Length = 18
Authority Key Identifier
KeyID=90 1c c5 bb 4f 51 74 2c 72 59 ed 52 04 55 cd 20 9d d1 36 dd
2.5.29.17: Flags = 0, Length = d
Subject Alternative Name
DNS Name=win2008r2
2.5.29.15: Flags = 0, Length = 4
Key Usage
Digital Signature, Key Encipherment (a0)
2.5.29.14: Flags = 0, Length = 16
Subject Key Identifier
9c f6 50 1f cf 0e 6e d1 ed 27 43 93 16 31 0e 4f 66 1a 4d b6
Signature Algorithm:
Algorithm ObjectId: 1.2.840.113549.1.1.11 sha256RSA
Algorithm Parameters:
05 00
Signature: UnusedBits=0
0000 7c 46 db 6f 43 eb 9f e7 2e 4f ce c0 f8 56 1f 6f
0010 75 bf 32 6c 89 31 4f 74 d2 63 68 6c 74 0f c2 7e
0020 16 81 c7 15 e3 6f 5c f1 15 da a0 4b 9f 7e 6c 4e
0030 59 81 4e c2 74 9c 2a dc c6 c6 3e 6f 31 6f d3 2f
0040 23 77 d6 d2 59 6c 23 76 17 ac 44 b3 0d d5 d1 36
0050 92 24 13 f2 db cf d2 46 20 99 8e 89 61 05 e5 90
0060 eb 9e 5e dd 38 6d e5 77 b5 af cd d0 61 63 f0 91
0070 58 94 1c d8 ee d9 54 10 d9 d1 ea 63 35 d0 2b e9
0080 5f c6 5c bf 1d ca a5 b0 13 5c c7 20 80 11 84 d3
0090 af cd 08 e8 3c 0b 8b 85 71 1b 4b 91 01 33 ab 1b
00a0 8b 1a d2 07 28 40 57 e8 22 d5 49 73 a0 fd 7b 7b
00b0 27 be 6f 4e 5c 7e 20 f4 12 da b1 bf ae ce 90 6e
00c0 ad 60 62 96 21 91 59 ee 93 83 bc f2 fd d7 5f 42
00d0 f0 9b f0 44 1e 13 88 cb cd 35 0d 31 4a 0e bc cd
00e0 26 45 7b 74 a1 42 f2 0c 2e ac f0 cc b0 3e 20 6d
00f0 8b 63 55 21 0d c3 32 fc fc 0f 6b 7d 7b 9a 4b 12
Non-root Certificate
Key Id Hash(rfc-sha1): 9c f6 50 1f cf 0e 6e d1 ed 27 43 93 16 31 0e 4f 66 1a 4d b6
Key Id Hash(sha1): 69 f3 92 d4 0a 17 cb 77 f7 dc ac 38 0b 77 48 13 41 0d 8e ae
Cert Hash(md5): be 92 4c 0c 31 99 0e d7 17 e7 89 0a 63 21 ab 65
Cert Hash(sha1): ce ed bc a1 0e fc 1f dc bc 8d 6f 83 96 fd 3e 74 24 03 e8 da
---------------- End Nesting Level 1 ----------------
CERT_KEY_IDENTIFIER_PROP_ID(20):
9c f6 50 1f cf 0e 6e d1 ed 27 43 93 16 31 0e 4f 66 1a 4d b6
CERT_MD5_HASH_PROP_ID(4):
be 92 4c 0c 31 99 0e d7 17 e7 89 0a 63 21 ab 65
CERT_SHA1_HASH_PROP_ID(3):
ce ed bc a1 0e fc 1f dc bc 8d 6f 83 96 fd 3e 74 24 03 e8 da
CERT_KEY_PROV_INFO_PROP_ID(2):
Key Container = {69ABBFBC-33AA-4FB3-A576-6B27F3A4ADC0}
Unique container name: af4d3789d4097b8421bbf68ada56ca64_262c3b66-9441-4667-8655-a0c79c97d762
Provider = Microsoft Enhanced Cryptographic Provider v1.0
ProviderType = 1
Flags = 0
KeySpec = 1 -- AT_KEYEXCHANGE
Unique container name: af4d3789d4097b8421bbf68ada56ca64_262c3b66-9441-4667-8655-a0c79c97d762
PP_KEYSTORAGE = 1
CRYPT_SEC_DESCR -- 1
KP_PERMISSIONS = 3f (63)
CRYPT_ENCRYPT -- 1
CRYPT_DECRYPT -- 2
CRYPT_EXPORT -- 4
CRYPT_READ -- 8
CRYPT_WRITE -- 10 (16)
CRYPT_MAC -- 20 (32)
D:...[생략]...
Allow Full Control NT AUTHORITY\SYSTEM
Allow Full Control BUILTIN\Administrators
Allow Full Control ...[생략]...
Private Key:
PRIVATEKEYBLOB
Version: 2
aiKeyAlg: 0xa400
CALG_RSA_KEYX
Algorithm Class: 0xa000(5) ALG_CLASS_KEY_EXCHANGE
Algorithm Type: 0x400(2) ALG_TYPE_RSA
Algorithm Sub-id: 0x0(0) ALG_SID_RSA_ANY
0000 52 53 41 32 RSA2
0000 ...
048c
Encryption test passed
CertUtil: -dump command completed successfully.
마지막으로 가장 중요한 설정이 있는데요, 위의 인증서로 "SQL Server Configuration Manager"를 이용해 설정했다면, 반드시
private key 파일에 대한 읽기 권한이 SQL Server Service 프로세스에 있는지 확인해야 합니다. 만약 권한이 없는 상태에서 SQL 서버를 재시작하게 되면 서비스 실행이 실패하게 됩니다.
이때의 이벤트 로그를 보면 다음과 같은 식의 항목들을 볼 수 있습니다.
Log Name: Application
Source: MSSQLSERVER
...[생략]...
Event ID: 26010
Task Category: Server
Level: Information
Keywords: Classic
User: N/A
Computer: win2008r2
Description:
The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
Log Name: Application
Source: MSSQLSERVER
...[생략]...
Event ID: 26014
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: win2008r2
Description:
Unable to load user-specified certificate [Cert Hash(sha1) "CEEDBCA10EFC1FDCBC8D6F8396FD3E742403E8DA"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
Log Name: Application
Source: MSSQLSERVER
...[생략]...
Event ID: 17182
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: win2008r2
Description:
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
Log Name: Application
Source: MSSQLSERVER
...[생략]...
Event ID: 17182
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: win2008r2
Description:
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
Log Name: Application
Source: MSSQLSERVER
...[생략]...
Event ID: 17826
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: win2008r2
Description:
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Log Name: Application
Source: MSSQLSERVER
...[생략]...
Event ID: 17120
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: win2008r2
Description:
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]