Post reply

TLS on SQL Server 2014

  • February 25, 2020 at 7:43 pm

    #3728713

    Hi , I need some expert advise from the team here.

    There is a requirement to enable TLS on SQL Server -Microsoft SQL Server 2014 (SP3-GDR).

    I have never implemented this and no clue how to start with.

    I see some topics saying to just enable Force Encryption - to YES, in the SQL server configuration manager(Network protocols).

    And some places have seen for more secure connections to have a Certificate. And  I do not see any Certificate on my SQL Server drop down menu to "select certificate"- . I have given screen shots below.  My question,

    1. How to get this certificate?  Do I need to request my windows team to provide me one?
    2. OR Do I need  to create a new certificate on my own, if so how?
    3. Is it mandatory to have this certifcate or just by option of Force Encryption meets the requirement?

    thanks

  • February 26, 2020 at 4:42 pm

    #3728979

    Hi,

    I have not implemented encryption for client/server connections, though I did look into it in the past. These comments might or might not help (or at least bring the post to the top of the recent activity list)...

    I would have gone for option 1. Ask your Windows admin to generate a certificate for SQL Server. (Make a note of when the certificate expires... it's usually two years I think. At that point nobody will be able to connect to the instance.)

    I'm not sure if Windows clients will automatically download the public key from the Windows Certification Authority, or if the public key will need to be pushed out by a group policy. Take a look at the certificate store on your own PC - you might have certificates already getting pushed to your machine for e.g. Exchange or Skype for Business.

    Non-Windows clients will need the certificate manually imported into their local keystore I would expect.

    My feeling is that connection encryption should be enforced by the instance, otherwise there is not much point in having it. Your release level looks cool for TLS 1.2 https://support.microsoft.com/en-gb/help/3135244/tls-1-2-support-for-microsoft-sql-server

    Don't forget to restart the service and reboot the box as part of your testing. Let us know how it goes.

    HTH

  • February 26, 2020 at 6:11 pm

    #3729018

    Thanks for the inputs, so I was able to get the certificate loaded to configuration manager of SQL Server. The steps I did :

    Use Powershell run as administrator, to create a new certificate for SQL Server:

    PS C:\Windows\system32> New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -DnsName xx.domainname.com -KeySpec KeyExchange -FriendlyName SQLCert

    Just by doing the above, I see the certificate :

     

    But, the issue is I have to restart SQL Server after doing this. And now starting of SQL server leads an error:

    A fatal error occurred when attempting to access the TLS server credential private key. The error code returned from the cryptographic module is 0x8009030D. The internal error state is 10001.

    When I remove the certifcate, SQL Server starts, but when I select it, I'm unable to start SQL Server.

    Any suggestions for this?

     

    Thanks!

     

     

     

     

     

     

  • February 27, 2020 at 11:58 am

    #3729211

    Maybe the SQL server service account has no read rights on the certificate.

    How to:

    Launch MMC (type mmc.exe in the Windows search bar)

    Go to File > Add / Remove Snap-in …

    Select Certificates (when prompted choose Computer Account)

    Right click on the certificate and go to “All Tasks”->”Manage Private Keys”

    Add the SQL Server account to the list of users and grant “Read” to it. Press the “OK” button to apply the changes and close the window.

  • February 27, 2020 at 1:30 pm

    #3729236

    Thanks, I guess you are right, I'll try this step.

  • February 27, 2020 at 1:58 pm

    #3729260

    Other Question : After Implementing TLS 1.2 encryption

    Since this is a  server level change will the server still accept encrypted and unencrypted connections??

    Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply