ODBC User Authentication
There are several methods for authorizing users from within ODBC applications.
User Name and Password
SQLConnect() and SQLDriverConnect() allow your application to specify a user (login) name and password. For example:
SQLConnect(hdbc, /* Connection handle */
(SQLCHAR *)"myDSN", /* The ODBC DSN definition */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)"myUserName", /* Local user name */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)"myPassword, /* Local password */
SQL_NTS); /* This is a null-terminated string */
In SQLDriverConnect(), the "uid" and "pwd" attributes specify the local user name and local password, respectively.
Specification of the user name and password authorizes the ODBC application to act as it were logged in to the local machine as that user. This is true even if the ODBC DSN definition specifies a network connection to another machine. If you specify an invalid user name or password, the connection fails regardless of whether the connection target is local or over the network.
Ingres Super Users
An Ingres user with security administrator privileges can assume the identity of other users without having to provide a password. Such users are called Ingres "super" users, as in Linux and Unix notation. An example in SQLConnect() follows:
SQLConnect( hdbc, /* Connection handle */
(SQLCHAR *)"myDSN", /* The ODBC DSN definition */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)"altUserName", /* Alternate username */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)NULL, /* No password required */
SQL_NTS ); /* This is a null-terminated string */
The example code allows the ODBC application to authorize as "altUserName" without a password. In functional terms, this is the equivalent to the -u flag in Terminal Monitor.
Note: The local user name must be an Ingres super user when connecting locally. If connecting remotely, the user name defined in netutil for the target vnode must be an Ingres super user.
Installation Passwords
SQLConnect() and SQLDriverConnect() can be used with installation passwords. The following code example demonstrates the use of installation passwords with SQLDriverConnect(). Note that the user name and password are not specified.
strcpy( connectString, "driver={Ingres};servertype=Ingres;" \
"server=vnodeDef;database=remoteDB" );
SQLDriverConnect( hdbc, /* Connection handle */
0, /* Window handle */
connectString, /* Connection string */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)NULL, /* Output (result) connection string */
SQL_NTS, /* This is a null-terminated string */
0, /* Length of output connect string */
SQL_DRIVER_NOPROMPT ); /* Don't display a prompt window */
If the ODBC application executes in the local "ingres" account, the target Name Server authenticates "ingres" using the installation password for the target database. You cannot use installation passwords for a local database unless the connection is done through a vnode definition.
The Ingres super user concept applies to installation passwords. Ingres super users can specify alternate user names, but do not need to supply passwords, as shown in the following SQLConnect() example:
SQLConnect( hdbc, /* Connection handle */
(SQLCHAR *)"myDSN", /* The ODBC DSN definition */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)"altUserName", /* Alternate username */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)NULL, /* No password required */
SQL_NTS ); /* This is a null-terminated string */
The example above assumes "myDSN" references a vnode definition.
DBMS Passwords
If the target installation is not configured for DBMS authentication, DBMS passwords are extra passwords defined for Ingres users and maintained in the Ingres database. When an Ingres user account is associated with a DBMS password, the correct DBMS password must be supplied as a connection parameter, otherwise the connection attempt will be rejected.
DBMS passwords in the DSN definition in odbc.ini are not supported. The ODBC Driver supports DBMS passwords only through SQLDriverConnect(), as shown in the following example:
/*
** The "dbms_pwd" connection attribute specifies the DBMS password.
*/
strcpy( connectString, "driver={Ingres};servertype=Ingres;" \
"server=(local);database=myDB;uid=myUserName; \
"pwd=myPassword;dbms_pwd=myDBMS_password );
SQLDriverConnect( hdbc, /* Connection handle */
0, /* Window handle */
connectString, /* Connection string */
SQL_NTS, /* Nll-terminated string */
(SQLCHAR *)NULL, /* Output connection string */
SQL_NTS, /* N=ull-terminated string */
0, /* Length of output connect string */
SQL_DRIVER_NOPROMPT ); /* No prompt window */
If the target installation is configured for DBMS authentication, user names and passwords are defined in the DBMS Server. In such cases, DBMS passwords are ignored, because they are not "extra" authentication. Rather, DBMS passwords are effectively the same as OS passwords in environments that do not support DBMS authentication.
The code in the previous example would still work, provided that "myPassword" was defined in the DBMS. For DBMS authentication, only the definition in the DBMS Server matters, not whether the DBMS name and password are also defined by the operating system.
Kerberos
User name or password specifications are not required in a Kerberos authentication environment, as shown in the following example:
SQLConnect( hdbc, /* Connection handle */
(SQLCHAR *)"myDSN", /* The ODBC DSN definition */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)NULL, /* No username required */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)NULL, /* No password required */
SQL_NTS ); /* This is a null-terminated string */
For Kerberos authentication to succeed, the following requirements must be met:
• Kerberos must be installed and configured on the Kerberos client and Kerberos KDC (Kerberos Domain Controller).
• The current login session must have valid Kerberos tickets obtained via the "kinit" command or from a Kerberos authorization utility such as Leash on Windows.
• An Ingres service principal name must be defined with keytabs defined in a Kerberos keytab file.
• Kerberos must be configured for Ingres via the "iisukerberos" command and possibly the CBF utility.
• The ODBC DSN definition "myDSN" must reference a vnode definition, regardless of whether the connection is to a local or remote database.
• The vnode definition must define the "authentication_mechanism" attribute as "kerberos".
For a full discussion of Kerberos authentication in an Ingres environment, see the Security Guide.
SQLConnect() authenticates the application according to the Kerberos principal. The user name of the Kerberos principal is passed to the DBMS as the owner of the connection; otherwise, no authentication information is passed between the client and server when SQLConnect() is invoked. Authentication is performed by the Ingres Service Principal of the server-side Name Server.
Dynamic (Run-Time) Authentication (Windows Only)
Some ODBC applications require that the user name and password are not hard-coded in the code. To address these requirements, ODBC applications can prompt the user for more information.
When prompted for connection information, the ODBC Driver displays the following input page:
Users can then enter the missing login information in the prompt window, and the application will attempt to connect using the supplied information.
Connection Prompt Window--Using SQLDriverConnect()
The ODBC Driver supports the DriverCompletion argument in SQLDriverConnect(). The DriverCompletion argument is the eighth and last argument in SQLDriverConnect(). The value SQL_DRIVER_PROMPT directs the driver to display a prompt window. The value SQL_DRIVER_NOPROMPT directs the driver to not display a prompt window.
Example: DriverCompletion Argument
/*
** Fill the connection string with the minimum
** connection information.
*/
strcpy( connectString, "driver={Ingres};servertype=Ingres;" \
"server=(local);database=myDB" );
/* Connect to the database using the ODBC DSN definition. */
SQLDriverConnect( hdbc, /* Connection handle */
winHandle, /* Window handle */
connectString, /* Connection string */
SQL_NTS, /* This is a null-terminated string */
(SQLCHAR *)NULL, /* Output (result) connection string */
SQL_NTS, /* This is a null-terminated string */
0, /* Length of output connect string */
SQL_DRIVER_PROMPT ); /* Display a prompt window */
The example above displays a prompt window in a program that has a valid handle to a Windows application. Windows-sensitive applications such as ADO or the .NET Data Provider for ODBC are usually able to display prompts.
Note: ODBC programs that make no calls to the Windows API cannot display connection prompt windows. ADO programs executed as ASP pages from IIS cannot display prompt windows.
Connection Prompt Window--Using DSN Definition
SQLDriverConnect() is not visible in Windows applications such as ADO or the .NET Data Provider for ODBC. It is not possible to modify the Driver Completion argument in these cases. Ingres ODBC DSN definition pages include a Prompt User ID and Password check box that forces the application to prompt for more information, as in the following example:
If the application references the ODBC DSN definition at connect time, the prompt window is displayed.
Note: IIS applications, such as ASP pages, will not display prompt windows.
Specification of User Names and Passwords in ODBC
For remote connections, the specification of user names and passwords overrides the user names or passwords in the vnode definition.
For local connections, the specification of user names and passwords causes the ODBC application to behave as if it was logged in as the alternate user. This behavior is true regardless of whether or not the current login is an Ingres "super" user.
It does not matter if user names and passwords are provided interactively or are hard-coded, except in the case of Kerberos. For Kerberos, if you specify user names and passwords, the ODBC application authenticates against the local installation in addition to the Kerberos authentication. The Kerberos user specified in "kinit" must match the user name specified in SQLConnect() or SQLDriverConnect().
SQLBrowseConnect()--Prompt for Connection Information
SQLBrowseConnect() is another function that can be used to prompt for connection information. SQLBrowseConnect() returns missing connection information in a formatted output string that is used as a guide in the next connection attempt. When SQLBrowseConnect() receives the minimum connection information, a connection is made to the database.
Last modified date: 08/28/2024