I had 2 servers that were using Firebird 2.5. Each of the servers had a separate database and one of them was connecting to the other one to retrieve some data. One of the servers switched to Firebird 3.0 and now it can't connect to the 2.5 server. It says my Username or Password is incorrect. I've connected to the 2.5 server with the credentials and they are OK.
To retrieve data I was using Execute statement [STATEMENT] on external Datasource [SERVER] as user [USER] password [PASSWORD].
The Firebird 2.5 server has more databases and it would be a hassle to upgrade it to Firebird 3.0.
How can I resolve this issue?
I have done some testing with a Firebird 2.5 (2.5.8) and Firebird 3 (3.0.4) on the same server with different ports using the following simple statement (and modifying some parts depending on what I'm testing) to see what kind of connection failures I can produce.
set term #;
execute block returns (tblname char(31))
as
begin
for execute statement 'select rdb$relation_name from rdb$relations where coalesce(rdb$system_flag, 0) = 0'
on external data source 'localhost/3051:D:\data\db\testdatabase.fdb'
as user 'sysdba' password 'masterkey'
into tblname
do suspend;
end#
set term ;#
Using this statement, I can get error "Your user name and password are not defined." from Firebird 3 to 2.5 in the following situations:
Firebird 3 has a AuthClient1 configuration that does not include Legacy_Auth. Firebird 3 cannot authenticate to Firebird 2.5 as Firebird 2.5 only knows about the legacy authentication mechanism.
To fix this, add Legacy_Auth to the AuthClient setting in firebird.conf of the Firebird 3 server (for example set it to AuthClient = Srp, Legacy_Auth) and restart the server.
This point is most likely your problem.
Not specifying username and password (i.e. leaving as user 'sysdba' password 'masterkey' out of the execute statement). This is probably due to the difference in authentication mechanisms as Firebird doesn't know the actual password with the SRP protocol and therefor will not be able to authenticate to the other server.
Specifying a username and password fixes this.
In reverse direction (Firebird 2.5 to 3), I cannot establish a connection in the following situations:
Authenticating with a username and password that exists only as a Srp user. This results in error "Your user name and password are not defined." as Firebird 2.5 only supports the legacy authentication, and as a result can only authenticate with users that exists for the Legacy_UserManager plugin in Firebird 3.
Create a user (either with the same name or a different name) for the Legacy_UserManager plugin:
create user theuser password 'thepassword' using plugin Legacy_UserManager;
commit;
If this results in error "Missing requested management plugin", then you need to edit the Firebird 3 firebird.conf and add Legacy_UserManager to the UserManager setting (e.g. set to UserManager = Srp, Legacy_UserManager; the default is only Srp) and restart Firebird.
As SYSDBA you can check on the Firebird 3 server for which plugin (or plugins!) a user exists by executing
select SEC$USER_NAME, SEC$PLUGIN
from SEC$USERS
Firebird 3 has setting WireCrypt = Required (this is the default!). This produces the error "connection rejected by remote interface".
To fix this, set WireCrypt = Enabled in the firebird.conf of the Firebird 3 server and restart the server.
Not specifying username and password (i.e. leaving as user 'sysdba' password 'masterkey' out of the execute statement). This produces an error "unknown ISC error 335545106" (actual message is "Error occurred during login, please check server firebird.log for details" if a Firebird 3 message file is used) where the log of Firebird 3 says "No matching plugins on server", this is probably due to the difference in authentication mechanisms.
Specifying a username and password fixes this.
Firebird 3 has a AuthServer configuration that does not include Legacy_Auth (the default is Srp only!). This also produces error "unknown ISC error 335545106" (actual message is "Error occurred during login, please check server firebird.log for details" if a Firebird 3 message file is used) where the log of Firebird 3 says "No matching plugins on server".
To fix this, add Legacy_Auth to the AuthServer setting in firebird.conf of the Firebird 3 server (for example set it to AuthServer = Srp, Legacy_Auth) and restart the server.
And, of course, in both directions error "Your user name and password are not defined." can also be produced by using non-existent users or wrong passwords.