SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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,


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.




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

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

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


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

Loading comments...