Issue connecting to SQL database with a warning message "SSL Security Error" after enabling TLS 1.2 and disabling TLS 1.0 and TLS 1.1 in the server machine.

Issue connecting to SQL database with a warning message "SSL Security Error" after enabling TLS 1.2 and disabling TLS 1.0 and TLS 1.1 in the server machine.

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.
SSL Security Error.JPG


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.
TLS Registry path.JPG

 

We can set programmatically TLS 1.2 Client and Server using the following PowerShell script attached

Powershell script to enable TLS 1.2 on Client and  ServerPowershell script to enable TLS 1.2 on Client and Server

 

$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'
    }
}


 

 

 

Was this article helpful? Yes No
No ratings
Version history
Last update:
‎Oct 12, 2020 12:28 AM
Updated by: