Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Change MSSQL expired password (ODBC) with PHP

Posted on 2009-04-06
7
Medium Priority
?
829 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 12

Assisted Solution

by:udaya kumar laligondla
udaya kumar laligondla earned 600 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 900 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
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 …

661 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