Component Integration Services only Creates an alternate login account and password to use when communicating with a remote server through Component Integration Services.
sp_addexternlogin remote_server, login_name, remote_name [, remote_password] [role_name]
is the name of the remote server. The remote_server must be known to the local server by an entry in the master.dbo.sysservers table.
is an account known to the local server. login_name must be represented by an entry in the master.dbo.syslogins table. The “sa” account, the “sso” account, and the login_name account are the only users authorized to modify remote access for a given local user.
is an account known to the remote_server and must be a valid account on the node where the remote_server runs. This is the account used for logging into the remote_server.
is the password for remote_name.
is the Adaptive Server user’s assigned role. If role_name is specified, login_name is ignored.
Tells the local server that when the login name “bobj” logs in, access to the remote server OMNI1012 is by the remote name “jordan” and the remote password “hitchpost”. Only the “bobj” account, the “sa” account, and the “sso” account have the authority to add or modify a remote login for the login name “bobj”:
sp_addexternlogin OMNI1012, bobj, jordan, hitchpost
Shows a many-to-one mapping so that all Adaptive Server Enterprise users that need a connection to DB2 can be assigned the same name and password:
sp_addexternlogin DB2, NULL, login2, password2
Adaptive Server Enterprise roles can also be assigned remote logins. With this capability, anyone with a particular role can be assigned a corresponding login name and password for a given remote server:
sp_addexternlogin DB2, NULL, login3, password3, role
sp_addexternlogin assigns an alternate login name and password to be used when communicating with a remote server. It stores the password internally in encrypted form.
You can use sp_addexternlogin only when Component Integration Services is installed and configured.
Mappings can be one-to-one (for specific users), role-to-one (role-based) , many-to-one (server-based), or based on the client login and password from the TDS loginrec.
The login and password have a many to one mapping. That is, you can assign all the users who need to log into a remote server the same name and password.
When several external logins are set for a user, the following precedence will be followed for user connections to a remote server. 1) one-to-one mapping, 2) if there is no one-to-one mapping, active role is used, 3) if neither one-to-one mapping nor active role is present, then many-to-one mapping, 4) if none of the above is used then Adaptive Server Enterprise login and password.
You can assign external logins to Adaptive Server roles. You can assign anyone with a particular role a corresponding login name and password for any given remote server.
When you establish a connection to a remote server for a user that has more than one role active, each role is searched for an external login mapping and uses the first mapping it finds to establish the login. This is the same order as displayed by the stored procedure sp_activeroles.
If you perform role mapping, and a user's role is changed (using set role), any connections made to remote servers that used role mapping must be disconnected. You cannot do this if a transaction is pending. You cannot use set role if a transaction is active and remote connections are present that used role mapping.
Before running sp_addexternlogin, add the remote server to Adaptive Server with sp_addserver.
remote_name and remote_password must be a valid user and password combination on the node where the server runs.
Sites with automatic password expiration need to plan for periodic updates of passwords for external logins.
Use sp_dropexternlogin to remove the definition of the external login.
sp_addexternlogin cannot be used from within a transaction.
The “sa” account and the login_name account are the only users who can modify remote access for a given local user.
Only the login_name, a System Administrator, and a System Security Officer can execute sp_addexternlogin.
System procedures sp_addserver, sp_dropexternlogin, sp_helpexternlogin, sp_helpserver