CODEWITHSUNDEEP
sql-serverencryptionsql-server-express
Stelios
Stelios

Reputation: 340

SQL Server certificate and symmetric key

I had the need to encrypt/decrypt some personal information in my database such as credit card no and passwords.

Following the steps from Microsoft I was successfully encrypt & decrypt the data.

But after some test I have questions.

Assume someone gets unauthorized access to the database (hack) and can see the database in SSMS from his own computer. Surly he can see that I had some certificate and symmetric key setup.

In order to display the data the examples shows the following query

OPEN symmetric KEY symmetrickey1 
decryption BY certificate certificate1
Now list the original ID, the encrypted ID  
SELECT customer_id, 
   credit_card_number_encrypt                                 AS 
   'Encrypted Credit Card Number', 
   CONVERT(VARCHAR, Decryptbykey(credit_card_number_encrypt)) AS 
   'Decrypted Credit Card Number' 
FROM   dbo.customer_data; 
-- Close the symmetric key 
CLOSE symmetric KEY symmetrickey1;

If the above query returns the data and can see the credit card how can then be protected? Shouldn't it be necessary to pass a password somewhere in the query?

I am sure that I miss something here. Can anyone explain this to me?

Upvotes: 1

Views: 2209

Answers (1)

Stelios
Stelios

Reputation: 340

i had to include a password to the cerificate

CREATE CERTIFICATE Certificate1
Encryption By Password='Password123'
WITH SUBJECT = 'Protect Data'

for retrieving data 

OPEN Symmetric KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1 With Password='Password123'
;
select *,CONVERT(nvarchar(255),DECRYPTBYKEY(Credit_Card_No)) as
[Credit_Card_No3] from Customers
;
Close Symmetric Key SymmetricKey1

Upvotes: 1

Related Questions