Microsoft MVP성태의 닷넷 이야기
글쓴 사람
정성태 (techsharer at outlook.com)
홈페이지
첨부 파일
 
(연관된 글이 2개 있습니다.)

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_cert_1.png

위의 인증서는 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.




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

[연관 글]






[최초 등록일: ]
[최종 수정일: 6/15/2023]

Creative Commons License
이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.
by SeongTae Jeong, mailto:techsharer at outlook.com

비밀번호

댓글 작성자
 




1  2  3  [4]  5  6  7  8  9  10  11  12  13  14  15  ...
NoWriterDateCnt.TitleFile(s)
13490정성태12/19/20231392개발 환경 구성: 698. Golang - GLIBC 의존을 없애는 정적 빌드 방법
13489정성태12/19/20231179개발 환경 구성: 697. GoLand에서 ldflags 지정 방법
13488정성태12/18/20231187오류 유형: 884. HTTP 500.0 - 명령행에서 실행한 ASP.NET Core 응용 프로그램을 실행하는 방법
13487정성태12/16/20231399개발 환경 구성: 696. C# - 리눅스용 AOT 빌드를 docker에서 수행 [1]
13486정성태12/15/20231223개발 환경 구성: 695. Nuget config 파일에 값 설정/삭제 방법
13485정성태12/15/20231224오류 유형: 883. dotnet build/restore - error : Root element is missing
13484정성태12/14/20231344개발 환경 구성: 694. Windows 디렉터리 경로를 WSL의 /mnt 포맷으로 구하는 방법
13483정성태12/14/20231504닷넷: 2184. C# - 하나의 resource 파일을 여러 프로그램에서 (AOT 시에도) 사용하는 방법파일 다운로드1
13482정성태12/13/20231847닷넷: 2183. C# - eFriend Expert OCX 예제를 .NET Core/5+ Console App에서 사용하는 방법 [2]파일 다운로드1
13481정성태12/13/20231551개발 환경 구성: 693. msbuild - .NET Core/5+ 프로젝트에서 resgen을 이용한 리소스 파일 생성 방법파일 다운로드1
13480정성태12/12/20231583개발 환경 구성: 692. Windows WSL 2 + Chrome 웹 브라우저 설치
13479정성태12/11/20231567개발 환경 구성: 691. WSL 2 (Ubuntu) + nginx 환경 설정
13477정성태12/8/20231724닷넷: 2182. C# - .NET 7부터 추가된 Int128, UInt128 [1]파일 다운로드1
13476정성태12/8/20231542닷넷: 2181. C# - .NET 8 JsonStringEnumConverter의 AOT를 위한 개선파일 다운로드1
13475정성태12/7/20231566닷넷: 2180. .NET 8 - 함수 포인터에 대한 Reflection 정보 조회파일 다운로드1
13474정성태12/6/20231470개발 환경 구성: 690. 닷넷 코어/5+ 버전의 ilasm/ildasm 실행 파일 구하는 방법 - 두 번째 이야기
13473정성태12/5/20231580닷넷: 2179. C# - 값 형식(Blittable)을 메모리 복사를 이용해 바이트 배열로 직렬화/역직렬화파일 다운로드1
13472정성태12/4/20231452C/C++: 164. Visual C++ - InterlockedCompareExchange128 사용 방법
13471정성태12/4/20231500Copilot - To enable GitHub Copilot, authorize this extension using GitHub's device flow
13470정성태12/2/20231702닷넷: 2178. C# - .NET 8부터 COM Interop에 대한 자동 소스 코드 생성 도입파일 다운로드1
13469정성태12/1/20231535닷넷: 2177. C# - (Interop DLL 없이) CoClass를 이용한 COM 개체 생성 방법파일 다운로드1
13468정성태12/1/20231473닷넷: 2176. C# - .NET Core/5+부터 달라진 RCW(Runtime Callable Wrapper) 대응 방식파일 다운로드1
13467정성태11/30/20231498오류 유형: 882. C# - Unhandled exception. System.Runtime.InteropServices.COMException (0x800080A5)파일 다운로드1
13466정성태11/29/20231630닷넷: 2175. C# - DllImport 메서드의 AOT 지원을 위한 LibraryImport 옵션
13465정성태11/28/20231457개발 환경 구성: 689. MSBuild - CopyToOutputDirectory가 "dotnet publish" 시에는 적용되지 않는 문제파일 다운로드1
13464정성태11/28/20231594닷넷: 2174. C# - .NET 7부터 UnmanagedCallersOnly 함수 export 기능을 AOT 빌드에 통합파일 다운로드1
1  2  3  [4]  5  6  7  8  9  10  11  12  13  14  15  ...