Hi, Johann -

A quick scan of the connection properties for SQL Server in the PB Connection Reference didn't turn up anything that looks like what you're inquiring about. This scenario is one reason our applications use Windows Authentication.

I'm guessing that you will have to recognize the "password expired" response from SQL Server, prompt/validate the user's new password, then issue the SQL Server ALTER LOGIN command to effect the password change. I suspect this is what the SQLCMD utility is doing under the covers.

HTH, John

I check for this in code. Note that we are an Oracle shop.

When the user types in their credentials into the LOGON window,
   Ensure that all three credentials were enters: userid, pwd, database
      (called "Environment" on the window)
   If CANCEL is hit, cancel and exit
   Use the credentials and attempt to connect. Check the return status.
   If we connect, check the status of the pwd
   If the user is in a grace period, they get the option to change their pwd
   If this is the last day of the grace period, the user is forced to change
   their pwd.

Hope This Helps,

Olan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ls_uid   = sle_userid.text               // OKnight, 07-DEC-2016, R#1472094.
ls_pwd = sle_password.text               // OKnight, 07-DEC-2016, R#1472094.

ll_rc = inv_logonattrib.ipo_source.dynamic event pfc_logon_env &
                (ls_uid, ls_pwd, ddlb_environment.text)   
if IsNull (ll_rc) then
    this.event pfc_cancel()
    GOTO Exit_Function
    
ElseIf ll_rc <= 0 Then
  If ii_logonattempts > 0 Then
        
        IF (ll_rc = -28001) THEN
            
            // Too late! Only the DBA can reset the users password now.
            MessageBox ("Password Has Expired", "Your password has expired. " + &
                        "Only the DBA can reset your password. ~r~n~r~n"      + &
                        "Please contact your DBA and ask them to reset your " + &
                        "password.", StopSign!)    
            ll_rc = FAILURE
            GOTO Exit_Function
            
        ELSE
            // We still have more attempts for a succesful login
            of_MessageBox ("pfc_logon_incorrectpassword", "Login", &
                           "The password is incorrect.", StopSign!, Ok!, 1)
                           sle_password.SetFocus()
    
            ll_rc = 0
        END IF
        
    Else
        // Failure return code
        ll_rc                 = -1
        inv_logonattrib.ii_rc = -1    
    End If

Else
    // Successful connection
    inv_logonattrib.ii_rc          = 1    
    inv_logonattrib.is_userid      = sle_userid.text
    inv_logonattrib.is_password = sle_password.text    
    // Added to support Oracle instance names - grf
    inv_logonattrib.is_environment = ddlb_environment.text
    
    // OKnight, 19-AUG-2005.  Set to UPPER before testing.
    // O Knight, 23-SEP-2004.
    IF (UPPER (gnv_app.is_database) = "ORA") THEN
        
        cb_pwd.Enabled = TRUE
    
        // Check the status of the current user account
        ll_result = this.FUNCTION of_check_pwd_status ()
        IF (ll_result = SUCCESS) THEN
            // Everything is good at this time, so proceed....
            
        ELSEIF (ll_result = 0) THEN
            // User is in a grace period, so send msg
            ll_rc = this.FUNCTION of_change_password (FALSE)
            
        ELSEIF (ll_result < 0) THEN
            // User must update their password NOW!
            ll_rc = this.FUNCTION of_change_password (TRUE)
        END IF
        
    ELSE
        cb_pwd.Enabled = FALSE
    END IF
    
End if

/////////////////////////////////

//*********************************************************************
// Object         :    corp_w_logon
// Function       :    of_Check_pwd_status()
//
// Ancestor       :    None
// Access         :    Public
// Arguments      :    None
//
// Returns        :    None
// Throws         :    None
//
// Description    :   Checks the account of the user currently logged on.
//                    If the user's password has expired, or if the user
//                    is in the last day of their GRACE period, then the
//                    return code will be sent that will force them to
//                    change their password.
//
//********************************************************************
// Revision History
//
// Developer    Date          Version      Description
// ---------    -----------   ---------    -------------------------------
// O Knight    08-SEP-2005    4.0.1.7      CEMS: Check for initial password.
// O Knight    10/03/2003     3.0.1.4      Initial version.
//
//********************************************************************
//  COPYRIGHT © 2017 CSG SYSTEMS INTERNATIONAL, INC. AND/OR ITS AFFILIATES
// (“CSG”). ALL RIGHTS RESERVED.
//********************************************************************
date          ldt_today, ldt_expire
datetime      ldt_expiry
long          ll_pos, ll_return, ll_diff
string        ls_hdr, ls_txt, ls_status, ls_expiry, ls_defpwd, ls_pwd
string        ls_applid



SetPointer (HourGlass!)
ll_return = SUCCESS                            // Init to indic a valid pwd
ls_hdr    = "Account Status"


// Get the account status for the current user
SELECT    TO_CHAR (expiry_date, 'mm/dd/yyyy' ),
          UPPER   (account_status),
          expiry_date
INTO      :ls_expiry, :ls_status, :ldt_expiry
FROM      user_users
USING     SQLCA ;

IF (sqlca.sqlCode <> 0) THEN
    ls_txt = "Unable to get the account status from the database." + &
             "The database error code is: " + String (SQLCA.SQLDBCode) + ".~r~n" + &
             "The error description is: "   + SQLCA.SQLErrText
    GOTO Exit_Function
END IF

// We have the account status for the user
ll_pos = POS (ls_status, "EXPIRED")
IF (ll_pos = 0) THEN GOTO CHECK_DEFAULT


// The account password has expired, so now see if we are
// in the middle of a grace period
ll_pos = POS (ls_status, "GRACE")
IF (ll_pos = 0) THEN
    ll_return = FAILURE            // User MUST change their pwd NOW!
    GOTO Exit_Function
END IF


// OKnight, 21-NOV-2005.   Force the user to change their password if
//                         they have less than two days Grace remaining.
// ---------------------------------------------------------------------

// See how many days remain in the GRACE period
ldt_expire = DATE (ldt_expiry)
ldt_today  = sqlca.of_currentdate ()
ll_diff    = DaysAfter (ldt_today, ldt_expire)
ls_txt     = "Your password will expire in " + string (ll_diff) + &
             " days (on " + ls_expiry + ").~r~n~r~n"

IF (ll_diff < 2) THEN
    
    // User MUST change their pwd NOW!
    ls_txt    = ls_txt + "You must change your password at this time."
    ll_return = FAILURE            
    GOTO Exit_Function
    
ELSE
    ls_txt = ls_txt + &
                "It is recommended that you change your password at this time."
    ll_return = 0    
END IF
// Now see if the user has supplied a default password....



// OKnight, 08-SEP-2005.  Check for the initial password.
// ------------------------------------------------------
CHECK_DEFAULT:
ls_applid = gnv_app.is_appl_id

// See if there is a "default_password" in the preferences
// table for this application.
SELECT  UPPER (TRIM (value))
INTO    :ls_defpwd
FROM    chas.PRFRNC
WHERE   appl_id      = :ls_applid
AND     upper (name) = 'DEFAULT PASSWORD' ;

// Proceed only if the entry exists
IF (sqlca.sqlcode = 0) THEN
    
    // Proceed only if the entry has a non-empty value
    IF (IsNull (ls_defpwd)) OR (TRIM (ls_defpwd) = "") THEN
        ll_return = 0
    ELSE
        // See if the password used to connect is the default password
        ls_pwd = TRIM (UPPER (sle_password.text))
        IF (ls_pwd = ls_defpwd) THEN
            
            // Password = default password, so force user to chg pwd
            ls_hdr = Upper (ls_applid)
            ls_txt = "Initial Login!"                         + &
                     "~r~n~r~nYou must change your password." + &
                        "~r~nClick OK to continue."
                        
            ib_initial_login = TRUE
            ll_return        = FAILURE        // User MUST change their pwd NOW!
            
        END IF
        
    END IF
    
END IF
// ------------------------------------------------------


Exit_Function:
    IF (ls_txt <> "") THEN
        MessageBox (ls_hdr, ls_txt)
    END IF
    
    RETURN ll_return

Thanks ever so much, but the problem is that the password has already expired so the new password has to be specified at login time SQLCMD has parameters that allow this SQLCMD -S MyServer -U MyUser -P MyPass -z MyNewPass

I was hoping that there was a property I could set so that the password can be changed when the connection is made as apposed to changing it after a successful login.  Given that SQLCMD can do this, this implies that SQL Server does cater for this feature, I must just find out if the client, in this case the transaction object SQLCA, can set the property.

I really appreciate the feedback, thanks ;-)

Thank you everyone for your input, I really appreciate it. I definitely think changing the application to use Windows authentication, as mentioned above, is the way to go.

A good day to you all.