ok I see that works;

With my example values, I also saw that SQL DES and TRIPLE_DES are not interchangable with the results from your function;

I think you'll have to wrap it into a CLR like my example suggested; you might have to make sure adding a CLR is OK at your biz as well.

You might also consider switching to the much stronger cyphers in SQL Server and replacing what you currently use.

my test code: when i compared the script results, they are nowehere near the values yours puts out for the same info.

unless i'm doing it wrong, that is.

--t13Oh9nvDTh3Ac+l9nUQqA==

--Encryption Test

--If there is no master key, create one now.

IF NOT EXISTS

(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'

GO

-- create symmetric key 'SecureSymmetricKey'

-- using the DESX encryption algorithm

-- and encrypt the key using the password

-- 'StrongPassword'

CREATE SYMMETRIC KEY SecureSymmetricKey

WITH ALGORITHM = DES

ENCRYPTION BY PASSWORD = N'Password';

-- must open the key if it is not already

OPEN SYMMETRIC KEY SecureSymmetricKey

DECRYPTION BY PASSWORD = N'Password';

CREATE SYMMETRIC KEY SecureSymmetricKey2

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY PASSWORD = N'Password';

-- must open the key if it is not already

OPEN SYMMETRIC KEY SecureSymmetricKey2

DECRYPTION BY PASSWORD = N'Password';

-- declare and set varible @STR to store plaintext

DECLARE @STR NVARCHAR(100)

SET @STR = N'Encryption Test';

-- declare and set varible @encrypted_str to store

-- ciphertext

DECLARE @encrypted_str VARBINARY(MAX)

SET @encrypted_str =

EncryptByKey(Key_GUID('SecureSymmetricKey'), @STR);

Print @encrypted_str

SET @encrypted_str =

EncryptByKey(Key_GUID('SecureSymmetricKey2'), @STR);

Print @encrypted_str

/*

--CLEANUP

CLOSE SYMMETRIC KEY SecureSymmetricKey2

CLOSE SYMMETRIC KEY SecureSymmetricKey

DROP SYMMETRIC KEY SecureSymmetricKey2

DROP SYMMETRIC KEY SecureSymmetricKey

*/