Issue creating a connection to SQL server 2014 with ODBC data source and TLS activated

6
votes

I am trying to create / modify a system data source for my application which needs to connect to our Microsoft SQL Express 2014 database through an ODBC data source. The connection used to work before we enabled the SSL encryption flow but, now that we have an encrypted link, the connection does not work anymore.

Here are some details:

  • We run Microsoft SQL Server express 2014 SP1 with the latest cumulative update (CU #3) (which was necessary so that we could deactivate SSL 3.0 and TLS 1.0 and run only with TLS 1.1 and TLS 1.2)
  • The database instance is a named instance, configured to run with a TCP/IP connection and a fixed port.
  • We enabled the "Force Encryption" option and configured the certificate through the "Sql Server Configuration Manager"
  • We can successfully establish a connection to the database either using Management Studio or the SQL command line utility
  • We disabled all usage of SSL/TLS prior to version TLS 1.1 done through Microsoft Schannel. This required us to update SQL Express 2014 SP1 to the latest cumulative update (CU #3)
  • The ODBC data source we try to configure (through ODBC data source Administrator) is a "System DSN" and uses the "SQL server" driver and target the exact same server name as the one working in management studio (and whose server name corresponds to the machine FQDN which is reflected in the certificate CN value)
  • The Version indicated for the SQL Server ODBC driver is "Microsoft SQL Server ODBC Driver Version 06.03.9600"
  • I am testing the connection directly through the ODBC data source Administrator tool.
  • The first test I runs are run on the exact same machine as the one hosting the database server.

We get two consecutive error message in the Microsoft SQL Server Login:

Connection failed:
SQLState : '01000'
SQL Server Error: 771
[Microsoft][ODBC SQL Server Driver][TCP/IP  Sockets]ConnectionOpen(SECDoClientHandshake()).
Connection failed:
SQLState : '08001'
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security error.

I went through all the literature I could find but cannot find a solution.

EDIT 27/01/2016 16:30

Following erg suggestion, I tried to use the last MS SQL Server Native Client version (11.00.2100 on my system), but it still does not resolve the issue. I experience another issue though (whether or not I force the strong encryption on the client side results in the same error):

Running connectivity tests...

Attempting connection
[Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host.

[Microsoft][SQL Server Native Client 11.0]Client unable to establish connection

TESTS FAILED!
1
sql-serversslodbc
Can you try with the driver 'Microsoft SQL Server Native Client 11.0' - I think this is more recent than the one you are currently using (?) See: msdn.microsoft.com/de-de/library/ms131321%28v=sql.120%29.aspx - erg
I just edited the main entry to follow your suggestion (erg) but I fall against another error. - user2457451
It looks like during establishing of the connection the server terminates the connection again - maybe someone else knows how to tweak sql server to do some more logging / where sql server stores logs. Imho there should be some log entry on the server side describing why the server first accetps the client connection and then terminates it. - erg

1 Answers

7
votes

So, I finally got it working after struggling a little bit more.

First of all, it appears that there is another CU available (CU4) for SQL Server Express 2014 SP1.

I did download it and install it, but it did not resolve my problem: as soon as I deactivated TLS 1.0 and below version ODBC did not work.

Strangely, though the CU3 and installation of .NET 4.6 allowed me to successfully log in in Management Studio, when I tried to execute the xp_readerrorlog query, it actually failed.

What I did to solve my issue was to follow malthus's answer on another related stack overflow question https://dba.stackexchange.com/questions/93127/sql-server-service-won-t-start-after-disabling-tls-1-0-and-ssl-3-0 (3rd answer from the top).

I therefore activated the local security policy on my computer and managed to get the links to my SQL db working.

Now, I got IIS working along my own service and client working.

BTW, if somebody needs a more user friendly way of activating SSL/TLS version(s) and cryptographic algorithms on Windows, I stumbled uppon a nice GUI application which does not required to use regedit for it:

https://www.nartac.com/Products/IISCrypto. It's free and I am not a a Nartac Software employee, manager or shareholder.

I hope this will help!

EDIT Feb 25, 2016

It looks like the latest cumulative update (CU5 available at https://support.microsoft.com/en-us/kb/3130926) removes the need to activate the FIPS option for the System Cryptography.

I discovered this since I had to deactivate FIPS to enable another required functionnality while still relying on a TLS 1.2 connection. Indeed, it appears that activating the FIPS option for the System Cryptography prevents .NET Framework to successfuly make use of some system algorithms (such as SHA256Managed).

In addition, according to some discussions seen around the web (i.e. http://blogs.technet.com/b/secguide/archive/2014/04/07/why-we-re-not-recommending-fips-mode-anymore.aspx) it is not recommended to activate FIPS (except to strictly comply with some governmental recommendations) since it breaks applications relying on non-FIPS validated implementations of cryptographic algorithms, which, however are provided in Microsoft system libraries.