SQL Server Performance

sql encryption required

Discussion in 'SQL Server 2005 Clustering' started by acki4711, May 9, 2007.

  1. acki4711 Member

    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

  2. satya Moderator

  3. acki4711 Member

    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
  4. satya Moderator

    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.
  5. acki4711 Member

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

    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.
  7. acki4711 Member

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

    Thats where you are getting the issues, this particular client is unable to use the suggested encryption mode, read the KBA again
    quote:So, if a client requests encryption, a certificate must be installed on the computer that is running SQL Server. After you install the certificate on the computer that is running SQL Server, you must restart SQL Server in order to use the certificate. If you do not restart SQL Server after you install the certificate, clients with encryption turned on will fail to connect and the same error message appears:
    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.
  9. acki4711 Member

    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
    ... ?
  10. satya Moderator


    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.
  11. acki4711 Member

    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
  12. satya Moderator

    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.

Share This Page