SQL Server Performance Forum – Threads Archive

sql encryption required

Hi all
Have a sql 2005 sp2 a/p setup with installed cert from our own cert auth.
Can connect with several clients to this server’s -> no problem. With one specific client I get in errorlog: 2007-05-09 12:32:37.58 Logon Error: 17835, Severity: 20, State: 1.
2007-05-09 12:32:37.58 Logon Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library. [CLIENT: 138.xxx.xx.x7] Have Installed the sqlncli.msi (native client) from sql server 2005 install disk but problem still persists. Adding SP2 on client does not change anything. What else could I try?
TIA
acki

---

See this KBAhttp://support.microsoft.com/kb/318605 is any help. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

satya,
I set the encryption on the server.
Some clients can connect -> means cert is ok
One client cannot connect -> where’s the problem TIA
acki

---

Check what is the difference on that client alone, as compared to others. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

The only difference I see is on client there is a msde and VS.net installed…

---

As per the KBA have you enabled the Force protocol encryption? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

satya,
"Force protocol encryption" is set on the SERVER side.

---

Thats where you are getting the issues, this particular client is unable to use the suggested encryption mode, read the KBA again
for further information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

satya,
force encryption is set on the server.
server runns with the cert installed, other clients can connect
flag is not set on ANY client I agree that this particular client has a problem with the encryption method.
But what do I have to upgrade/change on client side???
SQLServer ODBC dll is uptodate.
dbnetlib uptodate as well
… ?

---

If you use SSL Encryption on a SQL Server cluster, you can use the same procedures except that the certificate must be issued to the fully qualified domain name of the Virtual SQL Server and not the individual computer name. In addition, the way Microsoft recommends that you use Certificates and SSL encryption on a SQL Server cluster is:
-Install the certificates on each node in the cluster.
-Install the Trusted Root Authority on each client.
-Enable the Force Protocol Encryption option from the client computers by using the Client Network Utility. If you prefer the client to trust the same root authority, you must use Client Network Utility or the connection string option to force protocol encryption on the client. This is by design. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

satya
In my understanding there are two different approches for using SSL with sql server 1) If I want to encrypt ALL sql traffic to sql server I do the following:
– install a certificate on each node of the sql cluster
– install Trusted Root Authority on each client
– set the "force encryption flag" on the server 2) If I want to encrypt sql traffic to ONE specific client I do the following:
– install a certificate on each node of the sql cluster
– install Trusted Root Authority on specific client
– set the "force encryption flag" on the client However, for me approach 1) is what I want and I resolved the problem by making sure that Trusted Root Authority was installed correctly on the non working client. Conclustion: never thrust what the sysadmin tells you! HTH
acki

---

Acki
Appreciate what you have found from the list has similar and resolved the issue, that is the reason I have mentioned about the method you need follow in the case of force protocol encryption. Always Trusted Root Authority is the key in the kereberos authentication of client to server, if mismatches then the issues you have already seen. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

]]>