Scrambled Database refreshes in SQL ServeR


It is common task for SQL Server Database Administrators, for that fact, any DBAs to perform refreshes from Production environment to UAT/CAT/Test/Dev/QA environments. Production databases consists of PII or critical data which unauthorized users should not be able to view, as it violates security and compliance standards. So, it is important for DBAs to make sure that the data refreshed should SCRAMBLE or Obfuscate the critical data so it does not make any sense to the users viewing or making changes to this data. This process is known as SCRAMBLING. 

Normally databases refreshes are performed via scheduled jobs to avoid manual efforts and reduce the time taken to complete the task. As part of the refreshes, the production database is backed up and copied to lower environments and restored and then scripts are run to removed prod users and adding users and permissions specific to the environment. During this process it is important to make sure that the database is not allowed access to any users until we complete the data scrambling process. The data scrambling can be done in below ways.

  1. Identify and change the PII and other critical data by replacing existing values with random values matching the data type and length.
  2. Use third party tools to perform this scrambling process.
  3. Prepare data set for lower environment and replace them on the restored databases.
  4. Encrypt the PII and other critical data, so the non production users cannot decrypt the encrypted data as they would not have access to the encryption keys. But this will cause problems to the applications as they fail to access this data. This will work only if the testing is done on non PII data only.

Next time when you get a request for database refresh make sure to pitch this discussion in with the application teams and management about the importance and compliance and get this implemented. Normally there is no specific responsibility on who should own this task of scrambling the data. Since the application teams are well aware of the schema so they would be the best teams to create the scripts for scrambling the data and DBAs could use that script in the refresh jobs. If application team needs help, DBAs can offer created sample script on how can we scramble the data.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


New Always Encrypted Feature in SQL Server 2016


It is important for every organization to protect their important customers or company’s data and ensure that no one should be able to view or update the data. There are many security mechanisms in SQL Server which will ensure only authorized users can see the data or make changes to it, like logins and database users to restrict the permissions on what they can do with the data. However, still there are many ways, where in unauthorized users still try to gain access to the data, like tapping the network between SQL Server and client system, trying to hack someone else account and gaining access, etc. So, only having security at login/database user level is not enough, so there are other mechanisms available like SSL certificates to encrypt the data that gets transmitted over the network, encryption of data at individual cell/column level or table level or at database level. Prior to SQL Server 2016, database/cell level encryption, still lets the database administrators or other users with high permissions to still gain access to the encrypted data, as the encryption keys are mostly stored in the database and are managed by the DBAs. This feature will be helpful for Microsoft to promote the SQL Azure databases more to the customers showcasing that their data will only be accessible to the clients, but no one else.

Starting with SQL Server 2016, new feature Always Encrypted has been introduced to safeguard the sensitive data from high privilege users as well as unauthorized users. Always Encrypted feature has been designed to protect sensitive data, such as credit card numbers or national identification numbers which are mostly stored in SQL Azure Database or on-premise SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine like SQL Database or SQL Server. Due to this, Always Encrypted provides a separation between those who own the data (who can view it) and those who manage the data (DBAs, but should have no access to sensitive data). This ensure that on-premises database administrators, cloud database operators, or other high-privileged, or unauthorized users cannot gain access to the encrypted data. This will help in for comfortable delegation of on-premises database administration tasks to third parties or to reduce security clearance requirements for the DBA staff.

Always Encrypted features mainly uses two types of keys which are, column encryption keys and column master keys.
Column master keys – are to protect the keys used to encrypt column encryption keys. It is important for the Column master keys should be stored in a trusted key store. The information about column master keys, including their location, is stored in the database in system catalog views.
Column encryption keys – are used to encrypt sensitive data stored which is stored in the database columns. All the values which are in a column can be encrypted using a single column encryption key. The encrypted values of column encryption keys are stored in the database in system catalog views. We need to store column encryption keys in a secure/trusted location for backup.

Another important component of the Always Encrypted feature which plays a key role is Always Encrypted enabled driver which ensures the transparency of encryption to client applications. This Always Encrypted enabled driver calls a server i.e., makes a roundtrip for each query with parameters to retrieve information on how to encrypt query parameter and whether they should be encrypted. This driver then calls a key store provider to decrypt the encrypted column encryption key value. The resultant plaintext column encryption key values are cached. Query results containing data from encrypted columns are accompanied by encryption metadata to enable transparent decryption. So, when an Always Encrypted enabled client driver queries encrypted columns, SQL Server sends the information about encryption settings for the queried columns, including encryption type information. The encrypted value of column encryption keys are used to protect the data in the queried columns, and also the location of the corresponding column master keys. The driver uses that information to, contact the key store containing each column master key and decrypt the column encryption keys, encrypted with the given master key. If for some reason, Always Encrypted is not enabled on the client side, the driver returns encrypted values and the values have the varbinary(max) data type.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Extensible Key Management (EKM) and Hardware Security Module (HSM) in SQL Server

Extensible Key Management (EKM):
SQL Server 2008 introduced Extensible Key Management (EKM) for managing keys outside of SQL Server. Traditionally, all Symmetric and Asymmetric Keys used by SQL Server reside in the database itself; however EKM allows key creation, storage, encryption and decryption to be done outside the database using an HSM.

With the growing demand for regulatory compliance and concern for data privacy, organizations are needs to come up with strong security solutions. One of the approaches followed is use of encryption, but this is often impractical for a strong security solution using only database encryption management tools.

Hardware Security Module (HSM) – Hardware vendors provide products that address enterprise key management by using Hardware Security Modules (HSM). HSM devices store encryption keys on hardware or software modules. This is a more secure solution because the encryption keys do not reside with encryption data.

Hardware security module (HSM) is a physical computing device that safeguards and manages digital keys for strong authentication and provides cryptoprocessing. These modules traditionally come in the form of a plug-in card or an external device that attaches directly to a computer or network server. HSMs may possess controls that provide tamper evidence such as logging and alerting and tamper resistance such as deleting keys upon tamper detection. Each module contains one or more secure cryptoprocessor chips to prevent tampering and bus probing.

An HSM should adhere to one or more recognized security and operational standards defined by various industry groups, such as the Federal Information Processing Standard (FIPS), Common Criteria, etc. An HSM deployment, and supporting operational practices, should also address the requirements of reputable business processes and security auditors to provide the highest degree of protection for the CA and its root keys.

A number of vendors (CREN, Safenet, Thales, etc) offer HSM for both key management and encryption acceleration. HSM devices use hardware interfaces with a server process as an intermediary between an application and an HSM. Vendors also implement MSCAPI providers over their modules, which might be hardware or software. Vendors can also provide management software for HSM, key configuration, and key access.

HSM implementations vary from vendor to vendor, and to use them with SQL Server requires a common interface. Although the MSCAPI provides this interface, it supports only a subset of the HSM features. It also has other limitations, such as the inability to natively persist symmetric keys, and a lack of session-oriented support.

Extensible Key Management – The SQL Server Extensible Key Management enables third-party EKM/HSM vendors to register their modules in SQL Server. When registered, SQL Server users can use the encryption keys stored on EKM modules. This enables SQL Server to access the advanced encryption features these modules support such as bulk encryption and decryption, and key management functions such as key aging and key rotation.

EKM Configuration – Extensible Key Management is Enterprise Edition feature. By default, Extensible Key Management is off. To enable this feature in SQL Server, use the sp_configure command that has the following option and value, as in the following example:

sp_configure 'show advanced', 1
Reconfigure with override
sp_configure 'EKM provider enabled', 1
Reconfigure with override

To disable the feature, set the value to 0.

SQL Server Extensible Key Management enables the encryption keys that protect the database files to be stored in an off-box device such as a smartcard, USB device, or EKM/HSM module. This also enables data protection from database administrators (except members of the sysadmin group). Data can be encrypted by using encryption keys that only the database user has access to on the external EKM/HSM module.

You can use Extensible Key Management for a username and password combination or other methods defined by the EKM driver. An EKM module can support more than one type of authentication. Each provider exposes only one type of authentication to SQL Server, that is if the module supports basic or other authentication types, it exposes one or the other, but not both.

EKM Device-Specific Basic Authentication Using username/password – For those EKM modules that support Basic authentication using a username/password pair, SQL Server provides transparent authentication using credentials.

A credential can be created for an EKM provider and mapped to a login (both Windows and SQL Server accounts) to access an EKM module on per-login basis. The Identify field of the credential contains the username; the secret field contains a password to connect to an EKM module.
If there is no login mapped credential for the EKM provider, the credential mapped to the SQL Server service account is used.

A login can have multiple credentials mapped to it, as long as they are used for distinctive EKM providers. There must be only one mapped credential per EKM provider per login. The same credential can be mapped to other logins.

Other Types of EKM Device-Specific Authentication – For EKM modules that have authentication other than Windows or user/password combinations, authentication must be performed independently from SQL Server.

SQL Server can use EKM keys to encrypt other keys in a database. You can create and use both symmetric and asymmetric keys on an EKM device. You can encrypt native (non-EKM) symmetric keys with EKM asymmetric keys.

The following example creates a database symmetric key and encrypts it using a key on an EKM module.

--Open database key

The main idea here is to get a HSM solution from a Vendor and then integrate it with the SQL Server and store the encryption keys on a HSM. The data can be encrypted/decrypted with the keys (Symmetric key, Asymmetric key, certificate) which are stored in HSM and only authorized users can gain access to these keys based on process defined by the HSM provider.

Happy Reading,
SQLServerF1 Team


Introduction to basics of Security and Encryption in SQL Server

What is Encryption?
Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted.

In SQL Server encryption can be used for connections, data, and stored procedures. We will see further about security, encryption, types of encryption.

Securing SQL Server can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system.

Platform and Network Security – The platform for SQL Server includes the physical hardware and networking systems connecting clients to the database servers, and the binary files that are used to process database requests. Best practices for physical security strictly limit access to the physical server and hardware components. For example, use locked rooms with restricted access for the database server hardware and networking devices. In addition, limit access to backup media by storing it at a secure offsite location.

Operating system service packs and upgrades include important security enhancements. Apply all updates and upgrades to the operating system after you test them with the database applications. Firewalls also provide effective ways to implement security. Logically, a firewall is a separator or restrictor of network traffic, which can be configured to enforce your organization’s data security policy. If you use a firewall, you will increase security at the operating system level by providing a chokepoint where your security measures can be focused. SQL Server uses operating system files for operation and data storage. A best practice for file security requires that you restrict access to these files.

SQL Server Objects Security – Access to SQL Server objects like SQL Server instance, databases, tables and other objects can be restricted with SQL Server logins and database users. Granting appropriate permissions limits the users from accessing unauthorized information.

Data in SQL Server can be encrypted. Encryption enhances security by limiting data loss even in the rare occurrence that access controls are bypassed. For example, if the database host computer is misconfigured and a malicious user obtains sensitive data, such as credit card numbers, that stolen information might be useless if it is encrypted.

Application Security – SQL Server security best practices include writing secure client applications. Which involves application securely connecting to SQL Server, using appropriate protocols, using network security and network encryption.

Not only applications, but there are tools which are used to access SQL Server like SQL Server Management Studio, SQLCMD utility, SQL Server configuration manager, profiler, BIDS studio, etc. Need to ensure these tools also use secure methods to connect to databases to get the data.

Encryption to Secure SQL Server Data:
Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. In SQL Server encryption can be used for connections, data, and stored procedures. Data encryption can be performed by the OS, by SQL Server, or by the application.

There are different types of encryptions available in SQL Server which includes Cell-level Encryption, Transparent Data Encryption, and Transport-Level Encryption.

Cell-level Encryption – Cell-level encryption allows sensitive data to be encryption at a finer level of detail than the entire database. In a table, you might just want to encrypt certain column like salary or credit card number which should not be visible.

Benefits of using Cell-Level Encryption:
(1) Granular, user specific control on encrypting individual cells or column values rather than entire databases (compared to using Transparent Data Encryption – TDE).
(2) Data retains its encrypted state in memory unless it is actively decrypted.

Drawbacks of Cell-Level Encryption:
(1) Requires application changes and analysis of tables to locate sensitive data that needs to be encrypted.
(2) Encryption of data introduces randomization. This makes it impossible to index data and causes a performance impact since indexes on encrypted columns cannot be used while searching for a value.
(3) Cell-level encryption built-in functions only return varbinary type data and the output is limited to up to 8000 bytes.

Transparent Data Encryption – Transparent Data Encryption (also called as TDE) is a technology in SQL Server that offers encryption of data-at-rest. This feature automatically encrypts the entire database (data and log files), as well as database backups, without requiring any programming or code changes to your application. The process is entirely transparent, hence the name Transparent Data Encryption.

When TDE is first enabled for a specific database, SQL Server encrypts the database in the background. During this process, the database remains online and responsive to client requests (similarly, when encryption is disabled, SQL Server decrypts the database in the background). Encryption is performed at the page level, and does not increase the size of the database in any way. Once the entire database is encrypted, new data gets encrypted on the fly as it is written to disk, and all data gets decrypted when read back.

Benefits of using TDE:
(1) Ease of implementation and transparency: TDE is essentially a “flip-the-switch” solution that allows you to encrypt your entire database and log files without application modifications.
(2) Additional security compared to cell-level encryption: TDE automatically encrypts tempdb and the database log files to prevent data leakage.
(3) Any data-type supported: Unlike cell-level encryption that returns only varbinary data, TDE allows you to store data using any native data type.
(4) Support for indexes: Because data is decrypted in the buffer pool, TDE allows the SQL Server query processor to use existing indexes on the data.

Drawbacks of TDE:
(1) Lowest support encryption granularity is the database.
(2) The data is not protected from authenticated, authorized database users, including the DBA.

Transport-Level Encryption – Secure Sockets Layer (SSL) encryption enables transmitting encrypted data across the network between an instance of SQL Server and a client application. SSL is used in SQL Server to provide security at transport level. It is most commonly used to support web clients, but it can also be used to support SQL Server native clients.

Secure Sockets Layer (SSL) is a protocol for establishing a secure communication channel to prevent the interception of critical or sensitive information across the network and other Internet communications. SSL allows the client and the server to authenticate the identity of each other. After the participants are authenticated, SSL provides encrypted connections between them for secure message transmission.

Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, enabling encryption does slow performance.

Encryption Mechanisms – SQL Server provides the following mechanisms for encryption:

Transact-SQL functions – Individual items can be encrypted as they are inserted or updated using Transact-SQL functions.

Asymmetric keys – An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other. Asymmetric encryption and decryption are relatively resource-intensive, but they provide a higher level of security than symmetric encryption. An asymmetric key can be used to encrypt a symmetric key for storage in a database.

Symmetric keys – A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database.

Certificates – A public key certificate, usually just called a certificate, is a digitally-signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. Certificates are issued and signed by a certification authority (CA). The entity that receives a certificate from a CA is the subject of that certificate.

MSDN Reference
Cell Level ENcryption

Happy Reading,
SQLServerF1 Team