In my previous post, I discussed the following error, and how to resolve it.

The client and server cannot communicate, because they do not possess a common algorithm

In this post, I’m going to answer some common questions about the provider you are using to connect to SQL Server, and which updates you should apply on the client side. For example, what driver are you using when you connect with SQL Server Managerment Studio (SSMS), or Reporting Services?

SSMS

SSMS is a .NET application, and depending on the version, you are either using ADO.NET 2 or ADO.NET 4 to connect to SQL Server. This is part of the System.Data.dll assembly, and upgrading these components is the first step to get your client connected to a SQL Server system that only accepts TLS 1.2 or TLS 1.1. So how do you know which version to upgrade? I took BID Traces to prove it to you:

SSMS 2008 R2 and below

02:CONNECTED [64D70000]C:\Windows\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll “System.Data.1” {914ABDE2-171E-C600-3348-C514171DE148}

1#, ‘Data Source=van-sql;Integrated Security=True;Pooling=False;MultipleActiveResultSets=False;Packet Size=4096;Application Name=”Microsoft SQL Server Management Studio”‘

SSMS 2012/2014

02:CONNECTED [5BD20000]C:\Windows\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll “System.Data.1” {914ABDE2-171E-C600-3348-C514171DE148}

1#, ‘Data Source=VAN-SQL;Integrated Security=True;Pooling=False;MultipleActiveResultSets=False;Packet Size=4096;Application Name=”Microsoft SQL Server Management Studio”‘


System.Data.dll version 2 is packaged with .NET Framework 3.5 and below, and version 4 is packaged with .NET Framework 4.x. Find the version of your SSMS, and as long as you upgrade your current .NET Framework for your system, you should now be able to connect to SQL Server on the client side. You will still need the appropriate updates to SQL Server to support TLS 1.2.

https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server

If you are wondering what Reporting Services uses, it’s also a .NET application. The difference however is that Reporting Services 2014 and below is built off of .NET Framework 3.5, and hence uses ADO.NET 2.0.

SSRS 2014 and below

16#, ‘Data Source=VAN-SQL;Initial Catalog=ReportServer_2014;Integrated Security=True;Max Pool Size=1536;Application Name=”Report Server”‘

04:CONNECTED [000000006D810000]C:\Windows\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll “System.Data.1” {914ABDE2-171E-C600-3348-C514171DE148}


Additionally for Reporting Services to support TLS 1.2, you must install the additional fixes for .NET 3.5 in KB 3135244:



What if you have a 3rd party application, and you really don’t know what you are using to connect to SQL Server? I would probably ask the application vendor. However, if you are unsure, you might be able to do some investigation on your own. For example, if your application is a .NET application, chances are that you are using the .NET drivers. You’ll want to check your application logs to see what drivers/providers it’s calling when it’s failing to connect.

What if your application is using a ODBC System DSN to connect? You can find out the version by going to the ODBC Data Source Administrator (32bit or 64bit), and checking out the configuration:



The System DSN will give you the driver that is being used, and the Drivers tab will show you what drivers are installed. The driver called “SQL Server” or SQLSRV32.DLL is the driver that is installed with Windows, and is deprecated. There’s no updates to this driver that will support TLS 1.2. SQL Native Client (SNAC) 11.0 is installed with SQL Server 2012/2014, and SNAC 10.0 is installed with SQL Server 2008/2008 R2. These are the SQLNCLI11.DLL and SQLNCLI10.DLL assemblies, and depending on what your application is calling, you’ll want to update these drivers to support TLS 1.2.

You’ll also notice the version number in the Drivers screenshot. Make note of the last 4 digits of the build for those drivers, as you’ll want to ensure that the driver version is updated to a build that supports TLS 1.2. You can reference the following blog for your build:

http://sqlserverbuilds.blogspot.com/

If you are wondering how you can extract the SQL Native Client from a certain build, Adam Saxton has a great blog and video to explain this:

https://techcommunity.microsoft.com/t5/SQL-Server-Support/Getting-the-latest-SQL-Server-Native-Client/ba-p/318605

Hopefully, that clears up some confusion and gets you on your way to be PCI Compliant.