How to configure and remove Transparent Data Encryption in SQL SERVER.

Transparent Data Encryption (TDE) is concept of encrypting data and log files of a database. This encryption is transparent to user, as data gets stored in encrypted format on disks and when user retrieves the data it gets decrypted and shown. More information related to TDE can be found in these links 1, 2.

In this post I will be providing step by step commands to use for enabling TDE in a database/instance and step by step commands for removal of TDE from the database/instance.

Please read about TDE fully before using this feature as improper maintenance of CERTIFICATES or KEYS can lead to data loss or database un-availability.

How to enable Transparent Data Encryption for a database?

Step:1 CREATION OF MASTER KEY

While enabling/configuring TDE first we need to create a MASTER KEY in master database of the server using below command.

USE master

Go

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'pa$$w0rd';

Go

Step:2 CREATION OF CERTIFICATE

We need to create a CERTIFICATE  in master database using below command.

USE master

Go

CREATE CERTIFICATE TestCert

WITH SUBJECT = 'TDE Test Certificate'

Step:3 BACKUP CERTIFICATE with PRIVATE KEY

Once we create certificate the immediate step should be to take backup of the certificate as recommended by Microsoft.

Note: If this CERTIFICATE OR KEY are lost then the database cannot be restored to any other server.

USE master

GO

BACKUP CERTIFICATE TestCert

TO FILE = 'C:\Temp\ TestCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Temp\ TestCert.pvk' ,

ENCRYPTION BY PASSWORD = 'pa$$w0rd' )

Once above command completes successfully we can see 2 files created in the location ‘C:\Temp’. Whenever we want to restore the database in other instance/server we need to copy these 2 files must, if not restore fails.

Step:4   CREATE DATABASE ENCRYPTION KEY

Next create a database encryption key based on the server certificate we created in previous step, use below command

USE [Test]

Go

  CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = AES_128

    ENCRYPTION BY SERVER CERTIFICATE TestCert;

GO

Step:5 ENABLE ENCRYPTION at database level

Next we need to turn on encryption at database level using below command.

USE [Test]

Go

ALTER DATABASE [Test] SET ENCRYPTION ON

We can use below 2 system tables to verify encryption status of each database and the certificates available in the instance.

                  sys.dm_database_encryption_keys

                       sys.certificates

Once we enable TDE on any of the database of instance automatically tempdb gets encrypted.

How to remove Transparent Data Encryption completely.

Step:1 DISABLE ENCRYPTION at database level.

First step is to disable database encryption using below command.

USE [Test]

Go

ALTER DATABASE [Test] SET ENCRYPTION ON

GO

Step:2 DROP DATABASE ENCRYPTION KEY

Now drop the database encryption key using below command.

USE [Test]

Go

DROP DATABASE ENCRYPTION KEY

Step:3 DROP CERTIFICATE from master database

Now drop the server level certificate using below command.

USE master

Go

DROP CERTIFICATE TestCert;

Go

Step:4  DROP MASTER KEY from master database

Now drop the server level master key using below command.

USE master

Go

DROP MASTER KEY;

In order to remove TDE completely we need to restart the instance after performing all the above 4 steps.

To verify the status of database encryption on each database we can use below system tables:

                   sys.dm_database_encryption_keys

                   sys.certificates

Thanks!!

5 components of SQL Server Architecture you must know.

Below are the 5 must know components/features of SQL Server Architecture as per me. To set the expectations clear about this post, in this article, I have given a brief overview of few components of SQL Server architecture, also note that each of these components may have several sub-components too but those are not covered here. To get in-depth knowledge of architecture I recommend going through MSDN website and other useful websites online. The main motto of this article is to cover few concepts of SQL Server Architecture that are asked in most interviews.

Storage Engine:

Its role is to store and retrieve data in database files. Every database will have minimum 2 types of files ‘.mdf (data file)’ and ‘.ldf (log file)’. Database files are for storing data and log files are for write-ahead logging.

The basic storage unit of SQL Server is called as ‘Page’, each page will be of 8KB size. ‘Extent’ is a group of 8 pages, so each extent would of 64KB size. Data inserted into a database will be stored in these pages. Here one thing to note is different types of data are stored in different pages ex: images, XML data are stored in LOB pages and so on;

Relational Engine:

It is also called as Query Processor. To put in simple terms this component determines the best way to execute the queries, executes them and returns back the result to the user. To execute the query it always tries to use the cheapest way i.e.., it tries to execute the queries by using minimal resources. It is a cost-based optimizer, so for every query before executing it, it determines what are possible ways of executing it and estimates the cost of each way and select the cheapest way among all.

Buffer Management:

It performs two mechanisms: the buffer manager to access and update database pages, and buffer cache (also called buffer pool), to reduce database file I/O. So basically buffer manager reads data pages from the disk and places them in the buffer cache(buffer pool) and if any modifications are done to the page which is already in buffer cache then buffer manager writes back those pages to the disk. Any page will remain in buffer cache as long as there is enough free space to accommodate new pages in it, if there is a need to copy new data pages to buffer cache and there is not enough space then the existing pages are flushed back to disk based on LRU algorithm. Only if the data of a page is modified they are written back to disk.

Lazy Writer:

Lazy writer is a process that keeps monitoring the free space available in Buffer Cache. If at any time there is not enough free space in buffer cache ‘Lazy Writer’ writes the dirty pages in the buffer cache to the disk based on LRU(Least Recently Used) algorithm. Usually during every checkpoint the dirty pages in buffer cache are written to disk but in between 2 checkpoints if it is found that there is space issue or space needed for new pages to come then Lazy writer will clear the dirty pages from buffer cache and writes them to disk.

SQLOS:

It is a layer between SQL Server and Windows OS. It takes care of tasks like scheduling, memory management, buffer management, deadlock detection, exception handling, extended events and I/O within SQL. This provides an API (Application Program Interface) to different layers when required by the operating resources. In simple terms, SQLOS manages the resources within SQL Server same way a Windows OS does in general.

Please add in the comment section the other important components of SQL Server Architecture according to you, I will try to include them.

Thanks VV!!

SQL Server Cluster Interview Questions.

SQL Server AlwaysOn Availability group interview questions.

Login failed for user '' because the account is currently locked out. The system administrator can unlock it.

Issue: There are multiple entries in the SQL Server Error log. Also Applications started throwing errors.

Login failed for user 'TEST' because the account is currently locked out. The system administrator can unlock it.

First thing to confirm if the account actually got locked or not, verified properties of the SQL login and under the tab ‘Status’ and noticed “Login is locked out” check box was in ticked state. This confirms account got locked.

Here you can unlock the account by simply unchecking the “Login is locked out” check box  and it will solve the problem. But the catch is if you have ‘Enforce Password Policy’ already checked in ‘General’ tab of SQL login properties  then it will force you to reset the password while unchecking the “Login is locked out” check box .

If you want to UNLOCK the SQL login without resetting the password of SQL login we have 2 options

1.    One way is running below 2 commands:

ALTER LOGIN [TEST] WITH CHECK_POLICY = OFF;

ALTER LOGIN [TEST] WITH CHECK_POLICY = ON;

2.    Un-check the ‘Enforce Password Policy’ option in ‘General’ tab of login properties and click on ‘OK’. Once you do that login gets unlocked. Once the account gets unlocked you can check the ‘Enforce Password Policy’ option in ‘General’ tab again.

Thanks!!