Today's question comes from Andy.
I need a way to validate a SQL Users password. The only way I have come up with is to try to access the server, then if it fails the user isn't validated. There must be a better way. I've tried running a query on both the
syslogintable and the
sysxlogintable with converting the password the various data types. I didn't think it would work, but tried anyway. Do you have any ideas?
The password in
syslogins is encrypted, so there really is no way you can
get to it. I don't understand why you are trying to reinvent the wheel
though. The whole point of the SQL Users password is to allow/disallow
access to the server. If this fails, it's up to the client application to
handle the failure gracefully and issue an error message to the user. I
think you should be focusing on handling the issue of a clean login using
the existing SQL Server functionality, rather than rewriting SQL's login
Another thing, have you considered using NT Security to handle user authentication. This could provide a transparent login procedure to the end-user, while you can rest assured that only authorised users are accessing the database. (You have to be using NT on the client machines to implement this though).