TDE Database Encryption with On Premise Dynamics

   Words by James Binnie

   on 04/09/2020 09:41:00

TDE Encryption

We were tasked by a client to look at what levels of Database Encryption (Encryption at Rest) were in place and
also how we could fill in any gaps. Here in the UK we would be talking about the sensitive personal information
covered by the GDPR legislation, and how we can make it as secure as possible. The request is quite simple –
‘please encrypt our database’, however from a SQL novice point of view, this would require a lot of new learning
for your typical Dynamics CRM Consultant.

Sure, I can deploy a multi–server deployment of Dynamics, but we only really touch base with SQL Server and database backups, so I already knew this would be an interesting challenge…

1. Why Should you Encrypt?..

So, we have the correct password policy, latest CRM patches for 8.2 and the server array and network conforms to TLS 1.2. For industry standards data access and data transit is where it currently needs to be, however how secure is that data storage?

Microsoft’s example runs along the line of ‘If someone was able to get access to your SQL Server and remove the hard drive, they could then restore the data in a secondary SQL Server’. This causes a major security flaw and that is where TDE (Transparent Database Encryption) comes in!

TDE is SQL’s form of Encryption at Rest. The term “data at rest” refers to the data, log files, and backups stored in persistent storage and allows an organisation to protect its data when not in use or when stored for audit purposes for instance

2. Things to Consider…

If you ever find yourself in this domain then I thought I would stick this at the top of the blog rather than a wrap up at the end – These are things I either knew already of found out during the process;

    3. Disclaimer…

    It is worth stressing that you need to check your SQL Server version(s) you will be using – The SQL Server licensing model allows for a non-production ‘Developer’ License that shares the same functionality as an Enterprise License. I found this out the hard way as we had the following set up

    • SQL Server 2016 Developer Edition for UAT
    • SQL Server 2016 Standard Edition for Live

    TDE is not available for the SQL Server 2016 Standard Edition so bare this in mind – However it is available for the SQL Server 2019 Edition 

    4. How and Where to Start

    I began my search with Microsoft via a good old Google Search. From what I could find there seemed to be 2 different options available here – Always Encrypted and TDE (Transparent Data Encryption). I did start with Always Encrypted as we only needed to encrypt 6 Fields on the Case (Incident) Entity and this seemed to be the least intrusive option. However as stated above, it was proving to be awkward and slow to test, and CRM was throwing generic SQL errors. With nothing in the SQL Event Log or the SQL Server Logs (Plus with me knowing that Dynamics 365 uses TDE my heart wasn’t really into wasting large amounts of time without exploring TDE myself first)

    What I would say is that the Microsoft documentation is very good and clear and provides your with all the information your require.

    5. Setting up TDE

    The diagram taken below from https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15 shows how the setup of TDE works. Using the familiar Parent/Child relationship concept we have in CRM, here we have a Database Master Key (Parent) and a Database Encryption Key (Child)

    The Transparent Database Encryption architecture

    [I would recommend getting comfortable around the above diagram as this drives the next stage in the process]

      So, we now need to create a DMK (Database Master Key) and a DEK (Database Encryption Key) using the below SQL Query;

        USE master;
        Go
        Create Master Key encryption by password = '<Add a secure password here>';
        go
        CREATE CERTIFICATE Give the DMK a certificate name WITH SUBJECT = 'Example Certificate Subject';
        go
        USE The target Database you want to apply TDE to;
        GO
        CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE Give the DMK a certificate name WITH SUBJECT ;
        GO
        ALTER DATABASE USE The target Database you want to apply TDE to
        SET ENCRYPTION ON;
        GO

          And here is what is looks like for me:

            image

              Once executed, you will get a message below to confirm and you can also check the current SQL Server Logs to show that the TDE Encryption Scan is in progress – Once the scan is completed, TDE is now set up and in place

                6. Progress and Evidencing

                On my Google travels I came across the following query a few times that I would recommend;

                  • Helps you to evidence TDE is in place for a Database
                    • Shows you the progress of the TDE encryption scan

                      SELECT DB_NAME (database_id) AS DatabaseName, encryption_state,
                      encryption_state_desc =
                      CASE encryption_state
                               WHEN '0'  THEN  'No database encryption key present, no encryption'
                               WHEN '1'  THEN  'Unencrypted'
                               WHEN '2'  THEN  'Encryption in progress'
                               WHEN '3'  THEN  'Encrypted'
                               WHEN '4'  THEN  'Key change in progress'
                               WHEN '5'  THEN  'Decryption in progress'
                               WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
                               ELSE 'No Status'
                               END,
                      percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

                        Copy the above query like for like, you don’t need to change anything:

                          image

                          TDE Encryption Scan in progress

                          image

                          TDE Encryption Scan complete

                          Next: Visit our Dynamics Day in the Life Articles and see how CRMCS have successfully implemented innovative and seamless solutions with our current clients

                          Contact us on 0161 348 7360 for a chat or Fill out our Contact Form for further information and how to get started.

                          Share this Article

                          Search Articles

                          Filter Articles

                          CRM Tech DocMan

                          Recent Articles

                          How to Set Up a Microsoft Teams Site Using DocDrive365 Microsoft Teams - Adding a Microsoft Teams URL to a Dynamics Appointment Dynamics 365 Marketing – Customer Voice Survey Not Appearing In Emails? Using SQL Management Studio to connect to the Dynamics DB Calling a Power Platform AI Builder Model via oData How to use DocDrive365 to integrate permissions between Business Units in Dynamics with Sites in SharePoint Getting started with the Power Platform AI Builder. Power Apps Portal Information Hub DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Part 5 - Power Apps Portals: How To Connect Azure B2C With Linked-In Part 4 – Power Apps Portals: Styling Azure B2C for Power Apps Portals The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms Part 3 – PowerApps Portals: Azure B2C and Power Apps Portals – User Flow for Signup and Signin Part 2 - Power Apps Portals: New Application Registration in Azure B2C for our Power Apps Portal Part 1 – Power Apps Portals: Creating a New Azure AD B2C Tenant The Automation Bot: Launching Contextual Flow from Teams Creating a New Bot for Teams Debugging your Teams Bot using Ngrok Adding a Microsoft Teams URL to a Dynamics Appointment Using Power Automate to Create a New Channel for a Record in Dynamics Connecting your Docusign Account with DocDrive365 for eSignature in PowerApps Showing or Hiding a SharePoint Metadata Column within Dynamics Running a Quick, Quick Campaign from Dynamics Dynamics for Sales: Use Word Templates to Export directly to PDF Newly Generated Documents in Dynamics or Power Apps do not save into SharePoint
                          Contact Us

                          Want expert advice or a demo?

                          Get in touch now and see how we can help your business grow.

                          • Name
                          • Email Address
                          • Phone Number
                           
                          Close

                          Understanding Your Challenges

                          Our strong understanding of CRM and emerging technologies within the Microsoft environment means we deliver the right solutions for you.

                          Proven Real-World Solutions

                          As a leader in the field of Dynamics solutions, our pedigree developing and delivering real-world solutions is unsurpassed.

                          Long Term Support

                          We provide support beyond our design, implementation and 'go-live' delivery using Sprints and continual updates to our AppSource apps.

                          CRMCS | Design by Thinktank Marketing | Citrus-Lime Limited

                          To improve your experience today and in the future, this site uses cookies. Read our full Privacy Policy & Cookie information here I Understand