SQL Server EncryptByCert weirdness

On a SQL Server 2005 box, given that I have already created a key and a certificate for encryption usage...

CREATE CERTIFICATE [cert_Employee]
    WITH SUBJECT = 'EmployeeTable_SSN protection'

CREATE SYMMETRIC KEY [key_Employee]
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE [cert_Employee]

and the following definitions...

CREATE TABLE [dbo].[EmployeeTemp](
 [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
 [LastName] [varchar](50) NULL,
 [SSN] [varchar](11) NULL,
 [SSNe_Function] [varbinary](256) NULL,
 [SSNe_NoFunction] [varbinary](256) NULL,
CONSTRAINT [PK_EmployeeTemp] PRIMARY KEY CLUSTERED 
(
 [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]

GO

CREATE FUNCTION [dbo].[EncryptSSN]
(
 @dataToEncrypt VARCHAR(11)
)
RETURNS VARBINARY(256)
WITH EXECUTE AS 'DBO'
AS
BEGIN
 RETURN EncryptByCert(CERT_ID('cert_Employee'), @dataToEncrypt)
END

GO

INSERT INTO dbo.EmployeeTemp (LastName, SSN) VALUES ('GOOGLE', '123-45-6789')

Why would the following statement generate two different values:

UPDATE dbo.EmployeeTemp SET SSNe_Function = dbo.EncryptSSN(SSN), SSNe_NoFunction = EncryptByCert(CERT_ID('cert_Employee'), SSN)

SSNe_Function Value: 0x88A5867E63F0026EEDD077561A29E8AE9EC14B0EC536A75688F5C9A7D31A119514A5204127A73935682640E0D1FBE853B2C39CC94CC42C1FE40975DA6463FD6E70169C2EF88F48317D60ACE45085E33A4785FCD8811E52287AC0C3930B0E66D8F137B04B4F670AEDD36EEF4C58467F80A8B6B010F80A61CA220AAD385E103AB7

SSNe_NoFunction Value: 0x440DC2F3A737E87194452BDFA2989BC070057B944196E88CDF7DAE7104C5F36D7CE021411FC85EB067B7BE4DC7779E2BD1495CE6258057F908A0F14A34BE202E530E127D318E768239232FB882B60A23FBFE5108B3EA94AEB4875D12B2948E4443CE80799D2DDAE7A349F94E7E1C15A6C48AD866F27E23E240699C8102DB1E36

ansaurus

tags:

views:

answers:

SQL Server EncryptByCert weirdness

related questions