-
-
Notifications
You must be signed in to change notification settings - Fork 23
Jaybird and Firebird 3
This article describes how to use Firebird 3.0 with Jaybird.
Jaybird 3.0.4 and higher support Firebird 3 wire encryption
Jaybird 3.0 versions earlier than 3.0.4 do not support the new wire encryption. To get Jaybird to work with Firebird 3, either upgrade to Jaybird 3.0.4 or higher, or change the WireCrypt setting from its default Required to Enabled (or Disabled, but that is not advisable as that will also disable it for clients that do support wire encryption).
WireCrypt = Enabled
To be able to connect with wire encryption, the authentication plugin needs to be able to generate an encryption key. This is only possible with Srp and, in Firebird 4 and higher, Win_Sspi. If you want to be able to connect with the Legacy_Auth plugin, you will need to relax the WireCrypt setting to Enabled. We strongly advice you to switch to Srp or Srp256.
Jaybird 3 supports the new SRP authentication mechanism. See the Firebird 3 release notes for details.
Support for database encryption callbacks was introduced in Jaybird 3.0.4. See the Jaybird 3.0.x release notes, section Database encryption support for details.
Jaybird 2.2.x does not support the new wire encryption, to get Jaybird to work with Firebird 3, it is necessary to change the WireCrypt setting from its default Required to Enabled (or Disabled, but that is not advisable as that will also disable it for clients that do support wire encryption).
WireCrypt = Enabled
Firebird 3 introduces a new authentication mechanism, combined with wire protocol encryption. This new authentication mechanism (SRP) is not supported by Jaybird 2.2.x and earlier when you use the pure java wire protocol (the default).
SRP will be supported in Jaybird 3.0, we plan to provide support for wire encryption in Jaybird 3.1.
For the Jaybird 2.2 pure java wire protocol to authenticate to Firebird 3 you need to enable legacy authentication and correctly setup your users. If you are using Jaybird 3.0 this is not necessary.
Setting up legacy authentication for Firebird 3 requires modifying the firebird.conf by adding (or modifying) the line with AuthServer = :
AuthServer = Srp, Win_Sspi, Legacy_Auth
Restart the Firebird service after you have saved the file.
The option Srp is the default new authentication, Win_Sspi is for the trusted Windows authentication, and Legacy_Auth enables the legacy authentication mechanism of Firebird 2.5 and earlier.
By default users created through SQL user management, the services API, gsec or another way will be setup for Srp. These users don't exist for the legacy authentication and trying to authenticate will result in the Exception: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544472. Your user name and password are not defined. Ask your database administrator to set up a Firebird login.
To setup a user for the legacy authentication, you need to change the UserManager in the firebird.conf to:
UserManager = Srp, Legacy_UserManager
The default value is UserManager = Srp. Restart Firebird server after changing this. Next you need to create the user, for example to create a user jaybird with password jdbc. This can be done using the new SQL based user management (eg through ISQL or Flamerobin with the sysdba account):
CREATE USER jaybird PASSWORD 'jdbc' USING PLUGIN Legacy_UserManagerIf you want to use the - deprecated(!) - gsec or servicemanager, you need to change the UserManager in the firebird.conf to:
UserManager = Legacy_UserManager
You don’t need to restart the Firebird service after changing this if you are going to use gsec (TODO: Also for services API?).
To create the user, for example user jaybird with password jdbc:
> gsec -user sysdba -password masterkey
GSEC> add jaybird -pw jdbc
After doing this you should be able to connect to Firebird 3 with Jaybird 2.2.8. I'd suggest to revert the UserManager setting in firebird.conf to its default after you have set up the users.
Firebird 3 introduces a few new datatypes.
Firebird 3 adds the BOOLEAN datatype (sqltype 32764/32765). This datatype is supported by Jaybird 2.2.4 and later.
In Jaybird 2.2.3 and earlier, using boolean values (in select, insert, etc) of this types will yield the following exception:
Exception in thread "main" org.firebirdsql.jdbc.field.TypeConversionException: SQL type for this field is not yet supported.
at org.firebirdsql.jdbc.field.FBField.createException(FBField.java:186)
at org.firebirdsql.jdbc.field.FBField.createField(FBField.java:468)
at org.firebirdsql.jdbc.field.FBField.createField(FBField.java:379)
at org.firebirdsql.jdbc.AbstractResultSet.prepareVars(AbstractResultSet.java:200)
at org.firebirdsql.jdbc.AbstractResultSet.<init>(AbstractResultSet.java:144)
at org.firebirdsql.jdbc.FBResultSet.<init>(FBResultSet.java:46)
at org.firebirdsql.jdbc.AbstractStatement.getResultSet(AbstractStatement.java:913)
at org.firebirdsql.jdbc.AbstractStatement.getResultSet(AbstractStatement.java:894)
at org.firebirdsql.jdbc.AbstractStatement.executeQuery(AbstractStatement.java:225)
A workaround is to wrap the field in an IIF (or CASE):
SELECT IIF(boolvalue, 1, 0) AS boolvalue FROM booltableOr for insert, use an expression with a boolean result in the value list:
INSERT INTO booltable(id, boolvalue) VALUES (?, 1 = ?)This works because the second parameter will be identified as an integer, and when you use setBoolean() on the PreparedStatement, it will use 1 for true and 0 for false.
Firebird 3.0 introduces an autoincrement field (<datatype> GENERATED BY DEFAULT AS IDENTITY). Jaybird 2.2.x does not have identity support, in other words it will be handled like any other <datatype> NOT NULL field. For example in DatabaseMetaData.getColumns() will not report YES for the column IS_AUTOINCREMENT (instead it reports empty string, meaning "it cannot be determined whether the column is auto incremented"). In Jaybird 3, this will report YES for an identity column.
Other operations, including retrieving generated keys, will work for this column, just as it works for normal columns.
There currently are no issues. If you come across any problems with using Jaybird on Firebird 3, please report them on Firebird-Java or in the Firebird JDBC tracker.