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.

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/0321.../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


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Tom Baxter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

See Below...

"Russell Fields" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



> What happens when you try to reset their password? Do you get an error?
>
> RLF
>
> "Tom Baxter" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>
>

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


"Tibor Karaszi" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> 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/0321.../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

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...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Tom Baxter" <(E-Mail Removed)> wrote in message
news:%233$(E-Mail Removed)...
> The SqlConnection.ChangePassword() method did the trick. I would
> still like to know what's going on behind the scenes though.
>
>
> "Tibor Karaszi" <(E-Mail Removed)>
> wrote in message news:%(E-Mail Removed)...
>> 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/0321.../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
>

"Tibor Karaszi" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> 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, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Tom Baxter" <(E-Mail Removed)> wrote in message
> news:%233$(E-Mail Removed)...
>> The SqlConnection.ChangePassword() method did the trick. I would still
>> like to know what's going on behind the scenes though.
>>
>>
>> "Tibor Karaszi" <(E-Mail Removed)> wrote
>> in message news:%(E-Mail Removed)...
>>> 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/0321.../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
>>
>
>

> 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...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Tom Baxter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "Tibor Karaszi" <(E-Mail Removed)>
> wrote in message news:%(E-Mail Removed)...
>> 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, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Tom Baxter" <(E-Mail Removed)> wrote in message
>> news:%233$(E-Mail Removed)...
>>> The SqlConnection.ChangePassword() method did the trick. I would
>>> still like to know what's going on behind the scenes though.
>>>
>>>
>>> "Tibor Karaszi" <(E-Mail Removed)>
>>> wrote in message news:%(E-Mail Removed)...
>>>> 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/0321.../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
>>>
>>
>>
>