Post reply

Encrypting a column in a table

  • July 9, 2010 at 3:51 am

    #234896

    Hi All,

    I want to know whether we can CREATE a table in such a way that one of the columns is encrypted.

    For example if we have a table login_info with two columns UName and Password, I want the Password column data to be encypted.

    Is this possible? If so, how to decrypt the data for testing or any other purpose? Can we have something like access rights on that table/column?

    Hope i will get answers to these questions from the experts here....

    Thanks in adv.

    Malavika.

  • July 9, 2010 at 4:16 am

    This was removed by the editor as SPAM

  • July 9, 2010 at 4:31 am

    #1190691

    Note that from a security standpoint you shouldn't use encryption to encrypt passwords, as if you know how to decode the password, then you can apply the same logic to every other encrypted password. Instead, you should use the SQL Server hashing functions to create a one-way hash to encrypt the password. Being one-way, you can't decrypt the hash to get the original value, but you can hash each attempt the user makes and compare it to the hashed password.

    You can use column-level security to protect the encrypted value, but anyone with dbo or sysadmin rights over the database will be able to view this data. And anyone that manages to get a copy of the database will be able attached it with sysadmin rights.

    There is an excellent chapter on encryption in the "Expert SQL Server 2005 Development" book (Apress), which I highly recommend if you want a deep understanding of SQL Server encryption.

    Alternatively, there are slide notes and examples from a presentation I gave to the Melbourne SQL Server User Group last year, available at http://www.jimmcleod.net/files/SQLServerEncryption.zip.

  • July 9, 2010 at 5:04 am

    #1190700

    Hi,

    Thanks for the reply. That password thing was just an example i gave to explain my req.

    Actually this is done for some id column in our database.

    Could you please explain me more on hashing?

  • July 9, 2010 at 6:51 am

    #1190767

    I must confess to feeling a little self-conscious when linking to blog posts of my own, but I wrote two longish posts on hashing and various applications of what you can do with hashing, available at:

    http://www.jimmcleod.net/blog/index.php/2009/04/22/hashing-functions-in-sql-server-20052008/

    http://www.jimmcleod.net/blog/index.php/2009/04/23/applications-using-hashing-with-sql-server-20052008/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply