ENCRYPT function not saving data as encrypted

  • Hello, 

    We have two stored procedures that save password information using the encrypt function. When the pasword is saved it is stored as the original data with each character seperated by a space.

    Example:

    password: "12345"

    Saved as: "1 2 3 4 5"

    Here is the table and the two SP's. Can anyone provide a solution to this problem?

    CREATE TABLE [dbo].[sys_user] (

     [id] [int] IDENTITY (10000, 1) NOT NULL ,

     [username] [varchar] (20) NOT NULL ,

     [password] [varchar] (50) NOT NULL ,

     [first_name] [varchar] (50) NOT NULL ,

     [last_name] [varchar] (50) NOT NULL ,

     [last_login_date] [datetime] NOT NULL ,

     [is_active] [bit] NOT NULL

    ) ON [PRIMARY]

    GO

    CREATE PROCEDURE sp_sys_user_insert

     @username VARCHAR(20),

     @password VARCHAR(10),

     @first_name VARCHAR(50),

     @last_name VARCHAR(50)

    AS

     INSERT INTO sys_user (

      username,

      password,

      first_name,

      last_name,

      last_login_date,

      is_active) VALUES (

      @username,

      ENCRYPT(@password),  /* encrypt password */

      @first_name,

      @last_name,

      GETDATE(),       /* default last_login_date to today */

      1);

    GO

    CREATE  PROCEDURE sp_sys_user_update

     @sys_user_id INT,

     @username VARCHAR(20),

     @password VARCHAR(10),

     @first_name VARCHAR(50),

     @last_name VARCHAR(50)

    AS

      UPDATE  sys_user

     SET  username = @username,

      password = ENCRYPT(@password),

      first_name = @first_name,

      last_name = @last_name

     WHERE  id = @sys_user_id

    GO

    Thanks,

    Steve DiDomenico, Nashua, NH

  • Steve,

    It appears that the encrypt function takes the characters that are typed in and then returns the Hex numbers of each character separated by 00.

    I am not sure if that will provide the results that you want.  You might want to try using pwdencrypt and pwdcompare.  These are both undocumented features, so the functionality might change between versions of SQL Server.

    Examples:

    • Select Encrypt('test') - returns 0x7400650073007400
    • Select ascii('t') - returns 116.  (Hex 74 = Decimal 116.)
    • Select ascii('e') - returns 101.  (Hex 65 = Decimal 101.)
    • Select ascii('s') - returns 115.  (Hex 73 = Decimal 115.)
    • Select ascii('t') - returns 116.  (Hex 74 = Decimal 116.)

    Try using pwdencrypt(@password) instead of encrypt(@password).  You can then use the following test condition:

    INSERT INTO tableName (userID, password) VALUES (1, pwdencrypt

    ('mypassword')) ;

    SELECT @test-2 = password FROM TableName where userID = 1

    PRINT pwdcompare('wrongpassword',@test ); (returns 0)

    PRINT pwdcompare('mypassword',@test ); (returns 1)

    A value = 1 means a match, a 0 means no match.

    Many Links, but try this for the full example: http://www.devx.com/tips/Tip/14407

    Thanks,

    Sami

     

    (Note: There are quite a few warnings against using these functions, so use them at your own risk.)

  • Thanks Sami, I forgot to mention that this worked on SQL Server 7.0 and stopped working after migration to SQL Server 2000.  I'll give your suggestion a try.  The only problem is that the passwords can be read if someone runs a Select from QA or opens a table returning rows in EM.

  • I think the pwdencrypt() will store it in an encrypted fashion.  That may solve your problem.

  • pwdencrypt() does encrypt, but the article at http://www.theregister.co.uk/2002/07/08/cracking_ms_sql_server_passwords/ 

    explains how they can also be cracked.

  • what about encryption the whole table ?


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • Hi Alamir,

    Thanks for the post.  I don't know what you mean.

    Steve

     

     

  • I am sorry there is no Encrypt for Tables in SQL Server 2000 !!

    you can use stored procedures, views, and functions and apply "With Encryption" to them.

    Also there is a product called "XP_Crypt", I think it Encrypts Tables

    see this link

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=6&messageid=99573

    I hope this help u


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • Is "With Encryption" different than what I am using in my Stored Procedures which I included in my first post to start this thread?

  • Yes.  I think this thread is referring to the encryption of the data in a table, not the encryption of the stored procedure.

    You can encrypt a stored procedure so that it can't be viewed, only executed.  (You can also find freeware utilities to decrypt any encrypted stored procedure).  It does not encrypt the data in a table.

    Real-life example: We had a DBA who encrypted his stored procedures prior to leaving the company.  The applications worked, but were impossible to change.  We found a freeware utility and decrypted the SP's.

    From BOL:

    Encrypting Procedure Definitions

    If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The procedure definition is then stored in an unreadable form.

    After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator.

Viewing 10 posts - 1 through 9 (of 9 total)

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