There are two ways to handle an Expired Password in the Advanced SQL Engine:

Connection String Attribute -- New Password

If this attribute is specified in the connection string, the expired password will be reset to the new password.

Refer to TdConnectionStringBuilder.NewPassword property for more details.

TdConnection.InfoMessage Event

This message is fired when the Advanced SQL Engine password has expired and pooling is turned off. When this event is triggered the application should respond to this event by performing the following actions:

1. Change the Teradata password.
2. Close the TdConnection.
3. Open the TdConnection.

Refer to the TdConnection.InfoMessage event for more details.

Scenarios

When coding an application to handle expired passwords, there are three scenarios that must be considered:

Password expires while the application is using a Connection Pool.

Suppose an application is using a connection pool and the password expired on the Advanced SQL Engine while the connection pool is active. When this occurs, the active sessions can still access Teradata. However, when the provider attempts to create another connection to the SQL Engine, it will attempt to reset the password with the value specified with the New Password attribute. Unfortunately, the provider is currently unable to reset an expired password while there are active connections in the pool. Therefore, a TdException will get thrown that indicates the password could not be modified.

Connection pooling is off and the New Password attribute is specified in the connection string.

When a password becomes expired on the SQL Engine, the provider will use the value specified with the New Password attribute and attempt to reset the password. If an attempt to reset the password fails, the provider will throw a TdException that indicates the password could not be modified.

If the password has been successfully reset, the TdConnection.InfoMessage event will be fired and the property TdInfoMessageEventArgs.Number will be set to 101006 --Expired Password successfully changed.

Connection pooling is off and the New Password attribute is not specified in the connection string.

This is similar to the previous scenario, except that the provider will not attempt to reset the password. The provider will fire the TdConnection.InfoMessage event, and a connection to Teradata will be opened. However, the only sql that can be executed is MODIFY USER <username> PASSWORD=<new password>. A TdException will get thrown if an any other type of statement is executed.

In addition, the property TdInfoMessageEventArgs.Number will be set to 101007 --Expired Password, MODIFY USER is the only command supported.

Example

The following is a snippet of code that sets up the TdConnection.InfoMessage event.

// This method is invoked when the TdConnection.OnInfoMessage is fired.
public void OnInfoMessage(Object sender, TdInfoMessageEventArgs i)
{
    // The TdConnection.OnInfoMessage event gets fired when TdConnection.ChangeDatabase
    // is called to change the default database, an expired password has been reset,
    // or a call to TdCommand.ExecuteCreateProcedure is made to create a stored
    // procedure that generates warning messages.

    // Going to check whether a TdConnection.ChangeDatabase was called or an
    // expired password has been reset.
    if (101001 == i.Number)
    {
       //TdConnection.ChangeDatabase has been called.

       // PERFORM ACTION BASED ON EVENT
    }
    else if (101006 == i.Number || 101007 == i.Number)
    {
       // An expired password has been reset

       // PERFORM ACTION BASED ON EVENT
    }
}

public void ConnectToTeradata(String username, String password, String newPassword )
{
    TdConnection cn = null;

    try
    {
       TdConnectionStringBuilder conStrBuilder = new TdConnectionStringBuilder();
       conStrBuilder.ConnectionPooling = false;
       conStrBuilder.SessionMode = "ANSI";
       conStrBuilder.UserId = username;
       conStrBuilder.Password = password;
       conStrBuilder.NewPassword = newPassword;

       // The connection string will appear as
       // "User Id=user;Password=password;Connection Pooling=false;New Password=newPassword"
       cn = new TdConnection(conStrBuilder.ConnectionString);

       // Setting up the event that will get fired if the password expires
       cn.InfoMessage += new TdInfoMessageEventHandler(this.OnInfoMessage);

       cn.Open();

    }
    catch (TdException e)
    {
       if (cn != null)
       {
          cn.Close();
       }
    }
}