Question:
Why do we receive an error message "SSL Security Error" while connecting to the SQL database after enabling TLS 1.2 and disabling TLS 1.0 and TLS 1.1 in the server machine?
Please refer attached screenshot "SSL Security Error.JPG" for more details.
Answer:
SQL Server OLE DB provider does not support TLS 1.2 so AdminStudio will not be able to connect to a SQL server in a TLS 1.2 only environment.
Adminstudio started supporting TLS 1.2 from the 2018 R3 version and above.
So if you are using the Adminstudio version less than 2018R3 and below, you will not be able to connect to the SQL server where TLS 1.2 is enabled.
However, it connects successfully to the SQL server if TLS 1.0 and TLS 1.1 are enabled.
Navigate to the following path in the SQL server machine and modify the value accordingly:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2
Please refer attached screenshot "TLS Registry path.JPG" for more details.
We can set programmatically TLS 1.2 Client and Server using the following PowerShell script attached
$protocols = @{
'SSL 2.0'= @{
'Server-Enabled' = $false
'Client-Enabled' = $false
}
'SSL 3.0'= @{
'Server-Enabled' = $false
'Client-Enabled' = $false
}
'TLS 1.0'= @{
'Server-Enabled' = $false
'Client-Enabled' = $false
}
'TLS 1.1'= @{
'Server-Enabled' = $false
'Client-Enabled' = $false
}
'TLS 1.2'= @{
'Server-Enabled' = $true
'Client-Enabled' = $true
}
}
$protocols.Keys | ForEach-Object {
Write-Output "Configuring '$_'"
# create registry entries if they don't exist
$rootPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\$_"
if(-not (Test-Path $rootPath)) {
New-Item $rootPath
}
$serverPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\$_\Server"
if(-not (Test-Path $serverPath)) {
New-Item $serverPath
New-ItemProperty -Path $serverPath -Name 'Enabled' -Value '1' -PropertyType 'DWord'
New-ItemProperty -Path $serverPath -Name 'DisabledByDefault' -Value '0' -PropertyType 'DWord'
}
$clientPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\$_\Client"
if(-not (Test-Path $clientPath)) {
New-Item $clientPath
New-ItemProperty -Path $clientPath -Name 'Enabled' -Value '1' -PropertyType 'DWord'
New-ItemProperty -Path $clientPath -Name 'DisabledByDefault' -Value '0' -PropertyType 'DWord'
}
# set server settings
if($protocols[$_]['Server-Enabled']) {
Set-ItemProperty -Path $serverPath -Name 'Enabled' -Value '1'
Set-ItemProperty -Path $serverPath -Name 'DisabledByDefault' -Value '0'
} else {
Set-ItemProperty -Path $serverPath -Name 'Enabled' -Value '0'
Set-ItemProperty -Path $serverPath -Name 'DisabledByDefault' -Value '1'
}
# set client settings
if($protocols[$_]['Client-Enabled']) {
Set-ItemProperty -Path $clientPath -Name 'Enabled' -Value '1'
Set-ItemProperty -Path $clientPath -Name 'DisabledByDefault' -Value '0'
} else {
Set-ItemProperty -Path $clientPath -Name 'Enabled' -Value '0'
Set-ItemProperty -Path $clientPath -Name 'DisabledByDefault' -Value '1'
}
}
on Jul 31, 2020 04:21 AM - edited on Oct 12, 2020 12:28 AM by sshivaraj