Unable to connect to SQL Server 2008 using ODBC connection via system dsn on windows 2012

Question

I have Windows 2012 server and I am able to connect to a SQL Server 2008 instance through Visual Studio Server Explorer by creating a data connection. I can also use SQL Server Management Studio and connect to that SQL Server. But for some reason when I try to connect using the ODBC Data Source Administrator to set up a DSN I get the following error.

Connection Failed:
SQLState: '01000'
SQL Error: 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials()).
Connection failed:
SQLState: '08001'
SQL Error: 18
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

I can't seem to find anything about this specifically and wondering if anyone out there is familiar with a this problem.

I would add that neither the client nor the server enforce encryption so its a little confusing to me that I would be getting an ssl related error.

Creeper

Answer 1

After turning on ODBC logging got the event message
A fatal error occurred while creating an SSL client credential. The internal error state is 10013.

this didnt lead me to the exact problem but did lead me to the fix from a similar error.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client\ Enabled needed to be flipped from 0 to 1

Creeper

Thanks! Wasted most of my day troubleshooting this. Now, looking for a way to enable TLS 1.2 instead of having to enable open this vulnerability. – Benzophenone

Information on TLS 1.2 and Sql Server: dba.stackexchange.com/questions/93127/… – Benzophenone

Answer 2

On Windows Server 2016 with SQL Server 2016, I disabled TLS 1.0 and 1.1 and got a problem with just one old site (other, newer websites on the server were working ok).

I tried updating the target build from 4.0 to 4.7 but that didn't fix it.

The problem was some really old code that called the database using OleDbDataReader, I upgraded it to use SqlDataReader and that did the trick.

Didn't find this anywhere else, so thought a post here might help someone else in the future...

Cell

Answer 3

If TLS 1.0 and TLS 1.1 are disabled on your SQL Server (which is a good security practice), you might need to disable it on your client machines. I had a Win 2012 R2 server, with this problem.

In the registry:

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\ ...
 \TLS1.0\Client (and Server): set DisabledByDefault = 1, Enabled = 0
 \TLS1.1\Client (and Server): set DisabledByDefault = 1, Enabled = 0

You probably have to restart your machine for the settings to take effect.

Gitagitel

Answer 4

Enable the below in Local Security Policy --> Local Policies --> Security Options --> ‘System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing

and run the gpupdate /force

Swarm