J & J - 정성태의 닷넷 이야기

사용자

메뉴

최근 덧글

[정성태] 그냥 RSS Reader 기능과 약간의 UI 편의성 때문에 사용...[이종효] 오래된 소프트웨어는 보안 위협이 되기도 합니다. 혹시 어떤 기능...[정성태] @Keystroke IEEE의 문서를 소개해 주시다니... +_...[손민수 (Keystroke)] 괜히 듀얼채널 구성할 때 한번에 같은 제품 사라고 하는 것이 아...[정성태] 전각(Full-width)/반각(Half-width) 기능을 토...[정성태] Vector에 대한 내용은 없습니다. Vector가 닷넷 BCL...[orion] 글 읽고 찾아보니 디자인 타임에는 InitializeCompon...[orion] 연휴 전에 재현 프로젝트 올리자 생각해 놓고 여의치 않아서 못 ...[정성태] 아래의 글에 정리했으니 참고하세요. C# - Typed D...[정성태] 간단한 재현 프로젝트라도 있을까요? 저런 식으로 설명만 해...

글쓰기

제목

이름

암호

전자우편

HTML

홈페이지

유형

내용

<div style='display: inline'>
<h1 style='font-family: Malgun Gothic, Consolas; font-size: 20pt; color: #006699; text-align: center; font-weight: bold'>openssl로 생성한 인증서를 SQL Server의 암호화 인증서로 설정하는 방법</h1>

우선, SQL Server의 암호화 인증서 설정 방법은 아래의 글에서 설명합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Configure SQL Server Database Engine for encrypting connections
; <a target='tab' href='https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption'>https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption</a>
</pre>
 
관련해서는 저도 예전에 소개한 적이 있는데요, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
SQL 서버 역시 SSL 3.0/TLS 1.0만을 지원하는 듯!
; <a target='tab' href='https://www.sysnet.pe.kr/2/0/1835'>https://www.sysnet.pe.kr/2/0/1835</a>
</pre>
 
이때 가장 애먹는 부분이 바로 SQL 서버가 요구하는 인증서 형식이 좀 까다롭다는 점입니다. 물론 이것 역시 문서로는 나와 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Certificate requirements for SQL Server
; <a target='tab' href='https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-requirements'>https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-requirements</a>
</pre>
 
사실, 암호화 통신을 위해 굳이 사용자 인증서를 설치할 필요는 없습니다. 왜냐하면, 기본적으로 SQL 서버는 self-signed 인증서를 할당해 주기 때문입니다. <a target='tab' href='https://www.sysnet.pe.kr/2/0/13372'>어떤 인증서를 할당해 주는지 혹시 확인</a>할 수 있을까요? ^^ 
 
이에 대해 아래의 Q&amp;A에서 언급하고 있는, 
<a name='get_tds_cert'></a>
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
OpenSSL: Fetching SQL Server public certificate
; <a target='tab' href='https://stackoverflow.com/questions/34981859/openssl-fetching-sql-server-public-certificate'>https://stackoverflow.com/questions/34981859/openssl-fetching-sql-server-public-certificate</a>

get_tds_cert.py
; <a target='tab' href='https://gist.github.com/lnattrass/a4a91dbf439fc1719d69f7865c1b1791'>https://gist.github.com/lnattrass/a4a91dbf439fc1719d69f7865c1b1791</a>
</pre>
 
get_tds_cert.py 스크립트를 이용해 다음과 같은 출력을 얻을 수 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
// HTTPS의 경우 <a target='tab' href='https://www.sysnet.pe.kr/2/0/11462#13373'>openssl s_client ...</a>

c:\temp&gt; 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-----
</pre>
 
출력 결과에서 마지막 BEGIN/END CERTIFICATE를 test.crt 파일로 저장 후 윈도우 탐색기에서 더블 클릭해 보면 인증서 정보를 대략 확인할 수 있습니다. 
 
<img alt='sql_server_cert_1.png' src='/SysWebRes/bbs/sql_server_cert_1.png' /> 
 
위의 인증서는 SQL Server 2008 R2로부터 구한 것인데 기본적으로 키 길이가 1024비트임을 알 수 있습니다. 
 
<hr style='width: 50%' /> 
<a name='user_cert'></a>
 
이제 기본 인증서를 길이가 2048인 걸로 늘려 대체해 볼까요? 이를 위해 우선 openssl을 이용해 다음과 같이 인증서를 생성할 수 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
// Windows 환경의 경우 <a target='tab' href='https://www.sysnet.pe.kr/2/0/11409#openssl'>openssl은 vcpkg를 이용해 빌드 가능</a>, 혹은 <a target='tab' href='https://www.sysnet.pe.kr/2/0/12657#openssl'>winget을 이용해 설치</a>

// Root CA Key/Cert
c:\temp&gt; openssl genrsa -out win2008r2_ca.key 2048

c:\temp&gt; openssl req -x509 -new -nodes -key win2008r2_ca.key -sha256 -days 3650 -out win2008r2_ca.crt -subj "/CN=win2008r2_ca"

// Key/Cert
c:\temp&gt; openssl genrsa -out win2008r2.key 2048

C:\temp&gt; 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&gt; openssl req -key win2008r2.key -new -out win2008r2.csr -config win2008r2.cnf

c:\temp&gt; 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 // <a target='tab' href='https://www.sysnet.pe.kr/2/0/12570'>https://www.sysnet.pe.kr/2/0/12570</a>

c:\temp&gt; openssl pkcs12 -legacy -export -out win2008r2_legacy.pfx -inkey win2008r2.key -in win2008r2.crt // # <a target='tab' href='https://www.sysnet.pe.kr/2/0/13367#legacy_opt'>legacy 옵션</a>
</pre>
 
위의 예제는 SQL 서버가 설치된 컴퓨터의 이름이 "win2008r2"라고 가정해 작성한 것이므로 이것은 여러분들의 환경에 맞게, 일례로 아래의 결과로 나오는 이름을 cnf 파일의 FQDN의 값으로 설정해야 합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
C:\temp&gt; echo %COMPUTERNAME%
testpc

C:\temp&gt; type win2008r2.cnf
FQDN = testpc
ALTNAMES = DNS:testpc

...[생략]...
</pre>
 
(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"으로 설정해야 합니다. 
 
<hr style='width: 50%' /> 
 
가장 실수하기 쉬운 부분이 CN 값이어서 위에서 강조를 했지만, "<a target='tab' href='https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-requirements'>Certificate requirements for SQL Server</a>" 문서에는 다음의 3가지 사항도 명시하고 있습니다. 
 
<ul>
<li>Server Authentication</li>
<li>KeySpec option of AT_KEYEXCHANGE</li>
<li>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)</li>
</ul>
 
자신이 생성한 인증서가 Server Authentication, AT_KEYEXCHANGE, CERT_KEY_ENCIPHERMENT_KEY_USAGE, CERT_DIGITAL_SIGNATURE_KEY_USAGE 속성을 가지고 있는지 확인하고 싶다면 certutil.exe를 이용하시면 됩니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
c:\temp&gt; 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.
</pre>
 
마지막으로 가장 중요한 설정이 있는데요, 위의 인증서로 "SQL Server Configuration Manager"를 이용해 설정했다면, 반드시 <a target='tab' href='https://www.sysnet.pe.kr/2/0/1835#key_access'>private key 파일에 대한 읽기 권한이 SQL Server Service 프로세스에 있는지 확인</a>해야 합니다. 만약 권한이 없는 상태에서 SQL 서버를 재시작하게 되면 서비스 실행이 실패하게 됩니다. 
 
이때의 이벤트 로그를 보면 다음과 같은 식의 항목들을 볼 수 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
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.
</pre>

<hr />[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

</div>

첨부파일

스팸 방지용 인증 번호

1757 (왼쪽의 숫자를 입력해야 합니다.)