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

사용자

메뉴

최근 덧글

[정성태] 그런 부분은 클라우드 업체 쪽에 문의를 하는 것이 더 좋지 않을...[정성태] 정적 분석과 함께, 이제는 실행 시 성능 분석까지 (비록 Azu...[정성태] .NET Source Browser를 이용해 Roslyn 소스 ...[정성태] Experimental C# Interceptors: AOT &...[정성태] .NET Conf 2023 (Day 2) - Tiny, fast...[정성태] The end of the Tye Experiment #1622...[정성태] This is a simple app that converts ...[정성태] Wrathmark: An Interesting Compute W...[정성태] FFmpeg Filters Every Youtuber Needs...[정성태] 일단, PInvokeStackImbalance 오류가 발생했다는...

글쓰기

제목

이름

암호

전자우편

HTML

홈페이지

유형

내용

<div style='display: inline'>
<h1 style='font-family: Malgun Gothic, Consolas; font-size: 20pt; color: #006699; text-align: center; font-weight: bold'>C# - Ubuntu + Microsoft.Data.SqlClient + SQL Server 2008 R2 연결 방법</h1>

모든 서버를 테스트할 수는 없었지만 적어도 SQL Server 2016 이상의 버전에서는 이 글의 내용에 따른 문제가 발생하지 않습니다. 
 
<hr style='width: 50%' />
 
다음과 같이 간단한 SqlConnection.Open 테스트 코드를 작성하고, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
using Microsoft.Data.SqlClient;

namespace ConsoleApp1
{
 // Install-Package Microsoft.Data.SqlClient
 internal class Program
 {
 static void Main(string[] args)
 {
 SqlConnection conn = new SqlConnection(
 "User ID=tstusr;Password=tstusr_pw;Persist Security Info=False;Initial Catalog=TestDB;Data Source=192.168.100.50");
 conn.Open();
 conn.Close();
 }
 }
}
</pre>
 
<a target='tab' href='https://www.microsoft.com/en-us/download/details.aspx?id=44271'>Microsoft® SQL Server® 2008 R2 Service Pack 3</a>을 적용한 (10.50.6000.34 버전의) SQL Server에 접속해 보겠습니다. 
 
우선, 클라이언트가 Windows 환경에 있으면 이런 예외가 발생합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
 ---&gt; System.ComponentModel.Win32Exception (0x80090325): The certificate chain was issued by an authority that is not trusted.
 at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
 at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
 ...[생략]...
 at Microsoft.Data.SqlClient.SqlConnection.Open()
 at ConsoleApp1.Program.Main(String[] args) in C:\temp\ConsoleApp1\ConsoleApp1\Program.cs:line 13
ClientConnectionId:a429ef29-8e36-4a31-883f-fb9ba3ad81f8
Error Number:-2146893019,State:0,Class:20
</pre>
 
오류 메시지에도 나오지만, 이 문제는 <a target='tab' href='https://www.sysnet.pe.kr/2/0/13340'>TrustServerCertificate=true 옵션으로 해결</a>할 수 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
"TrustServerCertificate=true; User ID=tstusr;Password=tstusr_pw;Persist Security Info=False;Initial Catalog=TestDB;Data Source=192.168.100.50"
</pre>
<a name='sec_warn_tls12'></a>
 
이후 다시 실행하면, 콘솔 창에 경고 메시지는 나오지만, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Security Warning: The negotiated TLS 1.0 is an insecure protocol and is supported for backward compatibility only. The recommended protocol version is TLS 1.2 and later.
</pre>
 
어쨌든, 정상적으로 실행은 됩니다. 
 
<hr style='width: 50%' /> 
 
그런데, 동일한 소스 코드를 WSL + ubuntu 20.04 환경에서 돌리면 이런 오류가 발생합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed)
 ---&gt; System.IO.IOException: Received an unexpected EOF or 0 bytes from the transport stream.
 at System.Net.Security.SslStream.ReceiveBlobAsync[TIOAdapter](CancellationToken cancellationToken)
 at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](Boolean receiveFirst, Byte[] reAuthenticationData, CancellationToken cancellationToken)
 at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
 ...[생략]...
 at Microsoft.Data.SqlClient.SqlConnection.Open()
 at ConsoleApp1.Program.Main(String[] args) in /mnt/c/temp/ConsoleApp1/ConsoleApp1/Program.cs:line 13
</pre>
 
이때의 SQL Server 2008 R2 측에는 이벤트 로그에 다음과 같은 항목이 남습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Log Name: System
Source: Schannel
Event ID: 36888
...[생략]...
Description:
The following fatal alert was generated: 40. The internal error state is 1205.

Log Name: System
Source: Schannel
Event ID: 36874
...[생략]...
Description:
An TLS 1.0 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.
</pre>
 
그러니까, TLS 1.0으로 붙었지만 지원되지 않는 <a target='tab' href='https://www.sysnet.pe.kr/2/0/12504#6'>cipher suites</a>로 시도했기 때문에 오류가 발생했다는 것인데요, 이것을 해결하려면 openssl.cnf 파일에 설정을 추가해야 합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
$ ls /usr/lib/ssl/openssl.cnf -l
lrwxrwxrwx 1 root root 20 Apr 18 03:11 /usr/lib/ssl/openssl.cnf -&gt; /etc/ssl/openssl.cnf

$ cat /etc/ssl/openssl.cnf

openssl_conf = default_conf

#
# OpenSSL example configuration file.
# This is mostly being used for generation of certificate requests.
#

# Note that you can include other files from the main configuration
# file using the .include directive.
#.include filename

# This definition stops the following lines choking if HOME isn't
# defined.
HOME            = .

// ...[생략]...
ess_cert_id_chain   = no    # Must the ESS cert id chain be included?
                # (optional, default: no)
ess_cert_id_alg     = sha1  # algorithm to compute certificate
                # identifier (optional, default: sha1)

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = DEFAULT:@SECLEVEL=1

</pre>
 
<a target='tab' href='https://www.sysnet.pe.kr/2/0/12270'>SECLEVEL 설정</a>에 대해 문서를 찾아보면, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
DEFAULT CALLBACK BEHAVIOUR
; <a target='tab' href='https://www.openssl.org/docs/man1.1.1/man3/SSL_CTX_set_security_level.html'>https://www.openssl.org/docs/man1.1.1/man3/SSL_CTX_set_security_level.html</a>
</pre>
 
아래와 같은 의미를 갖는다고 하는데요, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Level 0
Everything is permitted. This retains compatibility with previous versions of OpenSSL.

Level 1
The security level corresponds to a minimum of 80 bits of security. Any parameters offering below 80 bits of security are excluded. As a result RSA, DSA and DH keys shorter than 1024 bits and ECC keys shorter than 160 bits are prohibited. All export cipher suites are prohibited since they all offer less than 80 bits of security. SSL version 2 is prohibited. Any cipher suite using MD5 for the MAC is also prohibited.

Level 2
Security level set to 112 bits of security. As a result RSA, DSA and DH keys shorter than 2048 bits and ECC keys shorter than 224 bits are prohibited. In addition to the level 1 exclusions any cipher suite using RC4 is also prohibited. SSL version 3 is also not allowed. Compression is disabled.

Level 3
Security level set to 128 bits of security. As a result RSA, DSA and DH keys shorter than 3072 bits and ECC keys shorter than 256 bits are prohibited. In addition to the level 2 exclusions cipher suites not offering forward secrecy are prohibited. TLS versions below 1.1 are not permitted. Session tickets are disabled.

Level 4
Security level set to 192 bits of security. As a result RSA, DSA and DH keys shorter than 7680 bits and ECC keys shorter than 384 bits are prohibited. Cipher suites using SHA1 for the MAC are prohibited. TLS versions below 1.2 are not permitted.

Level 5
Security level set to 256 bits of security. As a result RSA, DSA and DH keys shorter than 15360 bits and ECC keys shorter than 512 bits are prohibited.
</pre>
 
아마도 SQL Server 측의 TLS 1.0 이벤트 오류는 Level 2 이상의 설정에서는 금지된 cipher suites가 선택되기 때문에 나타나는 것으로 짐작할 수 있습니다. 
 
<hr style='width: 50%' /> 
 
재미있는 건, 역시 동일한 소스 코드를 WSL + Ubuntu 22.04에서 실행해 보면 이번엔 다음과 같은 오류가 발생합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
 ---&gt; System.Security.Authentication.AuthenticationException: Authentication failed, see inner exception.
 ---&gt; Interop+OpenSsl+SslException: SSL Handshake failed with OpenSSL error - SSL_ERROR_SSL.
 ---&gt; Interop+Crypto+OpenSslCryptographicException: error:0A000102:SSL routines::unsupported protocol
 --- End of inner exception stack trace ---
 at Interop.OpenSsl.DoSslHandshake(SafeSslHandle context, ReadOnlySpan`1 input, Byte[]&amp; sendBuf, Int32&amp; sendCount)
 at System.Net.Security.SslStreamPal.HandshakeInternal(SafeDeleteSslContext&amp; context, ReadOnlySpan`1 inputBuffer, Byte[]&amp; outputBuffer, SslAuthenticationOptions sslAuthenticationOptions, SelectClientCertificate clientCertificateSelectionCallback)
 --- End of inner exception stack trace ---
 at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](Boolean receiveFirst, Byte[] reAuthenticationData, CancellationToken cancellationToken)
...[생략]...
</pre>
 
오류 메시지가 살짝 바뀌었는데요, Ubuntu 22.04의 경우에는 SECLEVEL을 0으로 해줘야 합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
openssl_conf = openssl_init

// ...[생략]...

[openssl_init]
providers = provider_sect
ssl_conf = ssl_sect

// ...[생략]...

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = DEFAULT:@SECLEVEL=0
</pre>
 
이런 차이점은 OpenSSL 버전에 따른 것으로 보입니다. Ubuntu 20.04에 설치된 openssl은, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
// Ubuntu 20.04

$ openssl version -a
OpenSSL 1.1.1f 31 Mar 2020
built on: Mon Apr 17 18:11:39 2023 UTC
platform: debian-amd64
options: bn(64,64) rc4(8x,int) des(int) blowfish(ptr)
compiler: gcc -fPIC -pthread -m64 -Wa,--noexecstack -Wall -Wa,--noexecstack -g -O2 -fdebug-prefix-map=/build/openssl-eF9088/openssl-1.1.1f=. -fstack-protector-strong -Wformat -Werror=format-security -DOPENSSL_TLS_SECURITY_LEVEL=2 -DOPENSSL_USE_NODELETE -DL_ENDIAN -DOPENSSL_PIC -DOPENSSL_CPUID_OBJ -DOPENSSL_IA32_SSE2 -DOPENSSL_BN_ASM_MONT -DOPENSSL_BN_ASM_MONT5 -DOPENSSL_BN_ASM_GF2m -DSHA1_ASM -DSHA256_ASM -DSHA512_ASM -DKECCAK1600_ASM -DRC4_ASM -DMD5_ASM -DAESNI_ASM -DVPAES_ASM -DGHASH_ASM -DECP_NISTZ256_ASM -DX25519_ASM -DPOLY1305_ASM -DNDEBUG -Wdate-time -D_FORTIFY_SOURCE=2
OPENSSLDIR: "/usr/lib/ssl"
ENGINESDIR: "/usr/lib/x86_64-linux-gnu/engines-1.1"
Seeding source: os-specific
</pre>
 
1.1.1f 버전인 반면 Ubuntu 22.04는 3.0으로 바뀌었습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
// Ubuntu 22.04

$ openssl version -a
OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022)
built on: Mon Apr 17 18:12:58 2023 UTC
platform: debian-amd64
options: bn(64,64)
compiler: gcc -fPIC -pthread -m64 -Wa,--noexecstack -Wall -Wa,--noexecstack -g -O2 -ffile-prefix-map=/build/openssl-M0kolw/openssl-3.0.2=. -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -DOPENSSL_TLS_SECURITY_LEVEL=2 -DOPENSSL_USE_NODELETE -DL_ENDIAN -DOPENSSL_PIC -DOPENSSL_BUILDING_OPENSSL -DNDEBUG -Wdate-time -D_FORTIFY_SOURCE=2
OPENSSLDIR: "/usr/lib/ssl"
ENGINESDIR: "/usr/lib/x86_64-linux-gnu/engines-3"
MODULESDIR: "/usr/lib/x86_64-linux-gnu/ossl-modules"
Seeding source: os-specific
CPUINFO: OPENSSL_ia32cap=0xfed83203078bffff:0x400004219c01a9
</pre>
 
문서에 보면, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
OpenSSL 3.0 Series Release Notes
; <a target='tab' href='https://www.openssl.org/news/openssl-3.0-notes.html'>https://www.openssl.org/news/openssl-3.0-notes.html</a>
</pre>
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
SSL 3, TLS 1.0, TLS 1.1, and DTLS 1.0 only work at security level 0, except when RSA key exchange without SHA1 is used.
</pre>
 
TLS 1.0/1.1이 SECLEVEL=0에서만 동작하도록 바뀐 탓에 그런 것 같습니다. 
 
<hr style='width: 50%' /> 
 
그나저나, 초기에는 SQL 서버와의 통신이 TLS 1.2를 지원하지 않았지만, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
System.Data.SqlClient는 SSL 3.0/TLS 1.0만 지원하는 듯!
; <a target='tab' href='https://www.sysnet.pe.kr/2/0/1833'>https://www.sysnet.pe.kr/2/0/1833</a>

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>
 
이후의 문서를 보면 분명히 지원이 추가된 것으로 나옵니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
TLS 1.2 Support for SQL Server 2008, 2008 R2, 2012 and 2014
; <a target='tab' href='https://techcommunity.microsoft.com/t5/sql-server-blog/tls-1-2-support-for-sql-server-2008-2008-r2-2012-and-2014/ba-p/384613'>https://techcommunity.microsoft.com/t5/sql-server-blog/tls-1-2-support-for-sql-server-2008-2008-r2-2012-and-2014/ba-p/384613</a>
</pre>
 
하지만, Ubuntu뿐만 아니라 Windows 클라이언트의 Microsoft.Data.SqlClient도 "Security Warning: The negotiated TLS 1.0 is an insecure protocol and is supported for backward compatibility only. The recommended protocol version is TLS 1.2 and later."라는 경고를 띄우는 걸로 봐서는 TLS 1.2 통신이 아닌 1.0으로 되고 있습니다. 
 
검색해 보면, SCHANNEL의 레지스트리 설정이 있긴 하지만, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
KB3135244 - TLS 1.2 support for Microsoft SQL Server
; <a target='tab' href='https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe'>https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe</a>
</pre>
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2] 
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001 
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001 
</pre>
 
위의 설정을 하고 재부팅해도 효과는 없었습니다. 딱히 더 해볼 것이 없군요. ^^ 
 
<hr style='width: 50%' /> 
 
참고로, 이러한 변화는 .NET 5부터 바뀐 정책 때문이라고 합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
SqlClient troubleshooting guide
; <a target='tab' href='https://learn.microsoft.com/en-us/sql/connect/ado-net/sqlclient-troubleshooting-guide?view=sql-server-ver16'>https://learn.microsoft.com/en-us/sql/connect/ado-net/sqlclient-troubleshooting-guide?view=sql-server-ver16</a>

Default TLS cipher suites for .NET on Linux
; <a target='tab' href='https://learn.microsoft.com/en-us/dotnet/core/compatibility/cryptography/5.0/default-cipher-suites-for-tls-on-linux'>https://learn.microsoft.com/en-us/dotnet/core/compatibility/cryptography/5.0/default-cipher-suites-for-tls-on-linux</a>
</pre>
 
<div style='BACKGROUND-COLOR: #ccffcc; padding: 10px 10px 5px 10px; MARGIN: 0px 10px 10px 10px; FONT-FAMILY: Malgun Gothic, Consolas, Verdana; COLOR: #005555'>
.NET 5 introduced a breaking change for Linux clients, where a tightly restricted list of permitted cipher suites is used by default. You may need to expand the default cipher suite list to accept legacy clients (or to contact legacy servers) by either specify a CipherSuitePolicy value or changing the OpenSSL configuration file. 
</div>

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

</div>

첨부파일

스팸 방지용 인증 번호

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