Skip to Content

Error with Data Services Connectivity.

When connecting to Data Services repositories we started getting the following error messages:

There is a problem to connect to respository: [The Driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: The TDS protocol stream is not valid..]

/wp-content/uploads/2015/10/error_819124.png
This means for some reason DataServices cannot talk to SQLServer because it is unable to handshake a secure protocol.

TDS (Tabular Data Stream) is Microsoft’s SQL Server proprietary communication protocol.  It normally is not encrypted except for username/password during connection handshaking, but of course data can be encrypted should you wish, but it takes some effort :
https://gyorgybalassy.wordpress.com/2012/09/14/how-to-encrypt-sql-tds-connections/

ie normally certificates are defined and then connectivity is enabled with the connection eg.
/wp-content/uploads/2015/10/sqlc_819140.png

Anyway the error we seem to be facing is the encryption of the connection, and it seems a bit of a mystery why this would suddenly start occurring when it apparently would take real intellectual effort to enable it, random chance cannot be at play.

Anyway how I fixed the problem (not the root cause) was I followed note 1428134

/wp-content/uploads/2015/10/note1428134_819141.png

In sqlserver startup log we can see the following information messages.

/wp-content/uploads/2015/10/sqlstartlog_819142.png

The telltale info message is

10/27/2015 18:27:57,spid13s,Unknown,The certificate [Cert Hash(sha1) “43DFF73316………1275593074B030969CA9”] was successfully loaded for encryption.

Opened powershell and entered the command from note, you will note that the cert hash key from the above (which I have sanitized with some 99999’s) was inserted

$env:ALLUSERSPROFILE + “\Application Data\Microsoft\Crypto\RSA\MachineKeys\” + (((gci cert:\LocalMachine\my | ? {$_.thumbprint -like “43DFF73316AE99999999999999994B030969CA9”}).PrivateKey).CspKeyContainerInfo).UniqueKeyContainerName

The result C:\ProgramData\Application Data\Microsoft\Crypto\RSA\MachineKeys\98c49360ad999999999999984564b22baf_bd69edc

4f-d146640c1e84

/wp-content/uploads/2015/10/powersh_819149.png

Point your browser to directory

C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys

Fine the file and choose properties.
/wp-content/uploads/2015/10/prop_819150.png

Following note we need to deny access to the SQLServer Service service owners,

I used the names from with the server permissions on SQL management.
studio
/wp-content/uploads/2015/10/dsdsec_819151.png
In this case we are using SQLserver named instances so the name is NT Service\MSSQL$<instancename>

So in  security tab add users from above.

/wp-content/uploads/2015/10/select_819158.png

eg NT Service\mssql$<sid>

Then one by one (for each of the above 3) deny all.
/wp-content/uploads/2015/10/deny_819157.png

Restart the SQLServer Service

/wp-content/uploads/2015/10/sqlrestart_819155.png

Now if you are lucky you should find the new log doesn’t show any pickup of the certificate in startup.

eg.
/wp-content/uploads/2015/10/nosha_819159.png

But in my case found that another second hash was picked up so I repeated the process again. until no certificate is picked up.

Finally we can connect to SQLServer database repositories.
/wp-content/uploads/2015/10/success_819156.png

Would love to know the root cause, if someone has some tips or a guide communicate please 🙂

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply