Geek Answers Handbook

Encrypted password corruption - some saved as encrypted, some not encrypted, some not saved

I am programming an outdated ColdFusion application that uses the SQL Server 2008 database. I know very little about how the SQL Server database was created, but I hope that if I share some of the symptoms, someone may have suggestions for verification.

The database uses the Symmetric Key to protect user passwords. I have a Users table with username, password, etc. Like fields. Password is encrypted.

Most old users in the database work correctly. Users can log in using the website, change their passwords, etc. No problem. For the records used for testing, I changed the passwords to SQL in SQL Server, and not on the website: "update users, set password =" fluffy ", where userID is in (6543, 7654, 8765)", etc.

When I did this, several things happen:

  • I can never enter the site for the first time using USERID 6543 and PASSWORD "fluffy" - but it always works the second time.

  • When I started my stored procedure exec get_user_unencrypt_by_id 6543, the
    results return "NULL" as the password.

  • When I run the request select * from Users, I see the expected value of characters / delusions in most password fields, but for users
    6543, 7654 and 8765, I see "fluffy".

  • When I run the query select * from users where password is null, I get no results.

What I did to solve the problem:

I opened the following SQL to open and reset the master key:

OPEN MASTER KEY DECRYPTION BY PASSWORD = ''
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Close Master Key
GO

This seemed to have no effect.

I tried updating damaged passwords using SQL

  update users set password = EncryptByKey(Key_GUID('PASS_Key_01'), 'fluffy') 
  where userID in (6543, 7654, 8765)"

When I tried this, these users were blocked when using the password "fluffy".

I tried to reset passwords through the website. This seems to work correctly only for entries in which passwords are not corrupted. If I do this with one of the damaged passwords, it works temporarily, but later (the next day) the password is again damaged.

My SP named get_user_unencrypt_by_id:

OPEN SYMMETRIC KEY PASS_Key_01
   DECRYPTION BY CERTIFICATE UserPasswords0324

SELECT       userid, username, CONVERT (nvarchar, 
DecryptByKey([password])) as 'password', [role], firstname, lastname, 
Add1, Add2, City, [State], Zip, Phone, Fax, 
FROM         users

, , . .

, . , , . .

/****** Object:  Trigger [dbo].[encrypt_password_on_update]
Script Date: 4/1/2015 8:55:44 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[encrypt_password_on_update] 
   ON  [dbo].[USERS] 
      after update
AS 
BEGIN

    /***The purpose of this trigger is to encrypt a password that was 
update by the user.  When the update statement updates the password,  
this encrypts it before storing it in the db***/

DECLARE @updatecount int
DECLARE @userid  int
DECLARE @password nvarchar(50)
DECLARE @temp_encryt_password nvarchar(50)

select @updatecount = (select count(userid) from inserted)

if (@updatecount = '1')
BEGIN
SELECT @userid  = (SELECT userid FROM Inserted)

OPEN SYMMETRIC KEY PASS_Key_01
   DECRYPTION BY CERTIFICATE UserPasswords0324

if (@userid != '' and @userid is not null)
    BEGIN

        select @temp_encryt_password = (select   
EncryptByKey(Key_GUID('PASS_Key_01'), [password]) from users where  
userid = @userid)

    /***If the password is already encrypted (if the update was for   something else other than the password) we don't want to reencrypt***/
        if ( CONVERT (nvarchar, DecryptByKey(@temp_encryt_password))  is not null)
        BEGIN
            update USERS 
            set [password] = EncryptByKey(Key_GUID('PASS_Key_01'), [password])
            where userid = @userid
        END

    END
END
END

GO
+4
1

, . , reset ,

update users set password = "fluffy" where userID in (6543, 7654, 8765)

, , :

select @updatecount = (select count(userid) from inserted)

if (@updatecount = '1')
BEGIN
...

, , .

, , - SP, , NULL. User, . , , , . , , , reset , :

update users set password = "fluffy" where userID = 6543
GO
update users set password = "fluffy" where userID = 7654
GO
update users set password = "fluffy" where userID = 8765
GO
+1

More articles:


All Articles