SQL server 2005 ENCRYPTBYKEY returns null

Question

DECLARE @EmailEnc varbinary(maX)
declare @keyid uniqueidentifier
DECLARE @Email_test nvarchar(max)
set @Email_test = N'Sg@hotmail'
SELECT top 1 @keyid=[key_guid] FROM sys.symmetric_keys order by newid()
SET @EmailEnc=CAST(ENCRYPTBYKEY(@keyid,@Email_test) AS varbinary(max))
select @Email_test,@keyid,@EmailEnc

Results

---

Sg@hotmail D790AE00-63FC-4BC5-8182-073BACDF1B12 NULL (1 row(s) affected) Why Null ?? any ideas??

Answer 1

You must "open" the key before using it. An example is given in Books Online for how to do this.