Adaptive Server users who invoke Component Integration Services require login names and passwords to remote servers. By default, the user name and password pair used by Component Integration Services to connect to a remote server is the same as is used by the client to connect to Adaptive Server.
Component Integration Services supports a one-to-one mapping of Adaptive Server login names and passwords to remote server login names and passwords. For example, using the stored procedure sp_addexternlogin, it is possible to map Adaptive Server user steve, password sybase to Oracle login name login1, password password1:
sp_addexternlogin Oracle, steve, login1, password1
In Adaptive Server version 12.5 and later, you can provide a many-to-one mapping so that all Adaptive Server users who need an Oracle connection can be assigned the same name and password:
sp_addexternlogin Oracle, NULL, login2, password2
One-to-one mapping has precedence, so that if user steve has an external login for Oracle, that would be used rather than the many-to-one mapping.
In addition, you can assign external logins to Adaptive Server roles. With this capability, anyone with a particular role can be assigned a corresponding login name/password for any given remote server:
sp_addexternlogin Oracle, null, login3, password3, rolename
The role name identifies the name of a role, rather than the name of a user. When a user with this role active requires a connection to Oracle, the appropriate login name/password for the role is used to establish the connection. When establishing a connection to a remote server for a user who has more than one role active, each role is searched for an external login mapping, and the first mapping found is used to establish the login. This is the same order as displayed by sp_activeroles.
The general syntax for sp_addexternlogin is:
sp_addexternlogin <servername>, <loginname>, <external_loginname>, <external_password> [, <rolename>]
<rolename> is optional; if specified then loginname is ignored.
Precedence for these capabilities are as follows:
If one-to-one mapping is defined, it is used.
If no one-to-one mapping is defined, and a role is active and a mapping for it can be found, the role mapping is used to establish a remote connection.
If neither of the above are true, then many-to-one mapping is used if defined.
If none of the above is true, then the Adaptive Server login name and password are used to make the connection.
If role mapping is done, and a user’s role is changed (via set role), any connections made to remote servers that used role mapping are disconnected.
sp_helpexternlogin has been updated to allow viewing the various types of external logins that have been added using sp_addexternlogin. The syntax for sp_helpexternlogin is:
sp_helpexternlogin [<servername> [,<loginname> [,<rolename>]]]
All three parameters are optional, and any of the parameters can be NULL.
The stored procedure sp_dropexternlogin also accepts the <rolename> argument. If <role name> is specified then the second argument, <login name>, is ignored.
