tls1.0未正常启用导致sqlserver无法启动的处理过程记录

一套Mirror的备库突然无法启动

SQLServer无法启动，error log报以下错误

08:39:05.20 spid13s     The server was unable to initialize encryption because of a problem with a security library. The security library may be missing. Verify that security.dll exists on the system.
2024-04-18 08:39:05.20 spid13s     Error: 17182, Severity: 16, State: 1.
2024-04-18 08:39:05.20 spid13s     TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL support. The group or resource is not in the correct state to perform the requested operation. 
2024-04-18 08:39:05.20 spid13s     Error: 17182, Severity: 16, State: 1.
2024-04-18 08:39:05.20 spid13s     TDSSNIClient initialization failed with error 0x139f, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The group or resource is not in the correct state to perform the requested operation. 
2024-04-18 08:39:05.20 spid13s     Error: 17826, Severity: 18, State: 3.
2024-04-18 08:39:05.20 spid13s     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.
2024-04-18 08:39:05.20 spid13s     Error: 17120, Severity: 16, State: 1.
2024-04-18 08:39:05.20 spid13s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

windows log报以下错误：

he SQL Server (MSSQLSERVER) service terminated with the following service-specific error: 
The group or resource is not in the correct state to perform the requested operation.
A fatal error occurred while creating a TLS client credential. The internal error state is

客户端连接主库时，

TITLE: Connect to Server
------------------------------

Cannot connect to 10.134.171.206,3000.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.) (Microsoft SQL Server, Error: -2146893007)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2146893007&LinkId=20476

------------------------------

The client and server cannot communicate, because they do not possess a common algorithm

------------------------------
BUTTONS:

OK
------------------------------

检查OS的完整性，结果未发现异常

C:\Windows\system32>sfc /scannow

Beginning system scan.  This process will take some time.

Beginning verification phase of system scan.
Verification 100% complete.

Windows Resource Protection did not find any integrity violations.

互联网上查询到以下文章

https://blog.toadworld.com/2017/03/14/sql-server-errorlog-the-server-was-unable-to-initialize-encryption-because-of-a-problem-with-a-security-library

参考后，按如下方式处理：

先检查以下注册表值
I checked below key based on the article

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocols

There were no keys for TLS and it means that TLS was not enabled in the server. Either TLS1.0 or SSL3.0 needs to be enabled to start SQL services.


Windows Registry Editor Version 5.00
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0]

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Client]
 “Enabled”=dword:00000000
 “DisabledByDefault”=dword:00000001

[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Server]
 “Enabled”=dword:00000000
 “DisabledByDefault”=dword:00000001 

现有方式禁用了TLS1.0，做如下修改

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Client]
 “Enabled”=dword:ffffffff
 “DisabledByDefault”=dword:00000000

[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Server]
 “Enabled”=dword:ffffffff
 “DisabledByDefault”=dword:00000000

Once we modified the keys, we could start SQL Services.