T-SQL shortcuts and tips

The following T-SQL queries provide assistance or alternative methods to perform some of the examples shown in this document.

Creating a database

To create a database called TestDatabase.

USE master
GO
CREATE DATABASE TestDatabase;
GO

Creating a table

To create the following example table called TestTable within a previously created TestDatabase.

USE TestDatabase
GO
CREATE TABLE TestTable (FirstName varchar(MAX), LastName varchar(MAX),
NationalIdNumber varbinary(MAX), EncryptedNationalIdNumber varbinary(MAX),
DecryptedNationalIdNumber varbinary(MAX));
GO
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('Jack', 'Shepard', 156587454525658);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('John', 'Locke', 2365232154589565);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('Kate', 'Austin', 332652021154256);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('James', 'Ford', 465885875456985);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('Ben', 'Linus', 5236566698545856);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('Desmond', 'Hume', 6202366652125898);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('Daniel', 'Faraday', 7202225698785652);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('Sayid', 'Jarrah', 8365587412148741);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('Richard', 'Alpert', 2365698652321459);
INSERT INTO TestTable (FirstName, LastName, NationalIdNumber) VALUES ('Jacob', 'Smith', 12545254587850);
GO

Viewing a table

To view the previously created TestTable:

SELECT TOP 10 [FirstName]
,[LastName]
,[NationalIDNumber]
,[EncryptedNationalIdNumber]
,[DecryptedNationalIdNumber]
FROM [TestDatabase].[dbo].[TestTable]

To view the previously created TestTable with the NationalIDNumber in the original decimal form:

SELECT TOP 10 [FirstName]
,[LastName]
,CAST(NationalIdNumber AS decimal(16,0)) AS [NationalIDNumber]
,[EncryptedNationalIdNumber]
,[DecryptedNationalIdNumber]
FROM [TestDatabase].[dbo].[TestTable]

To view the previously created TestTable with the NationalIDNumber in the original decimal form, and also show the NationalIdNumber in VarBinary form:

SELECT TOP 10 [FirstName]
,[LastName]
,CAST(NationalIdNumber AS decimal(16,0)) AS [NationalIDNumber]
,(NationalIdNumber) AS VarBinNationalIdNumber
,[EncryptedNationalIdNumber]
,[DecryptedNationalIdNumber]
FROM [TestDatabase].[dbo].[TestTable]

Making a database backup

To make a database backup:

USE TestDatabase;
GO
BACKUP DATABASE TestDatabase
TO DISK = '<Drive>:\<Backup_directory>\TestDatabase_SomeState.bak'
WITH NOFORMAT,
INIT,
NAME = 'TestDatabase_SomeState Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

Where: <Drive>:\<Backup_directory> is the path to the directory to store the backup.

Adding a credential

The following query will add a credential to the database:

CREATE CREDENTIAL <credential name> WITH IDENTITY = '<protection domain id>',
SECRET = '<protection domain passphrase>' FOR CRYPTOGRAPHIC PROVIDER <provider name>;
ALTER LOGIN "<domain>\<login name>" ADD CREDENTIAL <credential name>;

Where

<credential name> is the name you wish to provide for the credential.
<protection domain id> matches the id of the protection domain.
<protection domain passphrase> matches the passphrase of the protection domain.
<provider name> is the name of the provider you are using.
<domain> is the relevant login domain.
<login name> is the relevant login name (to the database host).

Removing a credential

To remove a credential from the database:

ALTER LOGIN "<domain>\<login name>" DROP CREDENTIAL <credential name>;
DROP CREDENTIAL <credential name>;

Creating a TDEDEK

To create a TDEDEK using TestDatabase and dbAsymWrappingKey as an example:

USE TestDatabase;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY dbAsymWrappingKey;
GO

Removing a TDEDEK

To remove a TDEDEK using TestDatabase as an example:

USE TestDatabase
DROP DATABASE ENCRYPTION KEY;

Switching on TDE

To switch on TDE using TestDatabase as an example:

ALTER DATABASE TestDatabase SET ENCRYPTION ON;

Switching off TDE

To switch off TDE using TestDatabase as an example:

ALTER DATABASE TestDatabase SET ENCRYPTION OFF;

Dropping a provider

To drop an existing provider:

DROP CRYPTOGRAPHIC PROVIDER <provider name>

Where

<provider name> is the name of an existing provider.

Disabling existing providers

To disable all existing providers:

sp_configure 'show advanced options', 1; RECONFIGURE;
GO
sp_configure 'EKM provider enabled', 0; RECONFIGURE;
GO

Checking encryption state

To check the encryption state of your databases:

SELECT DB_NAME(e.database_id) AS DatabaseName, e.database_id, e.encryption_state, CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc, c.name, e.percent_complete FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint