Solved

Change MSSQL expired password (ODBC) with PHP

Posted on 2009-04-06
7
792 Views
Last Modified: 2013-12-13
Hello all:

Situation is that the web server is not the database server (different machine, different admins). Using IIS and MSSQL database. The admins have set the username that my PHP app logs in with to expire every 30 days. This ALWAYS happens on a weekend for some reason!

So, I thought it would be cool if my PHP app could just automatically change the password when it expires.

Well, the problem is that when I odbc_connect($myDsn, $myUser, $myPass) I get a message saying that the password for this login has expired, SQL state 37000 and to change the password.

Now, I can use the Microsoft SQL Server Manager to log in, and it pops up a window asking for a new password...

But how do I get the PHP app to do it, when it fails at odbc_connect()? Is there an odbc_change_password() or something?

(Not possible for me to change the password before it expires.  Military, go figure.)
0
Comment
Question by:InkEye
  • 3
  • 2
7 Comments
 
LVL 12

Assisted Solution

by:udayakumarlm
udayakumarlm earned 200 total points
ID: 24083828
1. first connect using another login which has user creation/Alter rights in PHP
2. change the password for the specific user you want to use, when it is expired
3.you can use something like ALTER LOGIN KittiLert WITH PASSWORD = '3948wJ698FFF7';

so,
You will have two logins A and B, A will be used to change and modify the User B. user B will be used to login as regular user who accesses the data.
0
 

Author Comment

by:InkEye
ID: 24090247
Ok, I will probably accept that as the answer if really you're saying, "you can't." :-)

Which is kind of what I thought.

Now, PHP's Oracle function list seems to have a function to do that (oci_change_password()).  But I don't see one for odbc drivers.
0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24093442
check this code. it may help
Dim wspODBC As Workspace, conODBC As Connection, qdfN As QueryDef
Dim strConnect As String

'database is the SID of the Oracle-database, DSN is the name of the ODBC datasource
strConnect = "ODBC;DATABASE=database;UID=username;PWD=password; DSN=oradb;"
Set wspODBC = DBEngine.CreateWorkspace("ORCL", "Admin", "", dbUseODBC)
Set conODBC = wspODBC.OpenConnection("conODBC", , False, strConnect)

Set qdfN = conODBC.CreateQueryDef
qdfN.ReturnsRecords = False
qdfN.SQL = "ALTER USER JOHN IDENTIFIED BY NEWPASSWORD"
qdfN.Execute
qdfN.Close
Set qdfN = Nothing
Set conODBC = Nothing
Set wspODBC = Nothing
End Sub
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:InkEye
ID: 24265051
This comment may lead to a partial solution, but it is not the solution as is.  Since I have not experienced any new expired passwords in the past 21 days, I have no way to test and derive a solution (yet).  I ask to keep it open just a while longer; I won't forget -- it's the only question I have ever posed.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 24291564
several things, starting with the ALTER LOGIN reference:http://msdn.microsoft.com/en-us/library/ms189828.aspx
you could alter a test login with MUST_CHANGE, so you will get that message without having to wait.

to change the password, the ALTER LOGIN should be used, for older databases the sp_password might be used instead:
exec sp_password 'old_password' ,  'new_password', 'login'

apart  from that, the suggestion to connect with a different valid username (which has the powers to change the password) is, IMHO,  the only correct one.
php does not implement a interface to change the password on "connection".
I know that SQL Server and Oracle (and possibly others) have that interface on their side...

0
 

Author Closing Comment

by:InkEye
ID: 31567251
I was afraid that was the answer.  I know that PHP has an interface to change an expired password if the database is Oracle, but not for MSSQL (at least I couldn't find a way).
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now