Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) encrypts the data within the physical files of the database, the 'data at rest'. Without the original encryption certificate and master key, the data cannot be read when the drive is accessed or the physical media is stolen. The data in unencrypted data files can be read by restoring the files to another server. TDE requires planning but can be implemented without changing the database. Robert Sheldon explains how to implement TDE.

TDE Eligible SQL Server Editions

·        SQL 2016 Evaluation, Developer, Enterprise

·        SQL 2014 Evaluation, Developer, Enterprise

·        SQL Server 2012 Evaluation, Developer, Enterprise

·        SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter

·        SQL Server 2008 Evaluation, Developer, Enterprise

Implementation

Create Master Key

We must first create the master key. It must be created in the master database.

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertStrongPasswordHere';

GO

Create Certificate protected by master key

Once the master key is created along with the strong password (that you should remember or save in a secure location), we will go ahead and create the actual certificate.

CREATE CERTIFICATE TDE_Cert

WITH SUBJECT='Database_Encryption';

GO

After we run the CREATE CERTIFICATE statement, we can verify that the certificate has been created by querying the sys.certificates catalog view:

SELECT name CertName,certificate_id CertID,  pvt_key_encryption_type_desc EncryptType,  issuer_name Issuer

FROM sys.certificates WHERE issuer_name = 'TDE certificate';

Create Database Encryption Key

Now switch to the database that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database. Then we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption.

USE

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

GO

To check DEK

SELECT DB_NAME(database_id) DbName,encryption_state EncryptState,  key_algorithm KeyAlgorithm,  key_length KeyLength,  encryptor_type EncryptType FROM sys.dm_database_encryption_keys;

If any filegroups associated with the database are set as read-only, the encryption operation will fail. You’ll also run up against a number of restrictions when trying to implement TDE, such as not being able to drop a database during the initial encryption process.

 

Enable Encryption

Finally, we can enable encryption on our database by using the ALTER DATABASE command.

ALTER DATABASE

SET ENCRYPTION ON;

Go

Once the encryption is turned on, depending on the size of the database, it may take some time to complete. You can monitor the status by querying the sys.dm_database_encryption_keys DMV.

When you implement TDE on any user table, SQL Server also encrypts the tempdb database as all database stores its data in tempdb as well.

Disable TDE on the user database

At some point, you might decide that you want to disable encryption on a user database. The process is as simple as enabling it. You again run an ALTER DATABASE statement, only this time turning off the encryption,

ALTER DATABASE EmpData2

SET ENCRYPTION OFF;

Notice that the tempdb database is still encrypted. As it turns out, the database will stay encrypted until it is re-created, which occurs whenever the SQL Server service restarts.

 

Backup Certificate

It’s important to backup the certificate you created and store it in a secure location. If the server ever goes down and you need to restore it elsewhere, you will have to import the certificate to the server. In certain environments, the DR servers are already stood up and on warm/hot standby, so it’s a good idea to just preemptively import the saved certificate to these servers.

BACKUP CERTIFICATE TDE_Cert

TO FILE = 'C:\temp\TDE_Cert'

WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',

ENCRYPTION BY PASSWORD='InsertStrongPasswordHere')

Remember to store the certificate in a safe and available locations .

Restoring a Certificate

In order to restore the certificate, you will once again have to create a service master key on the secondary server.

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertStrongPasswordHere';

GO

Once that is done, you must remember where you backed up the certificate and the encryption/decryption password.

USE MASTER

GO

CREATE CERTIFICATE TDECert

FROM FILE = 'C:\Temp\TDE_Cert'

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

DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere' );

Once the certificate is restored to the secondary server you may restore a copy of the encrypted database.

Some things to note before applying TDE. There are some drawbacks. Remember that TDE encrypts the underlying database files including the backups. You can’t just take the files and dump them onto another SQL Server without the appropriate encryption keys and certificates. It does NOT allow for granular user level encryption. If that is the type of encryption you are looking for, you should investigate column level encryption.

Move a TDE Protected Database to another SQL Server using detach/attach or backup/restore method.

TLS 1.2 enabled issue with sql server 2012 and 2014

When you try to install Microsoft SQL Server 2012 or SQL Server 2014 on a server that has Transport Layer Security (TLS) version 1.2 enabled, you may encounter the following issues:

TSL is new and advanced version of SSL and mostly added to new OS systems which causing compatibility issue with sql server connection during and after installation. Versions of TLS — v1.1 and v1.2 are significantly more secure and fix many vulnerabilities present in SSL v3.0 and TLS v1.0.

• If the version of SQL Server that you're trying to install doesn't contain the fix to enable TLS 1.2 support, you receive the following error message:
  Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
• If the version of SQL Server that you're trying to install does contain the fix to enable TLS 1.2 support, you receive the following error message:
  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)
  In both of these situations, the installation fails.
   

Cause  :

This issue occurs because SQL Server Setup uses SqlClient for ADO.NET from the .NET Framework 2.0. By default, the .NET Framework 2.0 works with TLS 1.0.

To resolve this issue, install the hotfix rollup for the .NET Framework, and then restart the server. Based on the operating system and SQL Server version that you're running: eg : Hotfix rollup 3106993 for the .NET Framework 2.0 SP2 for Windows 2012 R2 and Windows 8.1.

 

To work around this issue, follow these steps:

Run the SQL Server 2012 or SQL Server 2014 Setup program, and update the SQL Server version to a build that supports TLS 1.2. (For more information about the updates that add support for TLS 1.2, see KB 3052404.)

Use slipstream method while using workaround method with Cumulative updated and sql full installation simultaneously, below are the sql updates that has compatibility with TLS 1.2

Cumulative Update 12 for SQL Server 2014 
Cumulative Update 5 for SQL Server 2014 SP1 
Cumulative Update 10 for SQL Server 2012 SP2
Cumulative Update 1 for SQL Server 2012 SP3

 

After this SQL server will start but won’t be able to connect locally using SSMS as client requires TLS1.0 and 1.1 should be enabled in below registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0]  

 If we don’t want to enable TLS 1.0 and TLS 1.1 for security reason the other option would be install .Net 4.6+ which can be found here and everything will work fine.

https://www.microsoft.com/en-us/download/details.aspx?id=48130

 

Slipstream service pack, patch or cumulative updates with sql installation for 2012 and 2014

Slipstream service pack, patch or cumulative updates with sql installation for 2012 and 2014:

There are two scenario when we want to install SQL Server from scracth :

1. There are service pack or updates which is required to add after installation.
2. Or sql installation fails with some error and to resolve the issue Microsoft has released some cumulative updates.

In both the scenario we can install sql with all updates simultaneously using slipstream method, because you don’t want to first install the RTM version of the product and then manually patch the instance with the Service Packs, Cumulative Updates or Hotfixes that you think are needed

The method would be:

1. Download SQL Server 2012/2014 media.
2. Download all the needed SPs, CUs and Hotfixes that you want to “embed” and put all of the .exe files in one directory. (make sure they are executable files not in zipped format(unzip if not done already))
3. Launch the setup.exe from CMD by issuing the command:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=”path_to_the_directroy_where_the_hotfixes_are”

 

So let’s assume SQL installation setup file is in D:\SQL2014\ folder and hotfixex could be in same directory

D:\ Fixes\      Or  keep updates in same software path to ease copy method in next machine D:\SQL2014\updates folder

We can go to command prompt change current path to software path (D:\SQL2014\) and run below command as per update path in updatesource parameter:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource="D:\Fixes"

Or

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=".\Updates"

No more it is required to manually start every single Service Pack then Cumulative update and Security Hotfix you need! Now you just put all of those in a folder and “point” SQL Server’s setup to that folder. 

Find service account and status of SQL Service using TSQL

Since SQL server 2008R2 we can use DMV (sys.dm_server_services) to get service account  information, It gives us information about the current state of the services related to SQL Server that are currently installed.

SELECT  servicename,

        startup_type_desc,

        status_desc,

        last_startup_time,

        service_account,

        is_clustered,

        cluster_nodename,

        filename,

        startup_type,

        status,

        process_id

FROM    sys.dm_server_services AS ;

 

In SQL 2008 R1 and 2005 there is way to use registry read command to get same information.

 

DECLARE @sa NVARCHAR(128);

EXEC master.dbo.xp_regread

    'HKEY_LOCAL_MACHINE',

    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',

    'ObjectName',

    @sa OUTPUT;

SELECT @sa;

Install SQL Server using configuration file?

SQL Server allows to generate configuration file and using this file we can deploy sql instance with same configuration for different server. We can also standardize manual for installing sql with batch files which will launch SQL setup.

Setup supports the use of the configuration file only through the command prompt. The configuration file overwrites the defaults in a package and Command-line values overwrite the values in the configuration file

The configuration file can be used to track the parameters and values for each installation. This makes the configuration file useful for verifying and auditing the installations.

The ConfigurationFile.ini file is a text file with parameters (name/value pair) and descriptive comments.

To generate configuration file we need to start setup using media (setup.exe) till ready to install page.

The path to the configuration file is specified in the Ready to Install page in the configuration file path section.

Then cancel the setup,

Normally file location would be : File Location and Name: %programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\\ConfigurationFile.ini.

To see the progress of installation during setup using ConfigurationFile.ini we can manually edit the file and set the parameter INDICATEPROGRESS to TRUE and QUITESIMPLE to TRUE.

NB: In ConfigurationFile.ini sa password not be included even if you have entered while creating file, we must add parameter SAPWD=”Password” below SECURITYMODE=SQL in file to include sa password or we can add later after installation done.

To install the SQL=>

Copy ConfigurationFile.ini to setup folder where setup.exe is and use command prompt to use syntax from setup folder:

ð  Setup / ConfigurationFile= ConfigurationFile.ini

To specify passwords at the command prompt instead of in the configuration file:

ð  Setup.exe /SQLSVCPASSWORD="*****" /AGTSVCPASSWORD="*****" /ASSVCPASSWORD="*****" /ISSVCPASSWORD="*****" /RSSVCPASSWORD="******”  /SECURITYMODE=SQL /SAPWD ="******” /ConfigurationFile=MyConfigurationFile.INI

Page File for SQL Server

Page File for SQL Server

Page file is space allocated on system drive as a file and its functionality is: operating system works with memory and paging file. The page file is located on the disk, and when there is not enough physical memory left, operating system can use the page file and move data from the physical memory to it. When it will need that data, it will have to read it from the disk. The problem is that disk is a lot slower than the physical memory. If SQL Server's cache will be stored on the disk instead of the on the physical memory, you'll have a huge performance problem.

In general page file is physical extension of RAM and if not enough memory left during process page files save server from crash or being hung out.

Maximum memory space required by windows commit limit will be: RAM size + page file size

Normally a DBA set page file to 2X of RAM size which is not always correct and it depends over server functionality. For 32 bit size maximum page size should be 4 GB.

But for modern server we should have less page file size if RAM size is more, we can use performance counter to know the value of page file.

Create counter Memory\Committed Bytes and set it for one week and once we have data calculation would be

Max value if committed byte over period (20 GB) + 30% of committed byte as buffer – RAM Size.

For 16 gb Ram = data would be 20+6-16 = 10 gb pf page file

+  we also need to consider kernel or memory dump size which also store data primarily on page file before moving it to specified dump folder.

From Windows 2008 and onward we can put page file on any partition, not only system partition as before 2008. If page are created along multiple drives sql will generally use page file from faster drive.

To configure page file setting: go to:

 system->advanced system setting->advanced->setting under performance tab->Advanced

Database Backup Myth

1.       backup operations cause blocking

No. Backup operations do not take locks on user objects. Backups do cause a really heavy read load on the I/O subsystem so it might *look* like the workload is being blocked, but it isn't really. It's just being slowed down. There's a special case where a backup that has to pick up bulk-logged extents will take a file lock which could block a checkpoint operation – but DML is never blocked.

2.       switching from the FULL recovery model to the BULK_LOGGED recovery model and back again breaks the log backup chain.

No. It just doesn't. Switching from either FULL or BULK_LOGGED to SIMPLE *does* break the log backup chain however.

3.       breaking the log backup chain requires a full backup to restart it

No. You can restart the log backup chain with either a full or differential backup – anything that bridges the LSN gap from the point at which the log backup chain was broken.

4.        Concurrent log backups are not possible while a full or differential backup is in progress.

No,  In SQL Server 2000, a concurrent log backup with either a full or diff backup (I’ll just say ‘full’ from now on but take it to mean ‘full or diff’) was not permitted. The reason is that a log backup would clear the inactive portion of the log once it’s been backed up, but a full backup may still need some of that log so it can’t be cleared

In SQL Server 2005, the restriction was lifted, but there’s a subtle twist. You can do concurrent log backups with full BUT the log is not cleared when the log backup ends. The clearing of the inactive portion of the log is delayed until the full backup completes. This could cause you to have disk space problems if your log generation rate is huge and you’re relying on very frequent backups to manage the log size.

5.       A full or differential backup clears the log.

No. A log backup includes all the log since the last log backup – nothing can change that – no matter whether that log was also backed up by a full or differential backup. In the FULL or BULK_LOGGED recovery models, the only thing that clears the log is a log backup.

6.       Using the BULK_LOGGED recovery model for minimally-logged operations reduces the size of the next transaction log backup

No. A minimally-logged operation is so-named because only the page allocations are logged. A log backup needs all the information necessary to reconstitute the transaction, so a log backup following a minimally-logged operation must backup the log plus all extents changed by the minimally-logged operation. This will result in the log backup being roughly the same size as if the operation was fully logged.

7.       Full and differential backups only contain the log generated while the backup was running

No. A full or differential backup contains enough log to be able to recover the database to a transactional consistent view of the database at the time the data-reading portion of the backup finished (or as far back as the oldest log record that transactional replication has not yet processed – to ensure that replication works properly after a restore).

8.       Backups always test existing page checksums.

No. It only does it when you use the WITH CHECKSUM option – which you should.

9.       Backups read data through the buffer pool.

No. The backup subsystem opens its own channels to the database files to avoid the performance hit of having to read everything into SQL Server's memory and back out to the backup device (and also effectively flushing the buffer pool in the process). If you ask for the page-checksum checking, it uses its own small portion of memory.

10.   Backups perform consistency checks (a la DBCC CHECKDB)

No.

11.   If the backup works, the restore will too

No. You must regularly validate your backups to give yourself a high level of confidence that they will work if a disaster occurs. 

12.   A mirrored backup will succeed if the mirror location becomes unavailable.

No. If any one of the paths to a mirrored backup fails, the entire mirrored backup operation fails.

13.   A tail-of-the-log backup is always possible.

No. A tail-of-the-log backup is one that backs up all the log generated since the last log backup, in an exceptional situation. If the data files are damaged, you can still do a tail-of-the-log backup EXCEPT if the un-backed-up log contains a minimally-logged operation. That would require reading data extents – which cannot be done if the data files are damaged. For this reason, the BULK_LOGGED recovery model should not be used on databases that have 24×7 user transactions.

14.   Backup a database snapshot possible.

No. It's not implemented

15.   Log backups will be the size of the log.

No. The log has to accommodate the space necessary to roll back active transactions, the amount of space returned by DBCC SQLPERF (LOGSPACE) on a busy system doesn't accurately reflect the amount of log records in the log. log backup is just all the log generated since the last log backup – not the whole log file usually.

16.   Backup a corrupt database not possible.

No. In most cases you can use the WITH CONTINUE_AFTER_ERROR option to back up the corrupt database.  If that fails (maybe because of a damaged boot page or file-header page), there are no other options apart from OS-level file backups.     

       

17.   Log backups always clear the log.

No. If there's no concurrent data backup running, a log backup will always *try* to clear the log, and only succeed in clearing the inactive portion of the log – the log that's only considered 'required' by SQL Server because it hasn't yet been backed up. If anything else is holding the log 'required', it cannot be cleared, even though it has been backed up. Subsequent log backups will check again and again until the time comes when that portion of the log can be cleared.

Also, if there is a concurrent data backup running, the log clearing will be delayed until the data backup finishes. See the blog post in myth 30-04 for more details.

18.  You can back up a mirror database.

No. A mirror database is not accessible except through a database snapshot. And you can't back up that either. But it has changed in New feature in Always On where backup is permitted for secondary database.

19.  Back up a single table is possible.

No. You can effectively back up single table if it happens to be wholely contained on a single filegroup, but there's no way to say BACKUP TABLE (except export/import method).

20.   My transaction is guaranteed to be contained in the backup if it committed before the backup operation completed.

No. The commit log record for the transaction has to have been written out before the data-reading portion of the backup finished.

21.    You should shrink the database before a backup to reduce the backup size.

No. Shrink just moves pages around so won't make any difference.

22.   You don't need to back up master, msdb, model.

No. You should always back up the system databases. Master contains all the security info, what databases exist – msdb contains all the SSIS packages, Agent jobs, backup history – model contains the configuration for new databases. Don't fall into the trap of only backing up user databases otherwise you'll be in a world of hurt if you have to do a bare-metal install.

23.   Transaction log and differential backups are possible for the master database in full recovery model.

We cannot take transaction log or differential backups of the master database. This has been configured because the master database cannot be in a restoring mode as this database is essential for your SQL Server to be online. Thus we can only take full and copy only backups for the master database. We can change the recovery model of master database from simple to bulk logged or full. But log and diff backup possible for Model and MSDB database.

24.   We can take backup of tempdb database and we can change the recovery model of the database.

We cannot take backup of tempdb database nor we can change the recovery model of the database. The recovery model of tempdb will always be simple.

25.    The native SQL Server 2005 backups are in clear text.

True before SQL 2008 - With SQL Server 2008 is the introduction of natively encrypted database backups.

Prior to SQL Server 2008 a third party product was necessary to encrypt the database backups.

26.    We can stop SQL Server successful backup writing to the SQL Server Error Log.

A trace flag 3226 has been introduced that allows you to turn off logging of all successful backups in your SQL Server error logs. DBCC TRACEON (3226,-1) will turn it up globally.