Moving SQL Server connections to TLS 1.2 is not solely (or even mostly) a SQL server change. We need to get all application/vendor developers in the loop to make the transition to TLS 1.2, apply a lot of .NET version-specific patches, and more.
Disabling TLS 1.0 and 1.1 on the Windows Server that runs the SQL instance is definitely something a lot of security-sensitive folks are wanting to do (what's TLS anyway?), but they’re often hamstrung by the applications connecting to the SQL server, or by features inside SQL Server itself that have been configured to use legacy algorithms or version settings.
Contrary to some opinion out there, connections will not use the lowest common denominator allowed by the server and the application's client. Connections will use TLS 1.2 if possible. This is usually a limitation of the application connectivity client or .NET framework version. But if you want to prevent (and therefore break/expose) connections from using TLS 1.0 or TLS 1.1, you need to disable TLS 1.0 and 1.1. This is the only way to make sure you're sniffing out the insecure connections.
We definitely need to test this out in pre-production before any production changes. The list of things needed to get onboard with TLS 1.2 could be lengthy, but it’s a worthwhile endeavor. An initial checklist to consider:
- If using SQL Server prior to 2016, patch SQL Server. Info here.
- We need to make sure that the latest version of the SQL Server Native Client is configured on the SQL server itself, and also all client machines that connect to SQL, per version. See the "Client component downloads" section in this same link.
- Any clients that use the .NET framework and ADO.NET connectors will need to get up to .NET Framework 4.6 to use TLS 1.2. See the "Additional fixes needed for SQL Server to use TLS 1.2" in this same link as above. There are patches for other connectivity platforms like ODBC and JDBC as well that are needed for both the client and servers.
- The clients/webservers/appservers, and the SQL Server will ALL need these .NET patches. There are patches needed for frameworks starting with .NET Framework 3.5 for TLS 1.2, again see link above.
- For example, SQL Server Database Mail still uses .NET Framework 3.5 SP1, which needs a specific patch to allow TLS 1.2. See above link for OS-specific links.
- You need to change registry keys on the Windows Server.
- On operating systems prior to Windows 8/Server 2012, to enable TLS 1.2 you need to add keys, and modify existing keys to disable TLS 1.0 and 1.1.
- Starting with Windows 8 and later and Windows Server 2012 and later, TLS 1.2 is already enabled, and you need to add registry keys to disable TLS 1.0 and 1.1.
- Keys here: https://technet.microsoft.com/en-us/library/dn786418%28v=ws.11%29.aspx?f=255&MSPPError=-2147217396#BKMK_SchannelTR_TLS10
- Or, you can use a free tool like Nartac IIS Crypto to manage the registry edits for you.
- Note that a reboot is required to make the registry changes take effect.
- Anything in SQL Server that is encrypted using MD5 algorithms should probably be changed anyway (certificates, database keys, and endpoints for example) but it’s definitely going to be required for TLS 1.2. https://support.microsoft.com/en-us/help/3137281/fix-communication-that-uses-an-md5-hash-algorithm-fails-when-you-use-t In fact, starting with SQL Server 2016, all algorithms other than AES_128, AES_192, and AES_256 are deprecated.
- SQLOLEDB will not receive support for TLS 1.2, so some connections using the OLEDB driver (as opposed to ODBC, Native Client, or ADO.NET) will need to be rewritten.
- The 'Microsoft.ACE.OLEDB.12.0' provider continues to work, apparently.
- Linked server connections using @provider='SQLOLEDB' will continue to work, because this actually uses the 'SQLNCLI' provider, the SQL Native Client.
- For SSRS, we also need to make sure SSRS is using only HTTPS. Again the above .NET framework components need to be updated on the SSRS server and application servers. https://blogs.msdn.microsoft.com/dataaccesstechnologies/2016/07/12/enable-tls-1-2-protocol-for-reporting-services-with-custom-net-application/.
How can you tell what version of TLS is currently used for client connections? See my companion blog post on this topic.