Solved

Change MSSQL expired password (ODBC) with PHP

Posted on 2009-04-06
7
806 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:udaya kumar laligondla
udaya kumar laligondla 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:udaya kumar laligondla
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 143

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

828 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