SQL Server Performance

SQL 2005 encryption check

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by avipenina, Aug 6, 2007.

  1. avipenina New Member

    Hi,
    I've enabled the certificate on my SQL 2005 server
    and i want to test the connection through the SSL, now i don't to test the connection only when i check the
    client for encrypt in the client utility i want to see it lets say through the sql server profiler or other tool
    i want to see the different between the connections.
    how i do that?
  2. satya Moderator

    If encryption is enabled I don't think you will be able to monitor with profiler as they will be encrypted.
  3. avipenina New Member

    when i turn on the force protocol encryption on the Client network utility i'm able to connect to the sql server
    when i remove the force protocol encryption on the Client network utility i'm still able to connect to the server
    even when i start the force encryption in the protocols for mssqlserver i can connect to the server with and without encryption
    why is that?
  4. satya Moderator

    What sort of options and settings enabled on server side for encryption?
  5. avipenina New Member

    what do you mean exactly?
  6. satya Moderator

  7. avipenina New Member

    can u help me on making computer certificate with all this needs?For SQL Server 2005 to load a SSL certificate, the certificate must meet the following conditions:
    • The certificate must be in either the local computer certificate store or the current user certificate store.
      • The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
        • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
          • The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
            • The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
  8. satya Moderator

    I believe you might have seen this blog post in this case, if so have you referred the other links within that post.
  9. avipenina New Member

    [quote user="satya"]
    I believe you might have seen this blog post in this case, if so have you referred the other links within that post.
    [/quote]
    i've a computer certificate but i think that this certificate doesn't have all the requirements that a server certificate needs
    and i can't make one like the server needs.
    and when i create user certificate then i can't see the users certificate in the protocols for mssqlserver properties.
  10. satya Moderator

  11. avipenina New Member

    OK after implement the link with the makecert i see that it's half working.
    what i mean is that even when i turn on the ForceEncryption to Yes when i installed the certificate on the Protocols for mssqlserver
    when in the client i remove the force protocol encryption the client can connect to the server.
    but when i turn on the force protocol encryption the client can't connect(because he needs the certificate obviously)
    why is that? it should not connect when the force protocol encryption on the client is turned off.
  12. avipenina New Member

    can anyone help we with why the client can connect to the server when his encryption is set to off
    and when his encryption is set to on it cannot (which is fine).
    the Protocols for MSSQLSERVER Force Encryption is set to Yes.
  13. acki4711 Member

    avipenina,
    If you set the enforce encryption flag on sql server (restart sql server needed) the communication with ALL clients is encrypted. (Client don't need to set anything to make this happen).
    If you set enforce encryption on client, the communikation to ONLY that client is encrypted.
    The only way to check if encryption happens is to install packet sniffer.
    All this has nothing to do with client can/cannot connect. If you want to dissalow some clients to connect you have to go another way (firewall, IP restrictions, etc...)

Share This Page