Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2002
    Posts
    2

    Question Unanswered: How can I encrypt the data in a Table in SQL Server 2000

    Can some one help me..

    I have made a table for storing passwords.
    How can I encrypt the data in the table so that anyone who opens it cannot view the actual data

    thanks in advance

    Jasmita

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    As far as I know there is no easy way to encrypt data. You could create your own algrithum that encrypts a column, use a TRIGGER to enforce encryption on INSERT and UPDATE. If you where using SQL 2000 you could create your own User Defined Function that encrypts and decrypts a column.

    However this encrypting and decrypting could cause a small performance hit. You may think about securing the data so endusers can not run adhoc SELECTs on any table. If they need to do so then create a VIEW that does not have the sensitive column and give the end user SELECT on the VIEW but not on the base table.

  3. #3
    Join Date
    Mar 2002
    Location
    India
    Posts
    3
    There is no direct way fro encrypting the data stored in a table. I can suggest a small logic on which u can build ur T-SQL procedure. In the table add an extra column named say "SECONDS" of number datataype.
    Now for all the characters of the password get the ASCII value of that chararcter and add the Seconds value of the Sysdate to it. At the same time store this seconds value in the above mentioned column. U will need this while decrypting the password.
    for example suppose 'a' is a character in the password whose ASCII value is say 10,at that moment the seconds value of system time is say 06. Now after adding the value u get is 16 which is the ascii of some other character say 'x'. do this in a loop so that a password like "abc" gets stored as "xy3" with column SECONDS value stored as 6.
    While decrypting take the ascii value of 'x' minus SECONDS value(6 in this case) u get 10... ascii of which is 'a'... similarly for all other characters. this is a workable solution as i had myself used this logic in one of my projects. But then that was done in Oracle. U have to just fugire out the syntax ...... in Oracle we have a command ASCII('a') to give u the ASCII value.. am not sure how to get it in SQL SERVER.. but definitely u can have it.
    cheers

  4. #4
    Join Date
    Sep 2002
    Posts
    1

    Question Table - Column encryption

    I realize this posting is a little old but has anyone had experience with third party encryption utlities such as Communication Horizons for encrypting database info such as tables or specific columns.

    Thanks
    Ray

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: How can I encrypt the data in a Table in SQL Server 2000

    Originally posted by Jasmita
    Can some one help me..

    I have made a table for storing passwords.
    How can I encrypt the data in the table so that anyone who opens it cannot view the actual data

    thanks in advance

    Jasmita
    Try to use this draft - for passwords it will work....

    declare @password sysname
    select @password='pass'
    select @password=pwdencrypt(@password)
    select pwdcompare('pass2',@password) as [Incorrect password],pwdcompare('pass',@password) as [Correct password]

    Incorrect password Correct password
    ------------------ ----------------
    0 1

    SQL Server is using functions pwdencrypt() and pwdcompare() for saving passwords for logins.

  6. #6
    Join Date
    Sep 2002
    Location
    Gauteng
    Posts
    3
    I had the same problem, I wrote procedures that might be helpfull. This is the encryption part
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2002
    Location
    Gauteng
    Posts
    3
    This is the decryption part. Hope it helps you.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •