Thursday, June 21, 2012

Firebird: connecting to an embedded server

As you can read in a recent post I have discovered that using the SYSDBA user to connect to a deployed database from a program is not a good idea.
Since my main development platform is Windows I have used a Windows database manager program to add a new user with its own password, and that user is the owner of the database and its objects. The new username and password are stored in the security2.fdb file in the Firebird installation.

If the program connects to a server it uses those username and password and everything works as expected.
The program can also use and embedded server for smaller customers where a single user is enough. The Windows embedded server will allow any username and password, without checking them, but you must use the correct username or you will not be able to open the database tables. So I ended up using an empty password for embedded connections and it worked under Windows.

As soon as I tested it on a Mac I discovered that I could not connect to the database: wrong username or password.
It happens that it is possible to set up an embedded server under Linux and Mac, as I described in older posts, but these servers will still use security2.fdb to authenticate the user.

The Firebird server stores usernames and passwords in the security2.fdb file, so I solved the problem copying the security2.fdb file from my Windows server (the one containing the default username and password to use for non-SYSDBA access) and using it for the Linux and Mac embedded servers.
Of course I also had to use the right password for embedded access.

My program has an option to let the user connect to a server or use an embedded one.
If he connects to a server he will be able to enter a username and a password.
If he chooses an embedded server the program will always connect with the username and password used to develop the database. Shipping that security2.fdb file with the Linux and Mac embedded servers will make sure that the connection will be successful.

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Is there any way to encrypt the firebird database file on-the-fly like in sqlite.

      Delete
  2. I am working on a similar project, the problem is firebird table values can be edited in any text or hex editor easily, in a accounting application this seems to be a ugly situation anyone out there have a workaround? Like a UDF or something?

    ReplyDelete