databaseconnectionfirebirdisql

Firebird database SYSDBA connection error

I just installed Firebird for Win64, and I was trying to connect to the employee database which comes pre-packaged with ISQL. Following the steps from the Firebird official Quick Start Guide, I opened the ISQL utility and entered:

connect localhost:employee user sysdba password masterkey;

As a result I got:

Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.

screenshot of IQSL utility

The strangest thing is that if I navigate to the employee database sample itself and issue the isql command from there I can successfully connect.

screenshot of Windows Command Prompt

Solution

  • The difference is that connecting directly to a database file doesn't require a password, it will even ignore the password, and just use the provided user to know which privileges to apply.

    Without a hostname, ISQL will by default use Firebird embedded mode, and not the server. To compare, try using isql employee.fdb (or isql employee), it will just login with your current OS username, while isql localhost:employee will fail with a 'Your user name and password are not defined'.

    It looks like you specified a different password than the default of masterkey, or somehow the sysdba account wasn't initialized. I recall there was a problem with the installer of an earlier Firebird 3 version, but I don't think 3.0.2 should be affected by this (or at least: it worked for me).

    If the SYSDBA account wasn't initialized, then follow the steps of the Firebird 3 release notes, section Initializing the Security Database:

    Initialization Steps

    Initialization is performed in embedded mode using the isql utility. For an embedded connection, an authentication password is not required and will be ignored if you provide one. An embedded connection will work fine with no login credentials and “log you in” using your host credentials if you omit a user name. However, even though the user name is not subject to authentication, creating or modifying anything in the existing security database requires that the user be SYSDBA; otherwise, isql will throw a privilege error for the CREATE USER request.

    The SQL user management commands will work with any open database. Because the sample database employee.fdb is present in your installation and already aliased in databases.conf, it is convenient to use it for the user management task.

    1. Stop the Firebird server. Firebird 3 caches connections to the security database aggressively. The presence of server connections may prevent isql from establishing an embedded connection.

    2. In a suitable shell, start an isql interactive session, opening the employee database via its alias:

      > isql -user sysdba employee

    3. Create the SYSDBA user:

      SQL> create user SYSDBA password 'SomethingCryptic';
SQL> commit;
SQL> quit;

    4. To complete the initialization, start the Firebird server again. Now you will be able to perform a network login to databases, including the security database, using the password you assigned to SYSDBA.

    Where 'SomethingCryptic', should be your password.

    If a SYSDBA user was created, you will need to change its password if you no longer remember what you set. Follow the same steps, but in step 3 do:

    SQL> alter user SYSDBA set password '<new password>';
SQL> commit;
SQL> quit;

    If this gives an error "record not found for user: SYSDBA", make sure you are really connected as SYSDBA, otherwise retry the original step 3. Not having admin access will behave as if the user doesn't exist, so the error is the same if the user really doesn't exist, or if you are connected with an unprivileged user.