Encryption & Decryption

Database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.

We got the code working for the seond case but for the first, WHEN:

CREATE SYMMETRIC KEY sym_Key WITH ALGORITHM =
AES_256 ENCRYPTION BY ASYMMETRIC KEY asym_Key
GO

THEN:

-- Msg 15314, Level 16, State 1, Line 1
-- Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.

What can be the way out to be able to create the AES 256 Symmetric key.

AES is currently supported by SQL Server 2005 only on Windows 2003. If you are using Windows 2000 or Windows XP, you do not have access to AES encryption from SQL Server.

Thanks
Laurentiu

sql

Encryption - importing keys

Is it possible to load data into MSSQL 2005 that has been encrypted externally with a symmetric key algorithm, such as AES, and then import the key to SQL Server? After browsing through Books Online, I don't see any way to import a symmetric key from an external file, but maybe I'm missing something.

No, at this time, it is not possible to do that. You would have to decrypt the data and then encrypt it again using SQL Server's encryption functions.

Thanks
Laurentiu

|||

If your primary concern is protecting data in transit, you should be able to use SSL to accomplish this:

http://msdn2.microsoft.com/en-us/library/ms189067.aspx

Sung

EncryptByKey Function always returning null

When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption:

ALTER PROCEDURE [dbo].[ProcMyProc](@.ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';

Declare @.Temp varbinary(8000);

Set @.Temp =EncryptByKey(Key_GUID('MyKey'),@.ClearText);

close symmetric key MyKey;

select @.Temp as temp;
END

The result I get for this procedure is null. Is there something wrong with this code?

Hey Muhammad,

I tried out your code and it seems to work for me. Can you try executing your script outside of the proc? You can add in "select * from sys.openkeys" after your open statement just to check to see if the key was actually opened.

Sung

|||

Also, if this still doesn't work, can you let us know what OS version you are using? Is it XP, 2000, or 2003?

Thanks
Laurentiu

|||I found the cause of the problem. I was misspelling the name of the key when passing it to the Key_GUID() function. but shouldn't this give an error saying there's no key with this name instead of just returning null?|||

Hi Muhammad,

The way our built-ins work is that they return null instead of returning an error. I don't remember why exactly this is the case, but I think it has to do with backwards compatibility and parsing logic of the built-ins.

Sung