Solved

Change MSSQL expired password (ODBC) with PHP

Posted on 2009-04-06
7
795 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

910 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

25 Experts available now in Live!

Get 1:1 Help Now