locked
Using a certificate in SQL2008R2 not working as expected RRS feed

  • Question

  • Hi,

    We created a certificate for a 64_bit SQL2008R2 Standard Edition instance and made the SQL Server Network Configuration/Protocols for MSSQLSERVER have Force Encyption set to Yes and added the certificate. Under the Advanced Tab we have Extended protection Off and Accepted NTLM SPNs is blank.

    Now my question is about accessing this server. As a Windows account that is a Sysadmin to this server should I be able to use SSMS, on another SQL2008R2 server, and be able to access remotely without using the certificate?

    This doesn't seem right? I should be getting an access denied type message shouldn't I?

    Thanks

    Chris

    Wednesday, June 20, 2012 9:46 PM

Answers

  • Hi ChrisAVWood,

    Members of the sysadmin fixed server role can perform any activity in the server. So it should be able to use SSMS in the same server.

    On another Server, it could not be access the database engine without using the certificate.

    Enable encrypted connections for an instance of the SQL Server Database Engine by specifying a certificate for the Database Engine using SQL Server Configuration Manager. The server computer must have a certificate provisioned, and the client machine must be set up to trust the certificate's root authority.

    How to enable Encrypted Connections to Database Engine (SQL Server Configuration Manager): http://msdn.microsoft.com/en-us/library/ms191192(v=sql.105).aspx.

    TechNet Subscriber Support

    If you are TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.


    Thanks,
    Maggie

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Sunday, July 1, 2012 4:24 PM
    Friday, June 22, 2012 8:00 AM
  • It shouldn't matter whether the connecting login is a sysadmin or not. And the edition shouldn't matter either.

    Som information from Books Online http://msdn.microsoft.com/en-us/library/ms191192.aspx

    The server computer must have a certificate provisioned, and the client machine must be set up to trust the certificate's root authority.... The client must be able to verify the ownership of the certificate used by the server. If the client has the public key certificate of the certification authority that signed the server certificate, no further configuration is necessary. Microsoft Windows includes the public key certificates of many certification authorities. If the server certificate was signed by a public or private certification authority for which the client does not have the public key certificate, you must install the public key certificate of the certification authority that signed the server certificate.

    So, I think, your client wants to connect to the server. Your server says you must encrypt, and I have this certificate. The client says, OK, I trust that certificate, and I have the public key. Here is something encrypted with the public key. The server says, OK, that's valid. Let's use that channel to start an encrypted conversation.

    The potential fly in the ointment, is that the client might not have checked the servers certificate. There is an option in SQL Server Configuration Manager, on the client, for the client to select "Trust Server Certificate". This backwards named option, defaults to No, meaning the client doesn't validate the server certificate. The client just assumes it is good. Try setting that to Yes, meaning the client will validate the server's certificate is valid. If the certificate was issued by a trusted certificate authority (like Verisign) then the client can check it. If the certificate was issues by "Joe's great certs" then the client probably can't check it and the client will reject the server's certificate and then they are stuck. But if the certificate was issued by a certificate authority in your company, I don't know if the client can check it or not. Maybe.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Sunday, July 1, 2012 4:24 PM
    Tuesday, June 26, 2012 4:24 PM
  • SQL Server does use a self-signed certificate to encrypt the login packet when a user certificate has not been provided. However that is not (should not be) used when a user has provided a certificate. So I think the client is able to validate the cert you have provided, and is therefor using it. Unfortunatly I don't know of a way to definitively determine what cert is being used.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Sunday, July 1, 2012 4:24 PM
    Tuesday, June 26, 2012 8:52 PM

All replies

  • Hi ChrisAVWood,

    Members of the sysadmin fixed server role can perform any activity in the server. So it should be able to use SSMS in the same server.

    On another Server, it could not be access the database engine without using the certificate.

    Enable encrypted connections for an instance of the SQL Server Database Engine by specifying a certificate for the Database Engine using SQL Server Configuration Manager. The server computer must have a certificate provisioned, and the client machine must be set up to trust the certificate's root authority.

    How to enable Encrypted Connections to Database Engine (SQL Server Configuration Manager): http://msdn.microsoft.com/en-us/library/ms191192(v=sql.105).aspx.

    TechNet Subscriber Support

    If you are TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.


    Thanks,
    Maggie

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Sunday, July 1, 2012 4:24 PM
    Friday, June 22, 2012 8:00 AM
  • I made the change in that I used the Windows account running SQL rather than a Windows account that has sysadmin authority to drop and re-add the certificate into SQL Server Configuration Manager. I see the message The certificate [Cert Hash(sha1) "xxxxx"] was successfully loaded for encryption in the SQL errorlog but I can still access this server from another SQL2008R2 server as before.

    Is this happening because I am using Standard Edition or do I have to say Encrypt connection on any Client or Server that wants to access this SQL server instance?

    Thanks

    Chris

    Friday, June 22, 2012 7:34 PM
  • It might be helpful to check SELECT encrypt_option, * FROM sys.dm_exec_connections; to see if the connection is encrypted.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, June 25, 2012 5:08 PM
  • Rick,

    That was helpfull. I am connecting from another server using SSMS and the output shows Encypt_option as TRUE with auth_scheme of NTLM but I haven't loaded the certificate onto the server I am connecting from. Because I am using a Windows account that is a sysadmin on both the server I am connecting from and the one that has the certificate on does it bypass needed the certificate?

    Chris

    Monday, June 25, 2012 5:21 PM
  • Just as a test I created a nobody SQL login on my SQL2008R2 Standard Edition that has a certificate and gave it public access to master. I was able to connect with that SQL login from another server and the encrpyt_option came back as TRUE with SQL as the auth_scheme. Remember this was from a server that my Windows account was a sysadmin on both the encrypted server and the source server.

    Just to see if it was confused by that I connected from my own PC with that SQL login and was able to connect with TRUE for encrpyt_option and SQL for the auth_scheme when I do not have the certificate.

    This makes me believe that for this to work as expected we must use Enterprise Edition and not Standard Edition.

    Any suggestions?

    Chris

    Monday, June 25, 2012 5:57 PM
  • It shouldn't matter whether the connecting login is a sysadmin or not. And the edition shouldn't matter either.

    Som information from Books Online http://msdn.microsoft.com/en-us/library/ms191192.aspx

    The server computer must have a certificate provisioned, and the client machine must be set up to trust the certificate's root authority.... The client must be able to verify the ownership of the certificate used by the server. If the client has the public key certificate of the certification authority that signed the server certificate, no further configuration is necessary. Microsoft Windows includes the public key certificates of many certification authorities. If the server certificate was signed by a public or private certification authority for which the client does not have the public key certificate, you must install the public key certificate of the certification authority that signed the server certificate.

    So, I think, your client wants to connect to the server. Your server says you must encrypt, and I have this certificate. The client says, OK, I trust that certificate, and I have the public key. Here is something encrypted with the public key. The server says, OK, that's valid. Let's use that channel to start an encrypted conversation.

    The potential fly in the ointment, is that the client might not have checked the servers certificate. There is an option in SQL Server Configuration Manager, on the client, for the client to select "Trust Server Certificate". This backwards named option, defaults to No, meaning the client doesn't validate the server certificate. The client just assumes it is good. Try setting that to Yes, meaning the client will validate the server's certificate is valid. If the certificate was issued by a trusted certificate authority (like Verisign) then the client can check it. If the certificate was issues by "Joe's great certs" then the client probably can't check it and the client will reject the server's certificate and then they are stuck. But if the certificate was issued by a certificate authority in your company, I don't know if the client can check it or not. Maybe.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Sunday, July 1, 2012 4:24 PM
    Tuesday, June 26, 2012 4:24 PM
  • Rick,

    I just tried your suggestion on My PC and it did not make any difference, I was still able to connect to the server with the certificate.

    A colleague has suggested that as this is a self created certificate and not a trusted one that the connection becomes encypted but doesn't stop others from connecting to the server with the certificate and not needing the certificate to do it.

    So what we have is an encypted connection but clients with valid credentials should be able to connect to it using SSMS or SQLCMD etc without having the certificate.

    Thanks for your help in getting to the bottom of this.

    Chris

    Tuesday, June 26, 2012 7:11 PM
  • SQL Server does use a self-signed certificate to encrypt the login packet when a user certificate has not been provided. However that is not (should not be) used when a user has provided a certificate. So I think the client is able to validate the cert you have provided, and is therefor using it. Unfortunatly I don't know of a way to definitively determine what cert is being used.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Sunday, July 1, 2012 4:24 PM
    Tuesday, June 26, 2012 8:52 PM
  • Additional information Using Encryption Without Validationhttp://msdn.microsoft.com/en-us/library/ms131691.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, June 26, 2012 9:02 PM
  • I understand this a lot more now.

    Here is another question about this Rick. As we move up to the newer versions of SQL we try to reduce the permissions of the services running the various services that come with SQL Server. We found that we had to make the Windows Account running SQL an administrator on the W2K8R2 SP1 server that SQL was running on to get SQL to start up once we had the certificate loaded. Is this overkill? Is there a permission we can give the Windows Account so that SQL can start rather than an admin?

    Thanks

    Chris

    Monday, July 9, 2012 7:13 PM
  • Windows stores the certificate in the users local certificate store. If you (Windows user Bob) use SQL Server Configuration Manager to install the certificate, and then start Database Engine service using your credentials (Bob) then SQL Server can access the Windows certificate store as Bob and read the certificate.

    But... if you start the Database Engine service running under the credentials of Mary, then the Database Engine can't find the certificate because Mary doesn't have access to Bob's certificate store. So the trick is to install the certificate while logged in as the Windows identity that will be used for the Database Engine service.

    From Books Online topic Enable Encrypted Connections to the Database Engine (SQL Server Configurationi Manager) http://msdn.microsoft.com/en-us/library/ms191192.aspx

    • Certificates are stored locally for the users on the computer. To install a certificate for use by SQL Server, you must be running SQL Server Configuration Manager under the same user account as the SQL Server service unless the service is running as LocalSystem, NetworkService, or LocalService, in which case you may use an administrative account.

    The details of what goes on in the certificate store is a Windows issue, so it isn't really my area. But there are some Windows articles on MSDN that can help. I see:
    http://msdn.microsoft.com/en-us/library/windows/hardware/ff548653(v=vs.85).aspx
    http://msdn.microsoft.com/en-us/library/windows/desktop/aa386971(v=vs.85).aspx
    http://blogs.msdn.com/b/steverac/archive/2009/07/09/adding-certificates-to-the-local-certificates-store-and-setting-local-policy-using-a-command-line-system-center-updates-publisher-example.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, July 9, 2012 9:04 PM
  • I have the answer in a blog entry by Nick Olsen.

    I gave the Group running SQL Read permission on the certificate inside the Certificates MMC snap-in. This works fine.

    Thanks for all your help Rick.

    Chris

    Monday, July 16, 2012 8:13 PM