What is TLS & How to check and enable TLS 1.2 on various versions of MS SQL Server DB SERVERS?
What is TLS?
Transport Layer Security (TLS) is a protocol that provides privacy and data integrity between two communicating applications. It's the most widely deployed security protocol used today and is used for Web browsers and other applications that require data to be securely exchanged over a network, such as file transfers, VPN connections, instant messaging and voice over IP.
What is TLS 1.2?
TLS 1.2 is an advance version of TLS 1.0 & TLS 1.1 helps in provides privacy and data integrity between two communicating applications
Method to check which version of TLS Enabled on the server
The File Name is IISCrypto.exe
Copy file to any Folder
Execute IISCrypto.exe
You will find output like below screen, which indicates TLS 1.0 and TLS 1.1 is enabled.
How to Enable TLS 1.2:-
1. Run IISCrypto.exe
2. Click on Protocols
3. Untick the following:: "TLS 1.0,TLS 1.1"
4. Tick the following:: "Multi-Protocol Unified Hello" and TLS 1.2
5. Untick Triple DES 168
5. Untick Triple DES 168
6. Click Apply and choose OK to the RDP warning
7. Restart the server
8. Test RDP access using NA-MSMPS credentials and ensure access is OK.
Question) in case After Enable of TLS 1.2 SQL Server Engine not starting what you will do?
Ans:- Check SQL Server Errorlog & EventViewer, in most cases, you will get below SSL connection error in Application
Error on SQL Server Error log
--------------------------------------------Log Name: Application
Source: MSSQL$UC1DBTSJR17FC
Date: 7/8/2017 11:59:09 AM
Event ID: 26010
Task Category: Server
Level: Information
Keywords: Classic
User: N/A
Computer: UC1DBTSJR17F01.JRLAB.LOCAL
Description:
The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x80090331. Check certificates to make sure they are valid.
Log Name: Application
Source: MSSQL$UC1DBTSJR17FCDate: 7/8/2017 11:59:09 AM
Event ID: 26017
Task Category: Server
Level: Information
Keywords: Classic
User: N/A
Computer: UC1DBTSJR17F01.JRLAB.LOCAL
Description:
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Log Name: Application
Source: MSSQL$UC1DBTSJR17FC
Date: 7/8/2017 11:59:09 AM
Event ID: 17182
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: UC1DBTSJR17F01.JRLAB.LOCAL
Description:
TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.
àAll Error indicates that the server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x80090331. Check certificates to make sure they are valid.
TLS 1.2 Works on Builds that are later than those listed in this table also support TLS 1.2.
SQL Server release | First build that supports TLS 1.2 | Download link for earlier builds | Additional information |
SQL Server 2014 SP1 | 12.0.4439.1 | KB 3052404 FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014 or SQL Server 2012 | |
SQL Server 2014 SP1 GDR | 12.0.4219.0 | ||
SQL Server 2014 RTM | 12.0.2564.0 | KB 3052404 FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014 or SQL Server 2012 | |
SQL Server 2014 RTM GDR | 12.0.2271.0 | ||
SQL Server 2012 SP3 GDR | 11.0.6216.27 | ||
SQL Server 2012 SP3 | 11.0.6518.0 | KB 3052404 FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014 or SQL Server 2012 | |
SQL Server 2012 SP2 GDR | 11.0.5352.0 | ||
SQL Server 2012 SP2 | 11.0.5644.2 | KB 3052404 FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014 or SQL Server 2012 | |
SQL Server 2008 R2 SP3 | 10.50.6542.0 | ||
SQL Server 2008 R2 SP2 GDR (IA-64 only) | 10.50.4047.0 | ||
SQL Server 2008 R2 SP2 (IA-64 only) | 10.50.4344.0 | ||
SQL Server 2008 SP4 | 10.0.6547.0 | ||
SQL Server 2008 SP3 GDR (IA-64 only) | 10.0.5545.0 | ||
SQL Server 2008 SP3 (IA-64 only) | 10.0.5896.0 |
Check Database Engine and decide next course of action..
1. Take Approval from customer and update the engine or revert the changes.
Steps to revert the changes:-
1. Run IISCrypto.exe
2. Click on protocols
3. Tick the following:: "TLS 1.0, TLS 1.1 and Untick TLS 1.2”
4. Untick the following:: "Multi-Protocol Unified Hello"
5. Tick Triple DES 168
6. Click Apply and choose OK to the RDP warning
7. Restart the server
8. Test RDP access using your credentials and ensure access is OK.
Question:- After Enable of TLS 1.2 Database engine starts and Cluster failover or AG Failover work successfully but using SSMS you are not able to connect to Database server.. what will you do?
Ans:-
1. Database client component should pass as per PowerShell script specified below and database engine version as per KB- KB3135244 (https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server)
2. Copy PowerShell script from https://github.com/Microsoft/tigertoolbox/blob/master/tls1.2/CheckClientUpdates.ps1
3. Execute Powershell script from Powershell window.
Below Screenshot Indicate client components (Native Client & ODBC Driver 11 for SQL Server) are not ready for TLS 1.2
àTake Approval from Customer to upgrade Microsoft SQL Server 2012 native Client from 11.0.2100.60 to now 11.3.6538
You can use following KB Article à
https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server
Check Section Client component downloadshttps://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server
Client component downloads
Use the following table to download the client components and driver updates that are applicable to your environment.
Client component /driver | Update with TLS 1.2 support |
ADO.NET - SqlClient (.NET Framework 4.5.2, 4.5.1, 4.5) | Hotfix rollup 3099842 for the .NET Framework 4.5.2, 4.5.1, and 4.5 on Windows 8.1 and Windows Server 2012 R2 Hotfix rollup 3099844 for the .NET Framework 4.5.2, 4.5.1, and 4.5 on Windows 8 and Windows Server 2012 Hotfix rollup 3099845 for the .NET Framework 4.5.2 and the .NET Framework 4.5.1 on Windows 7 Service Pack 1/ Windows Vista and Windows Server 2008 R2/Windows Server 2008 |
ADO.NET - SqlClient (.NET Framework 4.0) | |
ADO.NET - SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2) | |
SQL Server Native Client (for SQL Server 2008 R2) | |
SQL Server Native Client (for SQL Server 2008 R2) | |
SQL Server Native Client (for SQL Server 2008) | |
SQL Server Native Client (for SQL Server 2008) | |
SQL Server Native Client (for SQL Server 2012 and SQL Server 2014) | |
Microsoft ODBC Driver for SQL Server | |
JDBC 6.0 | |
JDBC 4.1 and JDBC 4.2 |
On one of my lab server Microsoft SQL Server 2012 native Client was on 11.0.2100.60
The Microsoft SQL Server 2012 Native client & Microsoft ODBC Driver needs an update.
In order to resolve this issue, I downloaded a native client from below link and upgrade the server
Now native client version is 11.3.6538
Checked Native client version again using PowerShell again:-
->It says now 11.3.6538 will supports TLS 1.2, here we have not updated Microsoft ODBC Driver 11 for SQL Server with Version 12.1.4100.1, hence it is pointing us in red color, this can also be done if the client uses ODBC drivers for SQL Server connection.
After native client update, SSMS will able to connect database engine.
Question:- Points to look while Writing DBA MOP for Windows registry change TLS 1.2
Ans:-
1. Database server:-
SQL Server 2014 SP1 should be on and above 12.0.4439.1
SQL Server 2014 SP1 GDR should be on and above 12.0.4219.0
SQL Server 2014 RTM should be on and above 12.0.2564.0
SQL Server 2014 RTM GDR should be on and above 12.0.2271.0
SQL Server 2012 SP3 GDR should be on and above 11.0.6216.27
SQL Server 2012 SP3 should be on and above 11.0.6518.0
SQL Server 2012 SP2 should be on and above GDR 11.0.5644.2
SQL Server 2012 SP2 GDR should be on and above 11.0.5352.0
SQL Server 2008 R2 SP3 -- should be on and above 10.50.6542.0
SQL Server 2008 R2 SP2 GDR (IA-64 only) -- should be on and above 10.50.4344.0SQL Server 2008 SP4 -- should be on and above 10.0.6547.0
SQL Server 2008 SP3 GDR (IA-64 only) -- should be on and above 10.0.5545.0
SQL Server 2008 SP3 (IA-64 only) -- should be on and above 10.0.5896.0
2. Client components:-
Do not confuse with .netframework versions and all just
1.Copy PowerShell script from https://github.com/Microsoft/tigertoolbox/blob/master/tls1.2/CheckClientUpdates.ps1. 2.Execute PowerShell script from powershell window.
3. if You will get green output
SQL Server engine as per above, then every thing is ok.
 |
While working with a customer, the customer can ask other questions like
Question: - We have doubt as for when we update database server from one service pack to another service pack do Native client components also gets update
Answer-No. Native Clients and ODBC drives only get updates when the first time we install SQL Server..
Following products depends on SQL Server 2012 Native Client:-
Following products depends on ODBC DRIVER 11 for SQL SERVER
Question:- what happens when we uninstall SQL native Clients & ODBC drivers from NodeY and try to failover database server from NodeX to NodeY, will DB engine come online on NodeY
Ans:- When you try to connect the database server you will get below error..
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. (Microsoft SQL Server, Error: 0)
And in Cluster Log you will get error like this:-
Line 2: 00000f04.00000de4::2017/07/14-15:16:20.437 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 13: 00000f04.00000de4::2017/07/14-15:16:25.438 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 41: 00000f04.00000de4::2017/07/14-15:16:40.442 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 41: 00000f04.00000de4::2017/07/14-15:16:40.442 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 45: 00000f04.00000de4::2017/07/14-15:16:45.443 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 55: 00000f04.00000de4::2017/07/14-15:16:50.444 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 68: 00000f04.00000de4::2017/07/14-15:16:55.446 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) Line 71: 00000f04.00000de4::2017/07/14-15:16:55.446 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 71: 00000f04.00000de4::2017/07/14-15:16:55.446 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 75: 00000f04.00000de4::2017/07/14-15:17:00.447 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 75: 00000f04.00000de4::2017/07/14-15:17:00.447 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 86: 00000f04.00000de4::2017/07/14-15:17:05.449 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 86: 00000f04.00000de4::2017/07/14-15:17:05.449 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 93: 00000f04.00000de4::2017/07/14-15:17:10.450 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 101: 00000f04.00000de4::2017/07/14-15:17:15.450 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 104: 00000f04.00000de4::2017/07/14-15:17:15.450 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 104: 00000f04.00000de4::2017/07/14-15:17:15.450 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 113: 00000f04.00000de4::2017/07/14-15:17:20.453 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 113: 00000f04.00000de4::2017/07/14-15:17:20.453 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 121: 00000f04.00000de4::2017/07/14-15:17:25.454 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 124: 00000f04.00000de4::2017/07/14-15:17:25.454 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 124: 00000f04.00000de4::2017/07/14-15:17:25.454 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 128: 00000f04.00000de4::2017/07/14-15:17:30.456 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 128: 00000f04.00000de4::2017/07/14-15:17:30.456 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 131: 00000f04.00000de4::2017/07/14-15:17:30.456 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 131: 00000f04.00000de4::2017/07/14-15:17:30.456 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 138: 00000f04.00000de4::2017/07/14-15:17:35.457 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 138: 00000f04.00000de4::2017/07/14-15:17:35.457 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 141: 00000f04.00000de4::2017/07/14-15:17:35.457 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 141: 00000f04.00000de4::2017/07/14-15:17:35.457 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 146: 00000f04.00000de4::2017/07/14-15:17:40.458 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 146: 00000f04.00000de4::2017/07/14-15:17:40.458 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
Line 156: 00000f04.00000de4::2017/07/14-15:17:45.460 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 170: 00000f04.00000de4::2017/07/14-15:17:55.462 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
Line 171: 00000f04.00000f20::2017/07/14-15:17:58.856 ERR [RHS] RhsCall::DeadlockMonitor: Call ONLINERESOURCE timed out by 16 milliseconds for resource 'SQL Server'.
Line 177: 000004c8.00000ef4::2017/07/14-15:17:58.856 ERR [RCM] rcm::RcmResource::HandleFailure: (SQL Server)
Line 197: 00000f04.00000de4::2017/07/14-15:17:58.859 INFO [RES] SQL Server <SQL Server>: [sqsrvres] SQL Server resource state is changed from 'ClusterResourceOnlinePending' to 'ClusterResourceFailed'
Line 197: 00000f04.00000de4::2017/07/14-15:17:58.859 INFO [RES] SQL Server <SQL Server>: [sqsrvres] SQL Server resource state is changed from 'ClusterResourceOnlinePending' to 'ClusterResourceFailed'
Line 205: 00000f04.00000f20::2017/07/14-15:17:58.877 ERR [RHS] WER report is submitted. Result : WerReportQueued.
Line 573: 000004c8.000013a4::2017/07/14-15:27:52.112 INFO [DCM] HandleSweeperRecheck
Line 609: 000004c8.00001334::2017/07/14-15:28:52.145 ERR [RCM] [GIM] ResType Virtual Machine has no resources, not collecting local utilization info
Line 679: 000004c8.000013a4::2017/07/14-15:30:52.116 INFO [DCM] HandleSweeperRecheck
è And SQL Server services in cluster will goes offline, as cluster.exe uses ODBC driver to connect to the database server engine.
When we check whether ODBC drivers installed on Node Y, we get to know that there is no ODBC drivers installed on NODE - Y
Let’s Install ODBC drivers on NodeY and see are we able to failover SQL Server Back to NodeY.
Here we Selected SQL Client Connectivity SDK
An only Native client will get downloaded.
Then we have to download ODBC drivers from the siteAnd found the new Version of ODBC drivers are compatible for TLS 1.2, but still I am not able to failover database engine back to NodeY.
Tried to reboot the server and check whether are we able to connect to the server, still, we are not able to connect, Checked the reason why are we still not able to connect to database server…
Generate ClusterLog again.
00000f3c.00001144::2017/07/14-16:45:46.490 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
00000e2c.0000090c::2017/07/14-16:45:46.642 INFO [RES] Physical Disk <Demo>: VolumeIsNtfs: Volume \\?\GLOBALROOT\Device\Harddisk4\ClusterPartition1\ has FS type NTFS
00000e2c.0000090c::2017/07/14-16:45:46.645 INFO [RES] Physical Disk <Backups>: VolumeIsNtfs: Volume \\?\GLOBALROOT\Device\Harddisk3\ClusterPartition1\ has FS type NTFS
00000e2c.00000cbc::2017/07/14-16:45:46.649 INFO [RES] Physical Disk <TLogs>: VolumeIsNtfs: Volume \\?\GLOBALROOT\Device\Harddisk5\ClusterPartition1\ has FS type NTFS
00000e2c.0000090c::2017/07/14-16:45:46.649 INFO [RES] Physical Disk <Data>: VolumeIsNtfs: Volume \\?\GLOBALROOT\Device\Harddisk7\ClusterPartition1\ has FS type NTFS
00000e6c.0000092c::2017/07/14-16:45:50.728 INFO [RES] Network Name: Agent: Sending request Netname/RecheckConfig to NN:0d8c4d89-c7c9-4390-9d1f-ac432565ffca:Netbios
00000f3c.00001144::2017/07/14-16:45:51.490 INFO [RES] SQL Server <SQL Server>: [sqsrvres] Connect to SQL Server ...
00000f3c.00001144::2017/07/14-16:45:51.491 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)
00000f3c.00001144::2017/07/14-16:45:51.491 INFO [RES] SQL Server <SQL Server>: [sqsrvres] Could not connect to SQL Server (rc -1)
Resolution:-
I found on Node-Y, the Clients protocol was Blank,
I installed Microsoft SQL Server 2012 Native Client {sqlncli} à 11.3.6538
After installation, I refreshed the SQL Server Configuration Manager and found Client protocols started showing
1. Shared Memory
2. TCP/IP
3. Named Pipes
Then I failed over database services to NodeY and services came online on 2nd node without any fail.
Conclusion:-
All TLS 1.2 related information is present in this link,