Using SQL Server Technologies for Enhanced Data Security

Data is an extremely valuable business asset, and data security is rapidly becoming an extremely critical business concern in this increasingly connected and fast-paced world. When Solvaria’s client launched a client portal, we were called in to help keep the sensitive data they were receiving secure. Microsoft has developed several database technologies that Solvaria includes in our 24×7 Remote or On-Site Managed DBA Services. We implemented three of these technologies to enhance data security for the data stored within our client’s databases.

Transparent data encryption

Transparent data encryption, or TDE, encrypts a database file while it’s on disk. This means that even if someone gains unauthorized access to a database file, they would still be unable to read it. In order to encrypt the database file, our SQL Server DBAs first create a master key on the database, then create a certificate protected by the master key, which in turn protects the encryption. Since SQL Server dynamically encrypts and decrypts the data as it moves onto and off the disk, end users and applications are unaware that the encryption is even happening.  Since TDE is contained entirely within the database, it pairs well with Always On Availability Groups, with the only requirement being that the same certificate that encrypted the data must be installed on every replica that will house the database.

SQL Server Auditing

SQL Server Auditing records SQL statements that run against the database, while statements that are expected, such as those from an application, can be filtered out and not reported. Auditing can be specified at the SQL instance or the database level, which gives a great deal of flexibility and the ability to audit specific types of activity, or everything, as needed. This does not prevent changes from being made, but a record is created that allows an auditor to determine who changed data in the database, or accessed data improperly.

Change Data Capture

Change Data Capture records data as it is being changed. This includes every delete, every insert, and every update to the rows. As changes are made, SQL Server captures them and loads them to tables that can be consumed by another process, such as a data warehouse load or data audit process.  The data in those tables is kept for a certain amount of time then purged.  This allows a business to keep a record not only of the fact that changes were made, but the actual data that was changed.

Note: When implementing Change Data Capture with Always on Availability, the DBA will need to manually modify the jobs to make sure they only run on the primary replica and that they are started on the new primary when a fail over occurs. 

Each security method has it’s own purpose, and if a business has critical data they need to protect, any of these methods can be implemented by themselves or in concert with each other. 

Does your business have client data that needs protecting?

{{cta(‘6d2db1f2-3cb9-4426-bd66-b3a1c91322e1′,’justifycenter’)}}

{{cta(‘aa3c05ab-3130-4402-9d18-029bb527314a’)}}