Results 1 to 9 of 9

Thread: password

  1. #1
    Join Date
    Apr 2004
    Posts
    101

    Unanswered: password

    Hi,
    Is there any way to show one column(password) values as ***.
    One of my table has password field, when you query the table it will give actual value, i wanted to show them as **** or with junk charecters.
    FYI..column data type is varchar(50).
    Thanks in advance.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You could use Pwdencrypt and pwdcompare:

    Pwdencrypt and pwdcompare are internal, undocumented functions that SQL Server uses to manage passwords. Pwdencrypt uses a one-way hash that takes a clear string and returns an encrypted version of that string. Pwdcompare compares an unencrypted string to its encrypted representation to see whether they match.

    Script below shows how to save and compare passwords:

    create table users(
    id int identity,
    username nvarchar(128) not null unique,
    userpassword nvarchar(128) not null
    )

    insert users(username,userpassword)
    select 'tom',pwdencrypt('tom2')

    insert users(username,userpassword)
    select 'brett',pwdencrypt('brett2')

    select Id from users
    where pwdcompare('tom2',userpassword)=1
    and username='tom'

    Id
    -----------
    1

    (1 row(s) affected)

    select Id from users
    where pwdcompare('brett3',userpassword)=1
    and username='brett'
    Id
    -----------

    (0 row(s) affected)

  3. #3
    Join Date
    Apr 2004
    Posts
    101

    password

    Thanks for your reply.
    And is there any way to decrypt the password, if you loose your actual password?
    Thanks

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Quote Originally Posted by sskris
    Thanks for your reply.
    And is there any way to decrypt the password, if you loose your actual password?
    Thanks
    Nope just set up new one

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, the point is to encrypt the password before it is stored in your table. And if it could be decrypted, it wouldn't be very secure, right?

    Actually, SQL Server's encryption method has been cracked, and decryption algorythms are available on the web. If you want more security, I have a one-way encryption function you are welcome to use.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2004
    Posts
    101
    Any leads would be greatly appreciated!!
    Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is my pasword encryption algorythm.

    The other disadvantage of using SQL Server's PWD_ENCRYPT function is that Microsoft can and has changed the algorythm in subsequent releases of SQL Server, rendering all existing passwords useless.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious...what sql server security model are you using?

    I'm always leary of Application level security that store passwords in the database.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2004
    Posts
    101
    Mixed mode.
    thanks

Posting Permissions

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