Verify Password before changing password

Hi Everyone,

     I've been doing some updating on an application and ran into a small problem.

     Capability - Allow the user to change their password via the frontend application.  All logins are SQL Server logins.

     I created a stored proc back in 2004 and used the following statement

     EXECUTE master.dbo.sp_password @strOldPassword, @strNewPassword

     This has worked fine (originally created in SQL Server 2000) and will probably work for the foreseeable future.  I was doing some reading about changing passwords and according to Microsoft the sp_password will be deprecated at some point in the future and we should use ‘ALTER LOGIN’ instead.  So being the good little developer I am, I decided to give it a try.  

     I modified my stored procedure to use ‘ALTER LOGIN’ using the parameters passed in (current password and new password).  I was getting the current user from SQL.  Low and behold ‘ALTER LOGIN’ doesn’t like parameters.  So I converted my SQL to Dynamic SQL, see below.

SET @strSQL = 'ALTER LOGIN ' + @strCurrentUser + ' WITH PASSWORD=''' + @strNewPassword + ''' OLD_PASSWORD=''' + @strOldPassword + ''''

SELECT @strSQL
EXECUTE(@strSQL)

     The above worked great.  By the way, the SELECT @strSQL is only there for testing.

     The problem showed itself when the old password for the user isn’t correct.  The stored procedure throws a hard error, telling you the user doesn’t exist or you don’t have permission.  I could have captured that error and moved on, but I thought there should be a way to check the old password first before changing it.  I was reading the ‘Guru’s Guide To Transact-SQL’ earlier and saw the PWDCOMPARE and when I did a search tonight the same topic came up.  Perfect, my problems were answered.  So I modified my SQL to incorporate this undocumented function.

          --First we need to see if the old password is correct for this user
               IF (SELECT PWDCOMPARE(@strOldPassword, password) FROM master..syslogins WHERE name = @strCurrentUser) = 1
            
                    BEGIN
       
                         SET @strSQL = 'ALTER LOGIN ' + @strCurrentUser + ' WITH PASSWORD=''' + @strNewPassword + ''' OLD_PASSWORD=''' + @strOldPassword + ''''

                         SELECT @strSQL
                         EXECUTE(@strSQL)

                         If @@ERROR<>0
                              RETURN 1
                         ELSE
                              RETURN 0
                    END

          ELSE
               --SELECT PWDCOMPARE(@strOldPassword, password) FROM master..syslogins WHERE name = @strCurrentUser
               RETURN 3

     This worked perfectly as long as the user executing the stored procedure had a role that would allow them to execute PWDCOMPARE.   So, thinking back to some changes I had made today on another proc, I added the….

WITH EXECUTE AS N'SecurityUser'

      … to the stored procedure.  Unfortunately, the stored procedure always returns a 3.  It seems that the PWDCOMPARE doesn’t like to be executed using the ‘WITH EXECUTE’.  I’ve tried giving the user, ‘SecurityUser’, the roles of securityadmin, serveradmin and sysadmin, but to no avail.  I only want ‘SecurityUser’ to have enough rights to execute this proc.

     Any help would be greatly appreciated.  

     All I’m trying to do is to allow the users to change their password, but I want to verify their password first.  If there is a different path, I’m all ears (eyes).

     I hope I’ve given enough details.  

Thanks,
Mike
LVL 18
Data-ManCOOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OklahomaDaveCommented:
Hmmm...just spitballing here, but wondering if you executing this from a web application, perchance? If so, the user context for the query may not be what you are expecting, eg that of the user for which you are trying to define privs/roles. If you're running this from IIS7, the user context could be either that of the app pool running the application, *or* the authenticated user to IIS if delegation between IIS and SQL server is enabled.

At least something to consider.
0
Data-ManCOOAuthor Commented:
Hi Oklahoma Dave,
   It's running from a traditional desktop application using ADO.

  I did some additional testing this morning and if I login to SSMS as 'SecurityUser' and execute the procedure without the 'WITH EXECUTE AS' statement, it works.  However, if I uncomment the 'WITH EXECUTE AS', the PWDCOMPARE statement always returns 0.  I really thought I would see the same results as I'm executing it as myself, the current login.  This one really has me stumped.

  I have about 6 or 7 areas where I was hoping to use this.  I really don't want to have to elevate a users permissions because they have been given the capability to reset passwords, add/remove logins, etc.

Thanks,
Mike
0
Data-ManCOOAuthor Commented:
Ok, I figured it out.  I changed to code to EXECUTE as 'sa'.  I received a message that the user didn't exist or was out of the current security context.  That was a different error than I was receiving before.  I decided to Google on 'SQL Server and security context'.  I was reading tons of posts and found one that said the database needs to be trustworthy.  So I ran this code on the database.

ALTER DATABASE [database name here] SET TRUSTWORTHY ON

Then I changed the proc back to my security user and dang if it didn't work  :-)

I will have to read up on what it means to set the database to TRUSTWORTHY.  I'm not sure of the security implications.

Hope this helps someone.  Please post if it does.

Thanks,
Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Data-ManCOOAuthor Commented:
This solution solved the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.