SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial

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 (http://blog.SQLAuthority.com)

6 thoughts on “SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial

  1. 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.



  2. 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.

    Pinal Dave ( http://www.SQLAuthority.com )


  3. 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?


  4. 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???


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s