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

i'm programming legacy coldfusion application uses 2008 sql server database. know little how sql server database set up, i'm hoping if share of symptoms, might have suggestions check.

the database uses symmetric key secure users passwords. have users table username, password, etc. fields. password encrypted.

most legacy users in database work correctly. users can login using website, change passwords, etc. without issues. records used testing, i've changed passwords sql in sql server, not through website: "update users set password = "fluffy" userid in (6543, 7654, 8765)" etc.

when i've done that, few things happen:

• i can never log website first time using userid 6543 , password "fluffy" -- works second time.

• when run stored procedure exec get_user_unencrypt_by_id 6543,
  results return "null" password.

• when run query select * users see expected
  symbols/gibberish in of password fields, users
  6543, 7654, , 8765, see "fluffy".

• when run query select * users password null no results.

what i've done try resolve problem:

i ran following sql open , reset master key:

open master key decryption password = '' alter master key add encryption service master key close master key go 

this seemed have no effect.

i tried updating corrupted passwords using sql

  update users set password = encryptbykey(key_guid('pass_key_01'), 'fluffy')    userid in (6543, 7654, 8765)" 

when tried this, users locked out when using password 'fluffy.'

i've tried resetting passwords through website. appears work correctly records passwords not corrupted. if 1 of corrupted passwords, appears work temporarily, later (the next day), password corrupted again.

my sp named get_user_unencrypt_by_id this:

open symmetric key pass_key_01    decryption certificate userpasswords0324  select       userid, username, convert (nvarchar,  decryptbykey([password])) 'password', [role], firstname, lastname,  add1, add2, city, [state], zip, phone, fax,          users 

i'm not sure else try, i'd happy suggestions or ideas. thanks.

edited add more detail. in continuing investigate, learned there trigger on table. trigger.

/****** object:  trigger [dbo].[encrypt_password_on_update] script date: 4/1/2015 8:55:44 ******/ set ansi_nulls on go  set quoted_identifier on go  alter trigger [dbo].[encrypt_password_on_update]     on  [dbo].[users]        after update  begin      /***the purpose of trigger encrypt password  update user.  when update statement updates password,   encrypts before storing in db***/  declare @updatecount int declare @userid  int declare @password nvarchar(50) declare @temp_encryt_password nvarchar(50)  select @updatecount = (select count(userid) inserted)  if (@updatecount = '1') begin select @userid  = (select userid inserted)  open symmetric key pass_key_01    decryption certificate userpasswords0324  if (@userid != '' , @userid not null)     begin          select @temp_encryt_password = (select    encryptbykey(key_guid('pass_key_01'), [password]) users   userid = @userid)      /***if password encrypted (if update   else other password) don't want reencrypt***/         if ( convert (nvarchar, decryptbykey(@temp_encryt_password))  not null)         begin             update users              set [password] = encryptbykey(key_guid('pass_key_01'), [password])             userid = @userid         end      end end end  go 

i believe i've solved own problem. issue occurring in passwords reset directly within database using query

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

then trigger encrypting passwords looks single record:

select @updatecount = (select count(userid) inserted)  if (@updatecount = '1') begin ... 

so passwords being stored in database natively, , never encrypted because updating multiple records @ same time.

then, when user attempted log in site, authentication fail -- sp returned decrypted password return null. failure trigger update on user's database increment number of failed login attempts. query trigger password encryption, , second time user attempted log in, authentication work. so, testing purposes, key needed reset passwords in database using series of queries this:

update users set password = "fluffy" userid = 6543 go update users set password = "fluffy" userid = 7654 go update users set password = "fluffy" userid = 8765 go