• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

Change MSSQL expired password (ODBC) with PHP

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
InkEye
Asked:
InkEye
  • 3
  • 2
2 Solutions
 
udaya kumar laligondlaTechnical LeadCommented:
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
 
InkEyeAuthor Commented:
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
 
udaya kumar laligondlaTechnical LeadCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
InkEyeAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
InkEyeAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now