Dotnetnuke/Sql Tips & Tricks

Transparent Data Encryption is designed to protect data by encrypting the physical files of the database, rather than the data itself These physical files include the database file (.mdf), the transaction log file (.ldf) and the backup files (.bak). Its main purpose is to prevent unauthorized access to the data by restoring the files to another server.  With Transparent Data Encryption in place, this requires the original encryption certificate and master key. The protection of the database files is accomplished through an encryption key hierarchy that exists externally from the database in which TDE has been enabled. The service master key exists at the instance level. The database master key and certificate at the Master database are used to protect the database encryption key that is located at the user database The dependency upon the encryption key hierarchy in the Master database, as well as the instance, prevents the database files from being restored to an instan

Dynamic Data Masking(DDM) SQL Server 2016 introduced dynamic data masking (DDM) as a way to prevent unauthorized users from viewing sensitive information. The database engine masks the data when it is retrieved from the database, based on masking rules defined on the column schema.  IMPORTANT - The data stored within the database remains unchanged. When a user queries the database, the database engine determines whether that user account has the permissions necessary to access the data in its unmasked state It is implemented within the database itself, the logic is centralized and always applies when the sensitive data is queried. It is quite simple to configure DDM rules on sensitive column Key points to consider; DDM is not a replacement for security access control mechanisms, nor is it a method for encrypting physical data.  Data masking performance impact is minimal and often negligible SQL Server 2016 and Azure SQL DB are the only platforms supporting data maskin