Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Symmetric Keys

One of the encryption options in SQL Server is symmetric key encryption. This is the type of encryption most people think about when they consider encrypting data. In symmetric key encryption, we use a key to encrypt data, and then also use a key to decrypt data.

The key used for encryption and decryption is the same in Symmetric Key Encryption. This is why we call this symmetric. Just like with a house lock,

image

the key that locks (encrypts) also unlocks (decrypts). This is a picture of my front door and the lock uses a single key.

In SQL Server, we create a symmetric key and use that to encrypt data and also decrypt it. Here’s a simple example:

DECLARE @plain VARCHAR(200), @cipher VARBINARY(5000), @decrypt VARCHAR(200) SELECT @plain = 'This is the plain text.' -- encrypt SELECT @cipher = ENCRYPTBYKEY(key_guid('MyFirstSymKey'),cast(@plain as NVARCHAR(200))); SELECT 'Plain' = @plain , 'Cipher' = @cipher -- decrypt SELECT @decrypt = CAST( DECRYPTBYKEY(@cipher) AS nVARCHAR(200)) SELECT 'Plain' = @plain , 'Cipher' = @cipher , 'decrypt' = @decrypt

 

That’s it. If you run it, you see the original text, the encrypted text, and the decrypted text.

 

symkey1

 

In another post, I’ll go into more options that are available for symmetric key encryption.


Filed under: Blog Tagged: encryption, sql server, syndicated

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...