Firebird database SYSDBA connection error

Question

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 QuickStart Documentation 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.

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

Solution 1

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.

Solution 2

CONNECT 'employee' user 'SYSDBA' password 'masterkey';

You need make sure that your alias.conf have something like this: employee=C:/examplepath/employee.fdb make sure that the services of firebird is on

Comments

[Marco]

Thanks for your answer, it helped a lot. I did the alter user command which changed the password for the sysdba user. What I then noticed was that the 'localhost:employee' was also giving an error, so I ditched the localhost part and now I can connect. The following is now working: "connect employee user sysdba password <my password>;"

[Mark Rotteveel]

@Marco If you changed the password, it should work. What was the error? If you used isql localhost:employee, then yes that is going to fail, as that doesn't include the username and password, which is required when connecting through the server.

[Marco]

SQL> connect localhost:employee user sysdba password <my password>; Statement failed, SQLSTATE = 08006 Error occurred during login, please check server firebird.log for details SQL> -As you can see, I'm including the user and password, maybe this is an issue with Win10, just guessing here...

[Mark Rotteveel]

@Marco Are you running Firebird as an application instead of as a service? In that case, you need to make sure Firebird is started with elevated privileges (Run as Administrator), or install it in a location outside the Program Files folder.

[Marco]

Indeed that was the issue, I was running Firebird as an application without elevated privileges, now it's working fine. That's great Mark, thanks a lot and have a great 2018! :)

[qwerty_so]

Whoa. You saved me. Never would have figured out that.