How to change expired database password via DBI?

Sue D. Nymme has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: How to change expired database password via DBI? by mje (Curate) on Aug 19, 2009 at 15:07 UTC
Are you sure Toad is using the ODBC Driver? What is the name of the dialogue which pops up from Toad? The first time an ODBC Driver knows it cannot "login" is when SQLDriverConnect fails. There is no specific ODBC state for "login expired". However, there is the magic SQL_DRIVER_PROMPT and SQL_DRIVER_COMPLETE options to SQLDriverConnect which allow the ODBC driver to throw up a dialogue and complete the connection string. If Toad is using the SQL Server ODBC driver then I'm guessing the dialogue you are seeing is NOT a Toad thing but a SQL Server driver dialogue. You won't get this with DBD::ODBC because it does not set SQL_DRIVER_PROMPT although if you rebuild it yourself it could easily do so. If you investigated it further by looking at the dialogue and trying to add SQL_DRIVER_PROMPT to SQLDriverConnect call in DBD::ODBC I would consider adding an attribute to DBD::ODBC to enable this functionality.	[reply]
Re^2: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 19, 2009 at 16:08 UTC
Are you sure Toad is using the ODBC Driver? No, I am not sure. It is probably talking directly to the server via the SQL Server port. Unfortunately, there is no DBD::SQLServer, just DBD::ODBC. The rest of your post sounds interesting, but I have to admit it's way over my head. :-(	[reply]
Re^3: How to change expired database password via DBI? by mje (Curate) on Aug 20, 2009 at 07:28 UTC
It does not really matter how Toad is connecting to SQL Server, I only asked because you said you were using DBD::ODBC and were comparing Toad and ODBC. The point is that the SQL Server ODBC Driver has access to the main window in a MS windows environment and can therefore throw a dialogue up (probably the one you are seeing). The ODBC specification has an API called SQLDriverConnect that is used to connect to an ODBC Driver and it is that API that DBD::ODBC uses. The second argument to that API is a window handle and the last argument can be SQL_DRIVER_PROMPT which allows the driver to throw up a dialogue if it needs to. DBD::ODBC does not have a window handle as it is not a GUI application but an interface (and currently there is no way to pass one to it) so as a result it does not bother using the SQL_DRIVER_PROMPT argument to SQLDriverConnect and this prevents the SQL Server driver from throwing a dialogue. Toad on the other hand is a GUI app so IF it was using the SQL Server ODBC Driver (and the same probably applies to JDBC) it can pass a window handle to the driver and hence it can pop up a dialogue if it needs to.	[reply]
Re^4: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 20, 2009 at 13:33 UTC
Re^5: How to change expired database password via DBI? by mje (Curate) on Aug 20, 2009 at 15:53 UTC
Re^2: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 20, 2009 at 15:39 UTC
Thanks again, mje. I've e-mailed the maintainer of DBD::ODBC and asked if enhancing the module is a possibility. We'll see how it goes from here.	[reply]
Re^3: How to change expired database password via DBI? by mje (Curate) on Aug 20, 2009 at 15:54 UTC
I am the maintainer :-)	[reply]
Re: How to change expired database password via DBI? by Anonymous Monk on Aug 19, 2009 at 20:08 UTC
Don't know if this helps, but I believe you can link your MSSQL to AD and have folks leverage their AD logins or use Samba to change the Password. Just food for thought.	[reply]
Re^2: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 20, 2009 at 12:55 UTC
Yes I knew that, and I've done that in the past, but it's not an option in this case. The new database server is on a completely separate subdomain which (for security reasons) does not communicate with the Windows AD domain that the users are coming from.	[reply]
Re: How to change expired database password via DBI? by MarkovChain (Sexton) on Aug 19, 2009 at 14:44 UTC
I am not sure if you could directly do so through DBI but you could certainly use sqlplus and then automate the password generation and changing via Expect. Granted this is not a pure perl solution but it works.	[reply]
Re^2: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 19, 2009 at 16:02 UTC
There are a couple problems with that suggestion; One, sqlplus is Oracle, not SQL Server; two, many of our users do not have TOAD or Query Analyzer on their computers. But thanks for your suggestions.	[reply]
Re: How to change expired database password via DBI? by metaperl (Curate) on Aug 19, 2009 at 14:08 UTC
I'm connecting to a Microsoft SQL Server 2008 database via DBI (and thus DBD::ODBC). I think DBD::Sybase works as well? The question is: How can my programs allow users to change their expired passwords? This is possible, somehow. If one of us runs TOAD and tries to connect with an expired password, TOAD pops up a dialog saying "Your password has expired. Enter old password new password new password again to confirm", and the change goes through just fine. How can I make my Perl programs do this too? I dont know. It sounds like TOAD has some special access privs.	[reply]
Re: How to change expired database password via DBI? by Anonymous Monk on Aug 19, 2009 at 14:08 UTC
How does TOAD do it? Is it just a SQL query/update...?	[reply]
Re^2: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 19, 2009 at 19:47 UTC
I don't know. Wish I did.	[reply]
Re^3: How to change expired database password via DBI? by Anonymous Monk on Aug 19, 2009 at 23:04 UTC
It should be disclosed in the documentation for TOAD	[reply]