Force Strict Encryption - SQL Agent fails
-
Hi,
On my SQL 2022 (16.0.4195.2) test environment, I tried to enable Force Strict Encryption.
To do this:
- I generated a certificate using ps script :
$newCertificate = New-SelfSignedCertificate -CertStoreLocation cert:\LocalMachine\My -DnsName "DEV-SERVER.domain.local","DEV-SERVER" -FriendlyName "Cert_Test" -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1"
# Convert the report server password to a secure string.
$secureStringPassword = ConvertTo-SecureString "<..some_pwd..>" -AsPlainText -Force
# Set up a variable for the path of a certificate file.
$folderPath = "C:\Temp"
$certificateFilePath = $folderPath, "\certificate-export.pfx" -join ""
# Set up a variable for the path of the certificate's store location.
$certificateStoreLocation = "cert:\LocalMachine\My\", $newCertificate.Thumbprint -join ""
# Export the certificate to the file.
Export-PFXCertificate -Cert $certificateStoreLocation -File $certificateFilePath -Password $secureStringPassword
# Import the certificate from the file to the trusted root to avoid problems with the certificate not being trusted.
Import-PfxCertificate -FilePath $certificateFilePath cert:\LocalMachine\Root -Password $secureStringPassword- Enabled TLS 1.2 and TLS 1.3,
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client Enabled = 1
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server Enabled = 1
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.3\Client Enabled = 1
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.3\Server Enabled = 1-In the protocols, I selected the certificate and set Force Strict Encryption to Yes.
The SQL engine is working correctly,
I can connect to SQL using SSMS — but the logs under "Management -> SQL Server Logs" are not available :
and the worst thing is that SQL Agent is not working.
When attempting to start it, the following messages appears in SQL ERROR log:
The SQL Server or the endpoint is configured to accept only strict (TDS 8.0 and above) connections. The connection has been closed.
In SQLAGENT.OUT :
[150] SQL Server does not accept the connection (error: 233). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
[000] Unable to connect to server 'DEV-SERVER'; SQLServerAgent cannot start
[298] SQLServer Error: 26, Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server. [SQLSTATE 08001]
[298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08001]Any idea or tip what can be wrong ?
-
Strict Encryption isn't supported with SQL Server Agent.
Within the documentation page on TLS 8.0:
The following features or tools still use previous version of drivers that don't support TDS 8.0, and as such, might not work with the strict connection encryption:
Always On availability groups
Always On failover cluster instance (FCI)
SQL Server Replication
Log Shipping
SQL Server CEIP service
SQL Server Agent
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply