sp_addexternlogin

(Component Integration Services only) Creates an alternate login account and password to use when communicating with a remote server through Component Integration Services.

Syntax

sp_addexternlogin server, loginame, externname 
	[, externpasswd] [rolename]

Parameters

Examples

Usage

There are additional considerations when using sp_addexternlogin:
  • 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.

    Note: You can use sp_addexternlogin only when Component Integration Services is 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 is 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 SAP ASE login and password.

  • You can assign external logins to SAP ASE 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 the SAP ASE server with sp_addserver.

  • externname and externpasswd 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 loginame account are the only users who can modify remote access for a given local user.

Permissions

The permission checks for sp_addexternlogin differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage any remote login privilege. Any user can execute sp_addexternlogin for their own login.

Disabled

With granular permissions disabled, you must be a user with sa_role or sso_role. Any user can execute sp_addexternlogin for their own login.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_addserver
sp_dropexternlogin
sp_helpexternlogin
sp_helpserver