Data Services ; Problem connecting to Repository ; The TDS protocol Stream is not valid..

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..]

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.

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

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

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

Point your browser to directory

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

Fine the file and choose properties.

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

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.

eg NT Service\mssql$<sid>

Then one by one (for each of the above 3) deny all.

Restart the SQLServer Service

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

eg.

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.

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