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

Question

I'm programming a legacy ColdFusion application that uses a 2008 SQL Server database. I know very little about how the SQL Server database was set up, but I'm hoping if I share some of the symptoms, someone might have some suggestions for what to check.

The database uses a Symmetric Key to secure users passwords. I have a Users table with username, password, etc. as fields. Password is encrypted.

Most legacy users in the database work correctly. Users can login using the website, change their passwords, etc. without issues. For records used for testing, I've changed the passwords with SQL in SQL Server, not through the website: "update users set password = "fluffy" where userID in (6543, 7654, 8765)" etc.

When I've done that, a few things happen:

• I can never log into the website the first time using USERID 6543 and PASSWORD "fluffy" -- but it always works the second time.

• When I run my Stored Procedure exec get_user_unencrypt_by_id 6543,
  the results return "NULL" as the password.

• When I run a query select * from Users I see the expected
  symbols/gibberish in most of the password fields, but for the users
  6543, 7654, and 8765, I see "fluffy".

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

What I've done to try to resolve the problem:

I ran 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 the corrupted passwords using SQL

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

When I tried this, those users were locked out when using the password 'fluffy.'

I've tried resetting the passwords through the website. This appears to work correctly only for records where the passwords are not corrupted. If I do this with one of the corrupted passwords, it appears to work temporarily, but later (the next day), the password is corrupted again.

My SP named get_user_unencrypt_by_id is this:

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

I'm not sure what else to try, so I'd be happy for any suggestions or ideas. Thanks.

Edited to add more detail. In continuing to investigate, I learned there is a trigger on the table. This is the trigger.

/****** 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

Answer 1

I believe I've solved my own problem. The issue was occurring in passwords that I reset directly within the database using a query like

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

Then the trigger that is actually encrypting the passwords looks for a single record:

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

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

So the passwords were being stored in the database natively, and never encrypted because I was updating multiple records at the same time.

Then, when a user attempted to log in to the site, the authentication would fail -- the SP that returned the decrypted password would return NULL. That failure would trigger an update on the User's database to increment the number of failed login attempts. THAT query would trigger the password encryption, and the second time the user attempted to log in, the authentication would work. So, for testing purposes, the key to what I needed was to reset passwords in the database using a series of queries like this:

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