Database Backup Encryption

Starting from SQL 2014, we can encrypt the database backups without encrypting the actual database files. Prior to SQL 2014, the only way to encrypt the database backups using native SQL options is by enabling Transparent Database Encryption(TDE) on the database – which basically encrypts the database files at rest and this encrypts the backups as well. Obviously, enabling TDE is a bit overkill if you want to encrypt just the backups. This issue is addressed in SQL 2014 where backups can be encrypted without enabling TDE.

Below is a simple step by step procedure to encrypt backups in SQL 2014 and later.

1. Create Master Key.
2. Create Certificate.
3. Backup the Certificate and Private Key.
4. Use the Certificate to backup the database.
5. Create Certificate on the target server.
6. Restore the database on the target server.

--Step 1:
Create Master key Encryption by Password ='SQL2016Master'
--Step 2:
Create Certificate CW_SQL With Subject='Certificate'
--Step 3:
Backup certificate CW_SQL to file ='C:\Temp\CW_SQL.Cert'
with Private Key
(
File = 'C:\Temp\CW_SQL_PrivateKey.pvt',
Encryption by password='SQLBackup'
)
--Step 4:
BACKUP DATABASE [TestDB] TO DISK = N'C:\Temp\TestDB.bak' WITH FORMAT, INIT, MEDIANAME = N'TestDB Backup',
NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [CW_SQL]), STATS = 10, CHECKSUM
--Step 5: on target server
Create certificate CW_SQL from file ='C:\Temp\CW_SQL.Cert'
with Private Key
(
File = 'C:\Temp\CW_SQL_PrivateKey.pvt',
Decryption by password='SQLBackup'
)
--Step 6: On target server
Restore Database TestDB from Disk='C:\Temp\TestDB.bak' with Recovery

When creating the certificate on the target server, the certificate name is not important – you can create the certificate with any name but the thumb print is what will be validated before restoring the database. So, as long as the certificate is created from the same cert\private key source combination even with a different name, it will work.

By default, all certificates created are valid for one year unless explicitly mentioned in the create certificate statement. While expired certificate cannot be used to backup the database, it can still be used to restore the database.
This also means expired certificates can be restored(created) should they be needed to restore the database.In fact, expired certificates will not be shown in the UI dropdown to encrypt the backups.

Without creating the certificate first, none of the restore arguments work to get the details on the backup file. So, commands like restore headeronly, restore verifyonly, restore filelistonly do not work without the certificate.

Also, along with backing up the certificate and private key, you should also backup the private key encryption password, otherwise the certificate cannot be recreated on the target server.

Advertisement

SQL Server Audits

SQL Server has built in capability to audit certain user actions both on server level and database level. This is a useful feature as it helps to log user actions such dropping objects,changing permissions on the objects\database etc without having to build a customize auditing solution.
--Query to find the events logged by Server Audit.
select a.Audit_ID as [ServerAuditID],A.Name as [ServerAuditName],B.Name as [ServerAuditSpecificationName],C.Audit_action_id as [Audit_ActionID],C.Audit_action_Name as [Audit_ActionName],D.action_id as [Sub_ActionID],D.Name as [Sub_ActionName],E.database_name,statement,object_name
from sys.server_audits A
INNER JOIN sys.server_audit_specifications B on A.audit_guid=B.audit_guid
INNER JOIN sys.server_audit_specification_details C on B.server_specification_id=C.server_specification_id
INNER JOIN sys.dm_audit_actions D on D.covering_action_name collate SQL_Latin1_General_CP1_CI_AS=C.Audit_Action_Name
INNER JOIN fn_get_audit_file('C:\Users\sqlwhisper\Documents\SQL\ServerAudit_12663303-53EF-4FFD-BBC5CCC0AA4EE477_0_130711508902400000.sqlaudit',default,default) E
on E.action_id=D.action_id

Read UnCommitted Isolation

IF Exists(Select 1 from sys.objects where name='foobar' and type='U')
Begin
Drop Table foobar
End
Create Table foobar (sno int,sname varchar(20))
Insert into foobar
Values(1,'Spurs'),(2,'Rockets'),(3,'Pelicans')
–Session 1
Begin Tran
Update foobar set sname='Bobcats' where sno=3
Waitfor Delay '00:00:03'
rollback
–Session 2
Set Transaction Isolation Level Read Uncommitted
Select * from Foobar–Dirty Read here.
Waitfor Delay '00:00:05'
Select * from Foobar

SQL Query to Find SQL Cluster Nodes

Below is the SQL Query to find the sql cluster nodes

If(serverproperty('IsClustered'))=1
Begin
SELECT @@servername as DatabaseServer,NodeName,
case when serverproperty('instancename') is null then 'Default Instance - MSSQLSERVER' Else serverproperty('instancename') End as InstanceName,
Case when serverproperty('Computernamephysicalnetbios')=NodeName then 'Active' else 'Passive' End as NodeStatus
FROM sys.dm_os_cluster_nodes
order by Case when serverproperty('Computernamephysicalnetbios')=NodeName then 'Active' else 'Passive' End
End
Else
Begin
Select 'This is database Server is not clustered.'
End

Sample Output :