Cannot get Spring Boot + Hibernate + Firebird database to work together

Question

It seems that I cannot find decent solution to my problem. I cannot figure out how to properly configure a Spring Boot REST project and Firebird 3.0 to work together in an Eclipse environment. I use kubuntu 20.04 Focal and already preinstalled Firebird 3.0 server, and added my people.fdb file in a src/main/resources folder and gave rw permissions to all groups. I also transferred a fdb file to a firebird:firebird ownership. I'm not sure if this should be like this. I constantly get error when run:

2022-06-04 20:54:42.331 ERROR 6948 --- [  restartedMain] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.

java.sql.SQLException: Error occurred during login, please check server firebird.log for details [SQLState:08006, ISC error code:335545106]
    at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:539) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:304) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:140) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.AbstractWireOperations.processOperation(AbstractWireOperations.java:204) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readOperationResponse(AbstractWireOperations.java:163) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.version13.V13WireOperations.authReceiveResponse(V13WireOperations.java:125) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.version10.V10Database.authReceiveResponse(V10Database.java:569) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.WireConnection.identify(WireConnection.java:335) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.FbWireDatabaseFactory.performConnect(FbWireDatabaseFactory.java:51) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.FbWireDatabaseFactory.connect(FbWireDatabaseFactory.java:39) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.FbWireDatabaseFactory.connect(FbWireDatabaseFactory.java:32) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.jca.FBManagedConnection.<init>(FBManagedConnection.java:145) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.jca.FBManagedConnectionFactory.createManagedConnection(FBManagedConnectionFactory.java:599) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.jca.FBStandAloneConnectionManager.allocateConnection(FBStandAloneConnectionManager.java:65) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.jdbc.FBDataSource.getConnection(FBDataSource.java:109) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.jdbc.FBDriver.connect(FBDriver.java:114) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-4.0.3.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:181) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:68) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:175) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:173) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1460) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1494) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.20.jar:5.3.20]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.20.jar:5.3.20]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.20.jar:5.3.20]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:734) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1295) ~[spring-boot-2.7.0.jar:2.7.0]
    at lt.company.peopleDatabase.PeopleDatabaseApplication.main(PeopleDatabaseApplication.java:12) ~[classes/:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-2.7.0.jar:2.7.0]
Caused by: org.firebirdsql.jdbc.FBSQLExceptionInfo: Error occurred during login, please check server firebird.log for details

2022-06-04 20:54:42.335  WARN 6948 --- [  restartedMain] o.h.e.j.e.i.JdbcEnvironmentInitiator     : HHH000342: Could not obtain connection to query metadata

java.sql.SQLException: Error occurred during login, please check server firebird.log for details [SQLState:08006, ISC error code:335545106]
    at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:539) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:304) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:140) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.AbstractWireOperations.processOperation(AbstractWireOperations.java:204) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readOperationResponse(AbstractWireOperations.java:163) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.version13.V13WireOperations.authReceiveResponse(V13WireOperations.java:125) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.version10.V10Database.authReceiveResponse(V10Database.java:569) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.WireConnection.identify(WireConnection.java:335) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.FbWireDatabaseFactory.performConnect(FbWireDatabaseFactory.java:51) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.FbWireDatabaseFactory.connect(FbWireDatabaseFactory.java:39) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.gds.ng.wire.FbWireDatabaseFactory.connect(FbWireDatabaseFactory.java:32) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    at org.firebirdsql.jca.FBManagedConnection.<init>(FBManagedConnection.java:145) ~[jaybird-4.0.6.java8.jar:4.0.6.java8 (build: variant=jaybird tag=v4.0.6 date=202204271624)]
    ..............

t(HikariPool.java:561) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-4.0.3.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:181) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:44) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    ... 40 common frames omitted
Caused by: org.firebirdsql.jdbc.FBSQLExceptionInfo: Error occurred during login, please check server firebird.log for details`

My pom.xml configuration:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>lt.company</groupId>
    <artifactId>PeopleDatabase</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <name>PeopleDatabase</name>
    <description>People personal info storage database to quickly parse info only providing id number and/or date of birth</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web-services</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/org.firebirdsql.jdbc/jaybird -->
        <dependency>
           <groupId>org.firebirdsql.jdbc</groupId>
           <artifactId>jaybird</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/javax.resource/javax.resource-api -->
        <dependency>
            <groupId>javax.resource</groupId>
            <artifactId>javax.resource-api</artifactId>
            <version>1.7.1</version>
        </dependency>



    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

application.properties:

spring.datasource.url:jdbc:firebirdsql://localhost:3050/~/Documents/Coding/projektai/ergo/PeopleDatabase/src/main/resources/people.fdb
spring.datasource.driverClassName:org.firebirdsql.jdbc.FBDriver
spring.jpa.database-platform=org.hibernate.dialect.FirebirdDialect
spring.datasource.username:SYSDBA
spring.datasource.password:masterkey
spring.jpa.hibernate.ddl-auto:update
spring.jpa.show-sql: true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.open-in-view=false

I am able to connect to that database using isql-fb CLI tool, and perform all of the operations there, but I suspect that something might be not OK with my server itself. firebird3.0.service when trying to connect is active.

Maybe someone will have something in mind on how to correctly configure such setup? By the way, in error message, it suggests checking firebird.log file, but when running

find / -type f -iname "firebird.log"

gives no results.

I am beginner with this database.

Answer 1

Most likely the problem is that your Firebrd server is configured with the insecure setting to only allow legacy authentication. This means its firebird.conf has setting AuthServer = Legacy_Auth (N.B. the default for Firebird 3.0 is AuthServer = Srp).

Since Jaybird 4, Jaybird - the Firebird JDBC driver - no longer uses the Legacy_Auth authentication plugin by default.

You have basically two options.

1. Configure Firebird server to use the Srp and/or Srp256 authentication plugin, and make sure your user exists for the Srp user management plugin.

   To do this, in firebird.conf, set AuthServer = Srp (or Srp, Legacy_Auth if you still need legacy authentication), and UserManager = Srp (or Srp, Legacy_UserManager if you still need legacy authentication). In addition, you need to make sure the user exists for Srp:

   create or alter user <username> password '<password>' using plugin srp;
   

2. Configure Jaybird to use the Legacy_Auth authentication plugin by specifying connection property authPlugins with value Legacy_Auth. This is less work than the previous option, but also less secure (Legacy_Auth limits passwords to 8 characters, and doesn't support wire protocol encryption).

   You can specify this by adding the following to your application properties:

   spring.datasource.hikari.data-source-properties.authPlugins = Legacy_Auth