?? Changing a MUST_CHANGE Password ??

[Tom Baxter]

Hi everyone,

I seem to have a "chicken and the egg problem" here. I created a SQL Server
2008 login for a user with the MUST_CHANGE option on the password, like
this:

CREATE LOGIN fred
WITH PASSWORD = 'fred' MUST_CHANGE, ...

If the user logs in through SSMS, sure enough, the user is prompted to
change the password. No problem.

When connecting to the database using from my C# program, however, the
connection fails (as I would expect) with a SqlException (18488) which
indicates the user must change the password before being able to connect.

When I receive this error I can easily prompt the user for a new password.
The problem is I have no way to then *set* this new password because I
cannot connect to the database in the first place. I'm in a cycle here: The
user must change the password in order to connect to the database, but in
order to change the password the user must first connect to the database.

The obvious "solution" would be to connect with some other login that has
permission to change the user's password but this seems overly complicated
and I'm not sure if this is the "right way" to do it. Does anyone know of a
good way to do this?

Thanks.

 

[Tibor Karaszi]

There are functionality in ADO.NET 2.0 to specify the new password
before you logged in.

For instance, search the SQL Server Books Online for 18488 and you
will find how to do it in ODBC/OLEDB:
http://msdn.microsoft.com/en-us/library/ms131024.aspx

As for .NET, you are probably looking for the .ChangePassword method
for the SqlConnection object. At least suggested by
http://my.safaribooksonline.com/0321382188/ch13lev1sec12
And here's a VS 2008 documentation URL:
ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/fxref_system.data/html/2c745192-9129-c150-1897-78fd3acab48c.htm

 

[Tom Baxter]

See Below...

Tom,

I would have thought that, if the SQL Server is prompting to change the
password, it has accepted the initial login and is now simply following
through on the MUST_CHANGE directive. So, your user must have rights to
the server or else the password prompt is meaningless.

The way a user changes the password in SSMS is under their own rights.
So, your application should be able to use their existing login rights to
change their password.

I realize that the user changes their own password in SSMS under their own
rights but I'm not sure what's going on behind the scenes.

Here're the steps I go through and the results:

1. Try to connect using existing password.
2. Connection fails because password must be changed.

After #2 I'm dead in the water. In order to change the password I must first
connect, but I cannot connect until I change the password.

Again, I *CAN* change the password if I log in through SSMS.

 

[Tom Baxter]

The SqlConnection.ChangePassword() method did the trick. I would still like
to know what's going on behind the scenes though.

 

[Tibor Karaszi]

Not sure what you mean "behind the scenes"? Do you mean what packages
are transmitted at the TDS level? I just think of it this way:

SQL Server has been enhanced to natively inform a client app that the
password we tried to connect with need changing. The protocol between
the client and the server has been enhanced to allow for us to send in
the old and the new password to the database engine so that the
database engine can implement the password change. The API's has
fortunately been upgraded to expose this new functionality in the TDS
protocol.

Beyond above I guess one can plug in a network sniffer which
understands TDS and look at the packages submitted...

 

[Tom Baxter]

Not sure what you mean "behind the scenes"? Do you mean what packages are
transmitted at the TDS level? I just think of it this way:

SQL Server has been enhanced to natively inform a client app that the
password we tried to connect with need changing. The protocol between the
client and the server has been enhanced to allow for us to send in the old
and the new password to the database engine so that the database engine
can implement the password change. The API's has fortunately been upgraded
to expose this new functionality in the TDS protocol.

When I say, "behind the scenes", I mean what commands are being sent to SS
by the .NET client when the SqlConnection.ChangePassword() method is called.
I may well use Ethereal to see what's happening. I'm very curious about
this.

Thanks for the tip about ChangePassword() -- it was just what I needed.

 

[Tibor Karaszi]

When I say, "behind the scenes", I mean what commands are being sent

to SS by the .NET client when the SqlConnection.ChangePassword()
method is called.

Ahh, I see. My theory is that there are no TSQL commands executed -
for the simple reason that no connection were established in the first
place. A Profiler trace could verify that. I.e., this is all
implemented (exposed) as the API level, so correlating some lower
level trace to a Profiler trace would probably confirm that...