Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ywb
Yak Posting Veteran
55 Posts |
 Posted - 2006-10-10 : 17:50:19
|
| Hi,My company has an old ASP application that accesses a user table in an SQL database. This user table stores password in clear text. We are now planning a ASP.NET application that would be used by the same set of users so we are planning to use back the same user table, but we'll add a couple of columns to store the hash and salt (which are created by our .NET code) for better security. Our plan is that we'll delete the column that stores the passwords in clear text after we decommission the old ASP application in the near future.Now my question is that in the meantime while the old ASP application is still in use, new users will be added and password can be changed using this application. Is there anyway I can set up triggers in the user table to create the hash and the salt using the classes we build, whenever there's any new entry or update to the user table, so that the new ASP.NET application can work properly with these changes too?Thanks,WB |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-10 : 22:29:10
|
| If you want to use a trigger, you would have to translate the hash function to TSQL, or write an extended stored procedure. In SQL 2005, you could create an CLR procedure, but I am guessing this is not SQL 2005.CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-11 : 01:39:46
|
| We did this once, and we decided it was easier to have the new application do the hashing (since it already had all the logic we needed and duplicating the logic in SQL looked like being a PITA - no CLR procedures back in those days!).So we extended the password routine in the new application so that if the hash/salt were blank that it calculated a new one from the "plain text" password, and saved it to the database. And we wrote a trigger that cleared the hash/salt when the plain-text password changed IF the hash/salt did not also change.i.e. if something just changed the plain-text password then the next login in the new application fixed up the hash/salt.There is obvious a logic change when you decommission your old app. which may be risky from a QA standpoint, and at the very least needs considering!Kristen |
|
|
|
ywb
Yak Posting Veteran
55 Posts |
Posted - 2006-10-11 : 12:58:22
|
| Hi Kristen,Thanks for the suggestion; I didn't think of it that way, but it's actually a much simpler approach.WB |
|
|
|
|
|
|
|
|
Trigger to Create Password Hash?