November 5, 2008 at 3:34 am
I have a column, which needs to be encrypted.
I am using a Simple symmetric encryption using a Triple_Des algorithm.
I am unable to get the proper data after decryption, I dont know what is the problem.
Some symbols are appearing in the result.
Here is my query:
--------------------------------------------
create table #tempp
(
Id int IDENTITY(1,1),
[profile] varchar(max)
)
insert into #tempp values
('Dr. AbcD. Pillrtr, MD is Professor and Chairman at the Department of Anesthesia and Perioperative Care, and Professor of Cellular and Molecular Pharmacology, University of Ciuehihih, San Francisco (UCSF), School of Medicine. He is in the Internal Advisory Committee, for the Program Project Grant, "Integrative Study of Brain Vascular Malformations" at Centre of Cerebrovascular Research at UCSF. He is amongst the most accomplished and influential anesthesiologists in the world. He is editor of the eponymous "sgdfgser’s Anesthesia," the definitive textbook for Anesthesia. He has been the Editor-in-Chief of Anesthesia & Analgesia from 1991-2006, during which time he was elected into the Institute of Medicine of the National Academies of Science. Dr Miller"s research focuses on clinical neuromuscular pharmacology and blood transfusion. He is the elected member of the Institute of Medicine, National Academy of Sciences. He has been Chairman of FDA Committee on Anesthetic and Life Support Drug Advisory Committee and Member of FDA Blood and Blood Products (when blood was discovered to transmit HIV (AIDS).')
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxL969#ghf0%'
--If there is no Certificate, create one
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name='KOLProfile037')
CREATE CERTIFICATE KOLProfile037
WITH SUBJECT = 'KOL Details';
--If there is no Symmetric Key, create one
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'KOLSymmetricKey')
CREATE SYMMETRIC KEY KOLSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE KOLProfile037;
go
OPEN SYMMETRIC KEY KOLSymmetricKey
DECRYPTION BY CERTIFICATE KOLProfile037;
go
alter table #tempp
ADD EncryptedProfile varbinary(4000);
go
Update #tempp
Set EncryptedProfile = EncryptByKey(Key_GUID('KOLSymmetricKey'), [profile])
where Id = 1
go
OPEN SYMMETRIC KEY KOLSymmetricKey
DECRYPTION BY CERTIFICATE KOLProfile037;
go
select [Profile],
CONVERT(nvarchar, DecryptByKey(EncryptedProfile)) EncryptedProfile
from #tempp
go
drop table #tempp
-----------------------------------
Please help me.
November 5, 2008 at 5:30 am
Here is my query:
--------------------------------------------
create table #tempp
(
Id int IDENTITY(1,1),
[profile] varchar(max)
)
OPEN SYMMETRIC KEY KOLSymmetricKey
DECRYPTION BY CERTIFICATE KOLProfile037;
go
select [Profile],
CONVERT(nvarchar, DecryptByKey(EncryptedProfile)) EncryptedProfile
from #tempp
go
I got the answer. I was using a different datatype while decrypting.
Datatype should be same for both encrypting and decrypting.
OPEN SYMMETRIC KEY KOLSymmetricKey
DECRYPTION BY CERTIFICATE KOLProfile037;
go
select [Profile],
CONVERT(nvarchar, DecryptByKey(EncryptedProfile)) EncryptedProfile
from #tempp
go
My question is, how safe is the encrypted data, since we can easily decrypt it once we know the Symmetric Key and the Certificate?
Or is it not as simple as it looks?
Please help.
November 5, 2008 at 10:20 am
sg (11/5/2008)
My question is, how safe is the encrypted data, since we can easily decrypt it once we know the Symmetric Key and the Certificate?
Or is it not as simple as it looks?
Please help.
Remember that not all users will have rights to open the symmetric key. If they don't have rights, they can't open the key and they can't decrypt the data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2008 at 10:07 pm
Thanks for the reply.
GilaMonster (11/5/2008)
Remember that not all users will have rights to open the symmetric key.
Thats fine. What If somebody hacks the database and access the mdf file, in that case they can easily decrypt the data. I dont know, but the chances are there i guess.
Please comment.
November 6, 2008 at 8:55 am
Sure they can. Encryption is not designed to protect the mdf file (secure your server), not is it mean to protect against the DBA (If you don't trust your DBA, he shouldn't be your DBA). It's to protect against a non-admin user who needs access to the tables, but shouldn't have access to certain data
eg a junior HR person who needs to be able to see general employee data, but shouldn't be able to read the social security number or salary.
It's for protecting against non-admin users accidentally seeing sensitive data, or trying to see (perhaps to sell) sensitive data.
What's interesting is that you don't have to encrypt all the rows with the same key. Say a table contains sensitive information and two users have access to that table, but neither is allowed to see the other one's data. Give each a symmetric key that only he has access to. Then each one can encrypt data and insert into that table and be able to see (unencrypted) the data that he added, but the other person can't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2008 at 7:03 am
GilaMonster (11/6/2008)
Sure they can. Encryption is not designed to protect the mdf file (secure your server), not is it mean to protect against the DBA (If you don't trust your DBA, he shouldn't be your DBA). It's to protect against a non-admin user who needs access to the tables, but shouldn't have access to certain dataeg a junior HR person who needs to be able to see general employee data, but shouldn't be able to read the social security number or salary.
It's for protecting against non-admin users accidentally seeing sensitive data, or trying to see (perhaps to sell) sensitive data.
What's interesting is that you don't have to encrypt all the rows with the same key. Say a table contains sensitive information and two users have access to that table, but neither is allowed to see the other one's data. Give each a symmetric key that only he has access to. Then each one can encrypt data and insert into that table and be able to see (unencrypted) the data that he added, but the other person can't.
Wouldn't using views or stored procedures solve the problem of the jr HR person. In our shop we never give access to base tables. Either we a create a view which contains only the rows and columns the user should see or we limit user access to tables to stored procedures which return only the values the user is authorized to see.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
November 7, 2008 at 11:30 am
For the Record SQL Server 2008 has Transparent Data Encription TDE which is supposed to be used in these cases for maximum security.
* Noel
November 8, 2008 at 4:27 am
Terri (11/7/2008)
Wouldn't using views or stored procedures solve the problem of the jr HR person. In our shop we never give access to base tables. Either we a create a view which contains only the rows and columns the user should see or we limit user access to tables to stored procedures which return only the values the user is authorized to see.
Sure, that works. Think of encryption as another level of defence.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply