Create Always Encrypted Certificate

By bradyupton in Always Encrypted, Auditing and Compliance, Database Design, Security 0 Comment

In SSMS, browse to Database, Security, Always Encrypted Keys, right click Column Master Keys and click New Column Master Key:

In the New Column Master Key box you can choose from several key stores. The most popular stores are Current User or Current Machine. See below for description.

Local machine certificate store
This type of certificate store is local to the computer and is global to all users on the computer. This certificate store is located in the registry under the HKEY_LOCAL_MACHINE root.

Current user certificate store
This type of certificate store is local to a user account on the computer. This certificate store is located in the registry under the HKEY_CURRENT_USER root.

Be aware that all current user certificate stores inherit the contents of the local machine certificate stores. For example, if a certificate is added to the local machine Trusted Root Certification Authorities certificate store, all current user Trusted Root Certification Authorities certificate stores also contain the certificate.

In this example, we’ll use Local Machine. Type a name for the Master Key, choose Key Store and click Generate Certificate.

Click OK and a new Column Master Key will appear:

Do the same for any other master keys you would like to create. For example, Developer:

To create a Column Encryption Key, right click on Column Encryption Key and choose New Column Encryption key:

Give the key a name and select the Column Master Key that you would like to associate it with:

Do the same for the Developer keys. You should have two column master keys and two column encryption keys:

To view Local Machine certificates:

Open Certlm.msc

Browse to Personal, Certificates:

Double click a certificate and go to the Detail tab where you will see the thumbprint:

Match this thumbprint with the Column Master Key in SSMS:

Right click the certificate in Certlm.msc and choose Properties. Enter a friendly name to identify which certificate belongs with the Column Master Key.

To Export and certificate, right click and choose All Tasks, Export:

SQL Server User Defined Server Roles

By bradyupton in Security 0 Comment

A new feature to SQL Server 2012 is the ability to create user defined server roles and assign server level/scope permissions to these roles. DBA’s have always had the ability to create user defined database roles which act as a security layer at the database level, but we’ve never been able to create roles at the server level until SQL Server 2012.

In this post I will show you how to create user defined server roles using T-SQL and SQL Server Management Studio.

What Permissions Can Be Assigned

First, to view the list of permissions that can be assigned to a user defined server role run the following query:

USE master 
GO
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
WHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE') 
ORDER BY class_desc, permission_name
GO

Create a Server Role in T-SQL

To create a server role called “juniordba” use the following:

USE master
GO
CREATE SERVER ROLE juniordba

Next we will create a login called Brady and then add it to the new juniordba role that was created:

USE master 
GO
ALTER SERVER ROLE juniordba ADD MEMBER Brady

We haven’t added any permissions to the server role, so Brady shouldn’t have access. To test this we can login as Brady and run the following query:

SELECT * FROM sys.dm_exec_connections

As you can see we get the following error message:

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

Click here to view the rest of this post.

Securing and protecting SQL Server data, log and backup files with TDE

By bradyupton in Security 0 Comment

In this post I’ll show you how to setup Transparent Data Encryption (TDE). TDE is new in SQL Server 2008 and serves as an encryption method that uses a database encryption key (DEK) to protect SQL Server’s data and log files. The DEK is a key secured by a certificate stored in the master database.

To setup TDE we’ll need to run a few scripts: (My test database is named TDE)

The following script will create the master key with a specified password ElephantRhin0:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0';
GO

Next, we’ll create a certificate named TDECert that will be protected by the master key:

USE master;
GO
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
GO

After creating the certificate we’ll backup the certificate to a specified source:

USE master;
GO
BACKUP CERTIFICATE TDECert TO FILE = 'C:\TDECert_backup' WITH 
PRIVATE KEY ( FILE = 'C:\TDECert_key' ,ENCRYPTION BY PASSWORD = 'ElephantRhin0' )
GO

Once the certificate is backed up we will create the DEK using the AES algorithm and protect it by the certificate:

USE TDE;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

The final step is to set our database to use encryption:

ALTER DATABASE TDE
SET ENCRYPTION ON;
GO

If everything completed successfully then we have officially encrypted our database with TDE, but don’t take my word for it, run the following query to confirm:

SELECT name, is_encrypted
FROM sys.databases
WHERE name = 'TDE'

SQL Freelancer SQL Server Transparent Data EncryptionClick here to view the rest of this post.