September 22, 2010 at 5:28 am
Hi, I'm running some tests on SQL2008 re encryption. I've read some articles that tell me how to set this up and it works fine. I have found code that inserts using 'encryptbykey' so that when I open the key I can see the real data and when I close it, its just binary.
However if I use a standard insert statement on this table, like a user or app would do (ie without 'encryptbykey'), the insert works but the data can't be seen whether the key is open or closed, it's either binary or NULL. And I'm trying to view this as a SysAdmin!!
So my question is : If I have set up an encrypted table, how do I get the data from a 'basic' insert statement to be viewable if I open the key?? Hope this makes sense. Thank-you.
September 22, 2010 at 5:47 am
Your post is not very clear. When you use keys to encrypt data, the data is stored as binary (or varbinary). If you don’t use any encryption at all, then you can’t insert it into a binary column without converting it to binary. If you did insert it to binary column without encryption, then you need to cast or convert the data to its original data type. If you inserted it to a different type of column, then you shouldn’t see it as binary data. Can you pleas create a small script that shows your problem (e.g. create the table, insert data, select the data and explain where is the problems with the select statement’s results?)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 22, 2010 at 8:46 am
Thanks very much for responding to this Adi and your comments. As you can probably tell I am knew to encryption so perhaps am missing something obvious 🙂
Anyway, heres the code I've been testing with.
/******************************************************************
Create Symmetric Key
******************************************************************/
CREATE SYMMETRIC KEY [SecretSymmetricKey]
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE [CertSymmetricKey]
GO
/******************************************************************
Open the Symmetric Key for use
******************************************************************/
OPEN SYMMETRIC KEY [SecretSymmetricKey]
DECRYPTION BY CERTIFICATE [CertSymmetricKey]
GO
/******************************************************************
Insert ENCRYPTED data into the table CustomerCreditCards (with 'encryptbykey')
******************************************************************/
DECLARE @Key_Guid AS UNIQUEIDENTIFIER
SET @Key_Guid = key_guid( 'SecretSymmetricKey')
IF( @Key_Guid is not null )
BEGIN
INSERT INTO dbo.CustomerCreditCards
VALUES ( 1, encryptbykey( @Key_Guid, N'4111-1234-1234-5678'))
INSERT INTO dbo.CustomerCreditCards
VALUES ( 2, encryptbykey( @Key_Guid, N'4111-9876-7543-2100'))
INSERT INTO dbo.CustomerCreditCards
VALUES ( 3, encryptbykey( @Key_Guid, N'4111-9876-7543-2100'))
END
ELSE
BEGIN
PRINT 'Error retrieving key GUID'
END
GO
/******************************************************************
Select from the table. Data appears in binary format, as expected.
******************************************************************/
SELECT * FROM dbo.CustomerCreditCards
GO
/******************************************************************
Query the table again, but this time decrypt the data.
******************************************************************/
SELECT CustomerId,
convert( NVARCHAR(100), decryptbykey( CardNumber )) as 'CardNumber'
FROM dbo.CustomerCreditCards
GO
/******************************************************************
Close the symmetric key. Re-run the previous SELECT. Since the key isn't
open, the data comes back as a bunch of NULLs for the CardNumber column.
/******************************************************************
CLOSE SYMMETRIC KEY SecretSymmetricKey
GO
Now, the above is all fine and works as expected, but if I just wanted to use a standard insert into this table with the key open or closed ie
INSERT INTO dbo.CustomerCreditCards VALUES (3,'5551-9876-7543-2100')
The data is inserted ok, but you cannot view the cardnumber column whether you then open or close the key afterwards. The CardNumber is displayed as binary if you query it with a standard select or NULL if you use select with convert and 'decryptbykey'. So how do you ever get to see the real cardnumber?
If an application did thousands of inserts into this table using a standard insert, would you need to change it's code so it would insert using 'decryptbykey'.
Hope thats ok
Thanks very much
September 22, 2010 at 2:24 pm
Without seeing the table DDL, I'm guessing you're storing the encrypted values as varbinary. String values can easily be converted into binary values (each byte represents an ASCII character value) and stored in varbinary columns. However, string data cannot be implicitly cast into varbinary values. I'm surprised the non-encrypted insert attempt worked.
If you were to insert a non-encrypted value into the varbinary column by converting the string into binary, then the original string representation may be viewed by simply converting the binary value back to varchar:
DECLARE @test-2 table(id int, CCNum varbinary(64))
-- the next statement will fail, comment it out to run the full script
INSERT @test-2(id, CCNum)
SELECT 3, '5551-9876-7543-2100'
-- this will insert a char value into a binary column
INSERT @test-2(id, CCNum)
SELECT 3, convert(varbinary(64), '5551-9876-7543-2100')
-- display the stored binary and converted character representations of the data
SELECT CCNum AS BinaryVersion, CONVERT(varchar(64), CCNum) AS VarcharVersion FROM @test-2
Eddie Wuerch
MCM: SQL
September 23, 2010 at 3:15 am
Thanks very much Eddie, yes it is a varbinary column and yes I see what you mean about converting it on insert. It does seem to confirm though that I would need the app provider to make changes to their code if a standard insert (without conversion) was being used, yes?
September 23, 2010 at 4:53 am
if you're using cell encryption then you need to call the encryptby or decryptby methods for each insert\select. The app code would need to reflect this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 23, 2010 at 6:13 am
Better still put the SQL into a stored procedure, which can be encrypted as well. So you can put in a salt value as well. Therefore the external app just calls the sp and has no knowledge of the encryption routines or even needs to.
Richard....
http://www.linkedin.com/in/gbd77rc
September 23, 2010 at 6:17 am
Great stuff, thanks everyone!! 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply