Post reply

Column Level Encryption In SQL Server 2016

  • April 11, 2022 at 10:31 am

    #4013872

    Hello,

    I'm planning to use different certificate instead of self signed certification while implementing Column level encryption in SQL server 2016. So, it is like we have to create a Symmetric key but in order to secure a Symmetric key, we should have a 'different ( not self signed) digitally certificate. How can I accommodate the different certification while implement this encryption type.

    Example:

    CREATE CERTIFICATE SelfSignedCertificate  ---> how to change this to different cert.

    WITH SUBJECT = 'Password Encryption';

    GO

    CREATE SYMMETRIC KEY SQLSymmetricKey

    WITH ALGORITHM = AES_128

    ENCRYPTION BY CERTIFICATE SelfSignedCertificate;  --> change this to different cert.

    GO

    Thank you!

  • April 11, 2022 at 3:14 pm

    #4013997

    I am pretty sure the syntax is just "CREATE CERTIFICATE <certname> FROM FILE=<path to file>", and a quick google confirmed this for me:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15

    Mind you, you likely also want to include the private key and the private key password.  Microsoft has good examples at the bottom of that page.

    The certificate name (SelfSignedCertificate in your example, <certname> in my example) is just a name that SQL uses to reference the certificate.  You can name it anything you like as long as you use that same name when using it.

    Now, my opinion, when working with encryption of data at rest, a properly created self signed certificate is just as good as a purchased certificate.  When dealing with data in motion, you will want a better trusted certificate though (thinking SSL here) so that all clients connecting can auto-trust the certificate as the trust the CA.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 2 posts - 1 through 1 (of 1 total)

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