locked
how to secure connection in sql server 2008? my main problem is which certificate should i add in mmc RRS feed

  • Question

  • i'm recently working on hardening of sql server 2008. now i face with a problem. my problem is  how to secure connection in sql server 2008?  my main problem is which certificate should i add in mmc? what are these certificates about?and guide me in choosing the appropriate certificate.

    and how should i know that the connection in sql server is secured?

    plz guide me from the beginning cause i'm rookie in this subject.

    thanks in advance.
    Tuesday, October 14, 2014 8:19 AM

Answers

  • Hi sqlfan,

    Question 1: my problem is how to secure connection in sql server 2008?
    Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. For more information about Encrypting Connections to SQL Server, please refer to the following article: http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

    Question 2: my main problem is which certificate should i add in mmc? what are these certificates about?and guide me in choosing the appropriate certificate.
    To install a certificate in the Windows certificate store of the server computer, you will need to purchase/provision a certificate from a certificate authority first. So please go to a certificate authority to choose the appropriate certificate.
    For SQL Server 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.

    Question 3: how should i know that the connection in sql server is secured?
    If the certificate is configured to be used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application will be encrypted using the certificate. For more detail about this, please refer to Configuring SSL for SQL Server in the following article: http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

    If you have any question, please feel free to let me know.

    Regards,
    Donghui Li


    Wednesday, October 15, 2014 7:53 AM

All replies

  • Read through the below blog and articles referenced, you should get some idea

    http://thesqldude.com/2011/10/22/to-ssl-or-not-to-ssl-that-is-the-question/


    Keerthi Deep | Blog SQLServerF1 | Facebook

    Tuesday, October 14, 2014 9:32 AM
  • Hi sqlfan,

    Question 1: my problem is how to secure connection in sql server 2008?
    Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. For more information about Encrypting Connections to SQL Server, please refer to the following article: http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

    Question 2: my main problem is which certificate should i add in mmc? what are these certificates about?and guide me in choosing the appropriate certificate.
    To install a certificate in the Windows certificate store of the server computer, you will need to purchase/provision a certificate from a certificate authority first. So please go to a certificate authority to choose the appropriate certificate.
    For SQL Server 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.

    Question 3: how should i know that the connection in sql server is secured?
    If the certificate is configured to be used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application will be encrypted using the certificate. For more detail about this, please refer to Configuring SSL for SQL Server in the following article: http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

    If you have any question, please feel free to let me know.

    Regards,
    Donghui Li


    Wednesday, October 15, 2014 7:53 AM