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

Connect in Powerbuilder to sql server after password expired

The SNC does not have the PWdialog and I can't change the expired SQL Server
2005 expired password since I get a message transaction not connected.

What is the method to force the password change upon first login ?


0
Rich-Alaska
Asked:
Rich-Alaska
  • 5
  • 3
1 Solution
 
diasroshanCommented:
Hi,

http://msdn.microsoft.com/en-us/library/ms131024.aspx
ODBC User Interface Password Expiration
The SQL Server Native Client ODBC driver supports password expiration through changes made to the SQL Server Login dialog boxes.

If SQLDriverConnect is called and the value of DriverCompletion is set to SQL_DRIVER_NOPROMPT, the initial connection attempt fails if the password has expired. The SQLSTATE value 28000 and the native error code value 18487 are returned by subsequent calls to SQLError or SQLGetDiagRec.

If DriverCompletion has been set to any other value, the user sees the SQL Server Login dialog, regardless of whether or not the password has expired. The user can click on the Options button and check Change Password to change the password.

If the user clicks OK and the password has expired, SQL Server prompts to enter and confirm a new password using the Change SQL Server Password dialog.

Cheers,
Rosh
0
 
Rich-AlaskaAuthor Commented:
Sorry but I'm using the OLEDB version of SQL NATIVE CLIENT not the ODBC.

Will this work for OLEDB ?
0
 
diasroshanCommented:
Hi,

It will work with OLEDB,

http://msdn.microsoft.com/en-us/library/ms131024.aspx
 SQL Server Native Client OLE DB Provider
The SQL Server Native Client OLE DB provider supports password expiration though a user interface and programmatically.

OLE DB User Interface Password Expiration
The SQL Server Native Client OLE DB provider supports password expiration through changes made to the SQL Server Login dialog boxes. If the value of DBPROP_INIT_PROMPT is set to DBPROMPT_NOPROMPT, the initial connection attempt will fail if the password has expired.

If DBPROP_INIT_PROMPT has been set to any other value, the user sees the SQL Server Login dialog, regardless of whether or not the password has expired. The user can click on the Options button and check Change Password to change the password.

If the user clicks OK and the password has expired, SQL Server prompts the user to enter and confirm a new password using the Change SQL Server Password dialog.

OLE DB Prompt Behavior and Locked Accounts
Connection attempts may fail due to the account being locked. If this occurs following the display of the SQL Server Login dialog, the server error message is displayed to the user and the connection attempt is aborted. It may also occur following the display of the Change SQL Server Password dialog if the user enters a bad value for the old password. In this case the same error message is displayed, and the connection attempt is aborted.

OLE DB Connection Pooling, Password Expiration, and Locked Accounts
An account may be locked or its password may expire while the connection is still active in a connection pool. The server checks for expired passwords and locked accounts on two occasions. The first is when a connection is first created. The second occasion is upon connection reset, when the connection is taken from the pool.

When the reset attempt fails, the connection is removed from the pool and an error is returned.

OLE DB Programmatic Password Expiration
The SQL Server Native Client OLE DB provider supports password expiration through the addition of the SSPROP_AUTH_OLD_PASSWORD (type VT_BSTR) property that has been added to the DBPROPSET_SQLSERVERDBINIT property set.

The existing "Password" property refers to DBPROP_AUTH_PASSWORD and is used to store the new password.

Cheers,
Rosh
0
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.

 
Rich-AlaskaAuthor Commented:
Can you show an actual example of the connection string. I have read the same documentation and cannot make it work.

I'm using Powerbuilder.

Thanks
0
 
Rich-AlaskaAuthor Commented:
can I just get a simple example please.

I'm confused on the format of the connection string using these driver specific parameters

Thanks
0
 
Rich-AlaskaAuthor Commented:
unfortunately the answer is no good without a example.
0
 
diasroshanCommented:
Hi,

Unfortunately, i havent been able to get my hands on SQL Server to try and give u some example. U cud always give it a try urself and we can help u move ahead from there. But there needs to be a starting point.

Cheers,
Rosh
0
 
Rich-AlaskaAuthor Commented:
I understand and appreciate any help if you get access. However, realize I had been trying to code this up for about a week before I posted.  

Thanks,
Richard
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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