Error SQL1101N When Accessing Federation Objects Without User Mapping

Symptom

When accessing remote data source without user mapping defined for user who connects to federated database, you might encounter one of the following scenarios:

1) Error SQL1101N will return if user connects to federated database without providing explicit authorization ID and password. E.g.

CONNECT to <fed_db>

select * from nickname1

SQL1101N Remote database "remote_db" on node "" could not be accessed with the specified authorization id and password. SQLSTATE=08004

2) Accessing data source succeeds if you explicitly specify authorization ID and password for connection to federated database. E.g.

CONNECT to <fed_db> USER <username> USING <password>

select * from nickname1

-- successfully get results

Note: This query can work only when <username> and <password> are also valid for the remote database meaning the federated <username> and <password> are the same as the target database username and password

3) With the same operations as 2), we can get SQL1101N error if the federated authorization ID and password specified for connection to federated database are not valid for the remote database.