CREATE EXTERNLOGIN statement

Assigns an alternate login name and password to be used when communicating with a remote server.

Syntax

Create external login for a remote server

CREATE EXTERNLOGIN login-name
TO remote-server
[ REMOTE LOGIN remote-user [ IDENTIFIED BY remote-password ] ]

Create external login for a remote server (include variables in syntax)

CREATE EXTERNLOGIN USER string | variable
SERVER string | variable
[ REMOTE USER string | variable [ IDENTIFIED BY string | variable ] ]

Create external login for a directory access server
```
CREATE EXTERNLOGIN login-name
TO remote-server
```
Create external login for a directory access server (include variables in syntax)
```
CREATE EXTERNLOGIN USER string | variable
SERVER string | variable
```

Parameters

login-name Specifies the local user login name. When using integrated logins, login-name is the database user to which the Windows user or group is mapped.
TO clause The TO clause specifies the name of the remote server.
REMOTE LOGIN clause The REMOTE LOGIN clause specifies the user account on remote-server for the local user login-name. Values for the REMOTE LOGIN clause are restricted to 128 bytes.
user-name
Specifies the database user name. For remote servers, when using integrated logins, the user-name is the database user to which the Windows user or group is mapped. This value can be a string or a variable.
SERVER clause
Specify the name of the remote server or the directory access server.
REMOTE USER clause (remote server)
The REMOTE USER clause specifies the user account on the remote server for the database user name. Values for the REMOTE USER clause are restricted to 128 bytes.
- IDENTIFIED BY clause
  Specify the remote password for the remote user. The remote user and remote password combination must be valid on the remote server. The This clause applies only to remote servers, not to directory access servers.
  
  If you omit the IDENTIFIED BY clause, then the password is sent to the remote server as NULL. However, if you specify IDENTIFIED BY "" (an empty string), then the password sent is the empty string.

Remarks: Remote servers

CREATE EXTERNLOGIN assigns an alternate login name and password to be used when communicating with a remote server.

Connections to a remote server are first attempted using the current executing user's external login. If this user does not have an external login, then the connection is attempted using the DEFAULT LOGIN credentials. If the remote server was created without a DEFAULT LOGIN, and no external login has been defined for the user, then the connection is attempted with the current executing user's ID and password.

The REMOTE LOGIN clause is required only when the remote server requires a user ID and password for the connection. Having an external login without a remote login allows the DBA to control who can access the remote server and tells the remote access layer that logging in to the remote server does not require a user ID and password.

The password is stored internally in encrypted form. The remote-server must be known to the local server by an entry in the ISYSSERVER table.

Sites with automatic password expiration should plan for periodic updates of passwords for external logins.

CREATE EXTERNLOGIN cannot be used from within a transaction.

If you use this statement in a procedure, do not specify the password (IDENTIFIED BY clause) as a string literal because the definition of the procedure is visible in the SYSPROCEDURE system view. For security purposes, specify the password using a variable that is declared outside of the procedure definition.

Note For required parameters that accept variable names, the database server returns an error if any of the following conditions is true:

The variable does not exist
The contents of the variable are NULL
The variable exceeds the length allowed by the parameter
The data type of the variable does not match that required by the parameter

Remarks (directory access servers)

By default, database users must have external logins to access the directory access server. However, you can configure the directory access server to remove this requirement by creating a default external login that all users can use.

CREATE EXTERNLOGIN assigns an external login to be used when accessing a directory access server.

CREATE EXTERNLOGIN cannot be used from within a transaction.

Privileges

You must have the MANAGE ANY USER system privilege.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

This fictitious example maps a local user, DBA, to user sa with password Plankton when connecting to the server server1.

CREATE EXTERNLOGIN DBA
TO server1
REMOTE LOGIN sa
IDENTIFIED BY Plankton;