User's Guide Part III. Using SQL Anywhere Chapter 15. Connecting to a DatabaseThe integrated loginfeature allows you to maintain a single user ID and password for both database connections and operating system and/or network logins. This section describes the integrated login feature.
An integrated login is a mapping from one or more Windows NT user profiles to an existing user in a SQL Anywhere database. A user who has successfully navigated the security for that user profile and logged in to their machine can connect to a database without providing an additional user ID or password. To accomplish this, the database must be enabled to use integrated logins and a mapping must have been granted between the user profile used to log in to the machine and/or network, and a database user.
Using an integrated login is more convenient for the user and permits a single security system for database and network security. Its advantages include:
Sybase SQL Anywhere provides integrated login capabilities for Windows NT versions of the server and standalone engine. As well, it is possible for Windows NT and Windows 95 clients to use integrated logins to connect to a network server running on Windows NT.
The integrated login feature was introduced and only works with release 5.5.03 engines and servers, and with databases that have been upgraded to 5.5.03 standards. Databases created prior to release 5.5.03 can be upgraded using the Upgrade utility.
For more information about the Upgrade utility see, "The Upgrade utility".
Several steps must be implemented in order to successfully connect via an integrated login.
To use an integrated login:
Another option is to temporarily enable integrated logins. For more information about the implications of temporarily setting the integrated login option see "Setting temporary public options for added security".
Each of these steps is described in the sections below.
The login_mode database option determines whether the integrated login feature is enabled. As database options apply only to the database in which they are found, different databases can have a different integrated login setting even if they are loaded and running within the same engine or server.
The login_mode database option accepts one of following three values (the values are case insensitive).
| Caution Setting the LOGIN_MODE database option to Integrated restricts connections to only those users who have been granted an integrated login mapping. Attempting to connect using a user ID and password generates an error.The only exception to this are users with DBA authority (full administrative rights). |
The following SQL statement sets the value of the login_mode database option to Mixed, allowing both standard and integrated login connections.
SET OPTION Public.LOGIN_MODE = Mixed
User profiles can only be mapped to an existing database user ID. When that database user ID is removed from the database, all integrated login mappings based on that database user ID are automatically removed.
A user profile does not have to exist for it to be mapped to a database user ID. More than one user profile can be mapped to the same user ID.
Only users with DBA authority are able to create or remove an integrated login mapping.
An integrated login mapping is made either using a wizard in Sybase Central or a SQL statement.
In Sybase Central, databases capable of hosting integrated login connections (those upgraded to versions 5.5.03 and above) will have an additional folder within their database container named Integrated Logins. Within that folder is a wizard for adding integrated logins to a database.
The wizard displays all the available user profile names of that local machine but does not restrict the choice of user profile to the names on that list.
The wizard displays all the available database users and restricts the choice to only those user IDs.
The following SQL statement allows Window NT users dmelanso and bhay to log in to the database as the user DBA, without having to know or provide the DBA user ID or password.
GRANT INTEGRATED LOGIN TO dmelanso, bhay AS USER dba
A client application can connect to the database engine or server using an integrated login in one of the following ways:
If integrated=yes is specified in the connection string, an integrated login is attempted. If the connection attempt fails and the LOGin_mode database option is set to Mixed, the engine or server attempts a standard login.
If an attempt to connect to a database is made without providing a user ID or password, an integrated login will be attempted which will succeed or fail depending on whether the current user profile name matches a integrated login mapping in the database.
For example, a connection attempt using the following SQL statement will succeed, providing the user has logged on with a user profile name that matches a integrated login mapping in a default database of an engine or server.
CONNECT USING 'INTEGRATED=yes'
Under certain conditions, the following SQL statement can create a connection to a database.
CONNECT
Those conditions include:
A client application connecting to a database via ODBC can use an integrated login by including the Integrated parameter among other attributes in its Data Source configuration.
Setting the attribute 'Integrated=yes' in a Data Name Source (DSN) will cause database connection attempts using that DSN to attempt an integrated login. If the login_mode database option is set to Standard, the SQL Anywhere ODBC driver will prompt the user for a database user ID and password.
There is no user interface for setting this DSN attribute. It must be added by either editing the DSN, the ODBC connection string, or making ODBC API calls.
As of release 5.5.03 of SQL Anywhere, Public User ID options could be made temporary, meaning the value change is in effect for as long as the database remains running. When the database is shutdown, the value of the option reverts to the previous, permanent value.
Assuming the permanent value of the login_mode option for a given database is Standard, setting the value to Mixed or Integrated using the following SQL statement will permanently enable integrated logins for that database.
SET OPTION Public.LOGIN_MODE = Mixed
If the database is shutdown and restarted, the option value remains the same and integrated logins are still enabled.
Changing the login_mode option temporarily will still enable integrated logins and allow user access via integrated logins. The following statement will change the option value temporarily.
SET TEMPORARY OPTION Public.LOGIN_MODE = Mixed
If the permanent option value is Standard, the database will revert to that value when it is shut down.
Setting temporary public options can be considered an additional security measure for database access since enabling integrated logins means the database is relying on the security of the operating system on which it is running. If the database is shut down and copied to another machine (such as a user's machine) access to the database reverts to the SQL Anywhere security model and not the security model of the operating system of the machine where the database has been copied.
For more information on using the SET OPTION statement see "SET OPTION statement".
If the database is located remotely, on a network server, then one of two conditions must be met for integrated login to be used:
For example, when the user TSLEE attempts to connect using an integrated login to a database loaded on network server, identical user profile names and passwords must exist on both the local machine and application server hosting the database. TSLEE must be permitted to login to both the local machine and the server hosting the SQL Anywhere network server.
A default integrated login user ID can be created so connecting via an integrated login will be successful even if no integrated login mapping exists for the user profile currently in use.
For example, if no integrated login mapping exists for the user profile name TSLEE, an integrated login connection attempt will normally fail when TSLEE is the user profile in use.
However, by creating a user ID named Guest in a database, an integrated login will successfully map to the Guest user ID on the condition that no integrated login mapping explicitly identifies the user profile TSLEE.
The default integrated login user permits anyone attempting an integrated login to successfully connect to a database if the database contains a user ID named Guest. The permissions and authorities granted to the newly-connected user are determined by the authorities granted to the Guest user ID.