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. If you are using a database failover cluster this will be relative to the active server.

Adding a credential

The following query will add a credential to the database:

CREATE CREDENTIAL <loginCredential> WITH IDENTITY = '<Credential name>', SECRET = '<Credential
passphrase>' FOR CRYPTOGRAPHIC PROVIDER
<Name of SQLEKM provider>;
ALTER LOGIN "<Domain>\<Login name>" ADD CREDENTIAL <loginCredential>;

Where

  • <loginCredential> is the name you wish to provide for the credential.

  • <Credential name> is the name of the OCS or softcard you wish to use as a credential.

  • <Credential passphrase> is the passphrase of the OCS or softcard you wish to use as a credential.

  • <Name of SQLEKM provider> is the SQLEKM provider name 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 <loginCredential>;

See Adding a credential for terms used.

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 SQLEKM Provider

To drop the services of an existing SQLEKM Provider:

DROP CRYPTOGRAPHIC PROVIDER <Name of SQLEKM provider>

Where

  • <Name of SQLEKM provider> is the name of an already existing SQLEKM Provider.

Disabling SQLEKM Provision

To disable the EKM provision in a SQL Server installation. This will disable all EKM providers:

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

Resynchronizing in an availability group

To resynchronize a database called 'SourceDatabase' in an availability group, try:

USE master;
GO
ALTER DATABASE [SourceDatabase] SET HADR RESUME

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