CODEWITHSUNDEEP
sql-serversslodbc
user2457451
user2457451

Reputation: 135

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

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 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!

Upvotes: 6

Views: 44872

Answers (1)

user2457451
user2457451

Reputation: 135

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.

Upvotes: 7

Related Questions