SQL Server 2005 provides encryption as a new feature to protect data against the attacks of hackers. Hackers may be able to get hold of the database or tables, but they wouldn’t understand the data or be able to use it. It is very important to encrypt crucial security related data when stored in the database, as well while transmitting across a network between the client and the server.
Read my complete article here : Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial
Reference : Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
One of the best article written on encryption using symmetric keys, comming from no other than a genuis ( DAVE) again.
The most interesting fact about your articles is they are written in very simple language which can be undertsood easily by a person which dont have much knowledge about SQL.
Thanks for the article
I have few questions regarding your article :
1. In your example we encrypted symmetric key using a certificate and then we encrypted the column in the table using that symmetric key and when decrypting the data we are just giving the name of the symmetric key and then name of the certificate, no where we mentioned the password, for some one to decrypt the data all he needs to know is name of the symmetric key and name of the certificate used to encrypt data and then he is good to go.
Please explain if I misunderstood.
2. we first inserted the data and then encrypted it and after encryption we are deleting that column, because it doesn’t make sense to keep that column. what if we want to insert more data in that column aftre it is encrypted, do we have to decrypt the existing column and then insert the data and the again encrypt it ?
or is there any way that we can insert the data into the encrypted column with out decrypting and inserting and encrypting it again.
Please clarify.
one request: please give a similar example for Assymetric key.
Thanks.
Yes, the article is so informative. But I also have same questions in my mind as Imran. So please answer it.
Hello Imran and Vicky,
Answer to first question:
Certificates will have to be transferred to different server physically first and setup with SQL Server before using its name to decrypt the data.
Answer to second question:
Yes there is way to insert the data with encryption on.
I will soon write separate article explaining both of the above methods.
Regards,
Pinal Dave ( https://blog.sqlauthority.com/ )
Thanks Pinal. We are waiting……..
Hi,
we have been waiting….
OK please see the code below,
declare @a varbinary(100)
declare @b varbinary(100)
declare @f float
set @f=123.456
set @a=EncryptByPassPhrase(‘vival’, cast(@f as varbinary))
set @b=DecryptByPassPhrase(‘vival’, @a)
select @b
select cast(cast(@b as decimal(10,4)) as float)
getting error at last line,
Arithmetic overflow error converting varbinary to data type numeric.
plz tell what’s d mess?
Any clue on how the Keys are securely stored in SQL Server, ie. what encryption algorithm used to store the key itself in SQL Server so that no one can grab it and to ensure its fully secured???