SQL Server help! Changing user passwords via ASP

[edmicman]

I'm using SQL Server and IIS 5.0.
I have a web page that I demo to clients; there are a handful of demo logins, and each prospect gets their own temporary password. I wrote an ASP interface that lets me log in, select a demo account, and change it's password for the next client.

This page changes both a SQL table that contains the user info, plus executes the sp_password stored procedure to change the password for the appropriate demo user.

Everything used to work fine, but now, when it executes the sp_password call, its giving this error:

Old (current) password incorrect for user. The password was not changed.

So then I have to manually change the password in Enterprise Manager. My method is that the ASP uses a connection string that connects as the specific demo user, and then calls the sp_password statement, which *should* be executed as that user. If I login using query analyzer and execute the same statements, it works no problem.

I'm not finding much that helps on google, etc.....anyone have any ideas? Anything? Thanks.

 

[MrChad]

How are you calling sp_password? Can you post some code?

 

[edmicman]

Here is my connection string:

sqlpass_conn = "DRIVER={SQL Server};SERVER=SERVERNAME;DATABASE=DBNAME;UID=" & form_username & ";PWD=" & form_passwordold

and the execution code

sqlpass_str = "EXEC sp_password '" & form_passwordold & "', '" & form_passwordnew & "'"
Conn.Open sqlpass_conn
rs = Conn.Execute(sqlpass_str)

The crazy thing is, it USED to work. I'm not sure where, though, it stopped working. A windows update?

edit: It doesn't seem to be a permissions thing...if I change the connection login to the sa user, or if I give admin privilidges to the username, it still doesn't work. It's like there's something not being passed to the server or something.

 

[MrChad]

Have you tried connecting with sa and specifying the login id as a third stored procedure parameter?

sqlpass_conn = "DRIVER={SQL Server};SERVER=SERVERNAME;DATABASE=DBNAME;UID=sa;PWD=sa"
sqlpass_str = "EXEC sp_password '" & form_passwordold &"', '" & form_passwordnew & "', '" & form_username & "'"
Conn.Open sqlpass_conn
rs = Conn.Execute(sqlpass_str)

 

[MrChad]

This page offers some alternative ways to call stored procedures from ASP.

 

[edmicman]

Still doesn't work by specifying the login id as the 3rd parameter....I'll check the link out.

Thanks!!

 

[Shazam]

Ugg.

The reason you haven't found any information on the web about this is because no one does it this way.

Why are you executing the stored proc as the user??!? Set up a SQL Server user that ASP uses to connect to the DB.

As well, your site is extremely vulnerable to SQL injection. Yikes.

 

[edmicman]

It's an internal app, so the outside world doesn't have access to the pages.

We do have ASP authentication, but the user logins also have permissions for the database. How do you change SQL user permissons outside of ASP?

edit: the main app actually grew out of an access database, hence there is an individual SQL user for each asp user.... (yeah, I know....)