Post reply

Column encryption

  • September 2, 2004 at 11:41 am

    #162611

    Hi all, I know I am going to hear it on this but I have no choice. My CTO insists on encrypting the contents of a column that contains passwords at the database level. He will not use the client to do this for various reasons that he will not share with me. So I am left with trying to learn if SQL provideds any functionality to encrypt a cloumn. I have been doing some reseach and I have found articles that refer to some xp called xp_encrypt. I have looked in the master db and I don't see such a xp, or a sp for that matter of this name.

    I really need some solid suggestions on how to encrypt the column in a database without 3rd party tools.

    Thanks in Advance.

    Gary

  • September 2, 2004 at 12:21 pm

    #521553

    SQL doesn't provide any encryption facility. You will have to rely on third party product for that. There is a function called pwd_encrypt() but this only provides one side encryption. i.e. you can not decrypt your password from it. You can only compare against the encrypted value using pwd_compare()

  • September 2, 2004 at 1:22 pm

    #521560

    Thanks for your reply. what 3rd party tools would people recomend. I need to have alternatives to present.

    Thanks again

    GF

  • September 3, 2004 at 2:56 am

    #521609

    You could use the undocumented PWDENCRYPT() and PWDCOMPARE() functions in SQL. The PWDENCRYPT encrypts the password as it suggests and the compare take a string and compares it to the encrypted version of the string. This is not the most secure as the encryption can be broken relatively easily but for the standard user it should provide some protection. These don't appear in BOL so you will have to search in google if you want more information on them.

    example of use  INSERT INTO USERSTABLE (PASSWORD) VALUES (PWDENCRYPT(@Password))

    DECLARE @password varchar(20)

    DECLARE @SecurePwd varbinary(100)

    SELECT @securePwd  = Password from dbo.Users where UserName = @userName

    SELECT PWDCOMPARE (@Password, @securePwd)

    It will return 1 if they match 0 if they don't.

    regards

    Andrew Barton

  • September 3, 2004 at 7:32 am

    #521647

    Here is a review of some of the solutions out there:

    http://nwc.securitypipeline.com/howto/showArticle.jhtml?articleId=18901525&pgno=1

     

  • September 3, 2004 at 9:58 am

    #521681

    Below are some links.

    http://www.xpcrypt.com/

    http://www.activecrypt.com/

  • September 3, 2004 at 11:10 am

    #521695

    For password, I'd recommend to use "PWDENCRYPT() and PWDCOMPARE() " because you only need one way encrypt since you don't need to decrypt so having 3rd party s/w is kinda waste money. You can do it from asp/asp.net page if you have asp/x front end.

    I recommend it do it from front end because if someone sniffs packets between front end app and sql server, he/she can always grab the pure text. Also easy to grab by using profiler as well.

    For credit card, we are using AspEncrypt software (http://www.aspencrypt.com/)

    My 2cents

  • September 3, 2004 at 4:49 pm

    #521748

    Thanks to all for the recomendations.

    Gary

Viewing 8 posts - 1 through 7 (of 7 total)

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