About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

What is TLS & How to enable TLS 1.2 on MS SQL Server Database Servers.

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

1.       Download  IIS Crypto 2.0 Tool  from link à  https://www.nartac.com/Products/IISCrypto

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 
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$UC1DBTSJR17FC
Date:          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)


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

Check Section Client component downloads
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)
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.0
SQL 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 site



And 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 Failover, We still get the same error.
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, 
Labels: , , , , , , , ,