Change Expired Password - T-SQL

[lameid]

I am in an environment where SQL passwords expire every 90 days and I have not found a way to have that changed (bureacracy).

This means my application quits working every 90 days until I login with SMS, change the password and update the FE.

Is there any simple way to have a SQL User change it's own password when it is expired using T-SQL i.e. via ODBC/ADO connection?

 

[bborissov]

USE [master]
ALTER LOGIN [Login Name here] WITH PASSWORD=N'newpassword'

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server

 

[lameid]

Wouldn't you have to be authenticated and with special permissions to execute Alter Login?

 

[SQLBill]

From the BOL:

A principal can change the password, default language, and default database for its own login.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[lameid]

I had put this on the backburner but I am revisiting now....

http://msdn.microsoft.com/en-us/library/ms131024(v=sql.90).aspx

Looks like that starting in SQL 2005 the SQL Native Client driver added support for managing expired passwords. Seems like my thought process is correct, once the password expires I can't change the password. This is true because installing SQL Native client is not an option.

So instead I will have to dig up a script to determine when the password expires and if it does in say less than X days, change it.

Nothing like creating a work around using questionable practices to work around security policy that won't consider reality.

 

[lameid]

Days until password expires IN SQL 2008:

SELECT LOGINPROPERTY('[Login Name here]', 'DaysUntilExpiration');  --That first value is a string

A user can Change his own password by specifying the old password but may not reset it by only setting a new password...

ALTER LOGIN [Login Name here] WITH PASSWORD=N'newpassword' OLD_Password = N'oldpassword';

 

[gmmastros]

This is true because installing SQL Native client is not an option.

I understand that developers are some times prevented from doing things. However, I am curious to know why you cannot use the SQL Native Client.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lameid]

The short answer is that at large companies the bureaucracy often out weighs anything that makes sense. Just glad this particular place is not Healthcare industry and the default security is HIPPA motivated (makes things more interesting).

Only the help desk is allowed to install software by policy (users are local admins so technically can just frowned upon). The help desk is not exacly motivated to support such things. They will also blame any unsactioned software that has been working for 180 days when a patch they rolled out and clearly the culprit of some new issue or so I have heard.

Secondly, I am a contractor and not necesarily around to fix things or remind them how to download (and maybe sneak in on sneaker net) the install file. Technically I could try and sponsor the software for install... Probably 6 months of paperwork for the uninitiated like me.

The only SQL client tools available for official install is for 2005 while the server is 2008. So I can't get the right SQL Native client driver version by hook or crook reliably. <--My SQL Express 2008 install is a mystery that will go unsolved

Lastly the project started in Access and was later moved to SQL through a parallel development effort so I am not technically on the Development team that owns the server. I just wrote the database, except for some data sprocs that the other app uses... Which is really asinine for me, as there is no developer assigned to the server... I bet TFS is here but do I have it? Nope.

So like any sane peron faced with insane situations, I just quietly go about my business keeping the parts lubricated hoping I don't have to listen to the insanity.