HSQLDB server mode username/password

Question

If I start the HSQLDB in server mode using my Java code, the server starts without any problem. However, when I try to connect to the same either through the Java code or through the HSQLDB DatabaseManagerSwing; I am unable to connect.

I started the server with user=conn1 and password=conn1 in memory-only mode. But when connecting to the server it gave me following exception:

java.sql.SQLInvalidAuthorizationSpecException: invalid authorization specification - not found: conn1

I can only connect by giving user=SA and blank password. I am using HSQLDB 2.2.5 and JRE1.7 on Windows7 machine.

Can someone tell me where am I doing wrong?

Answer 1

If you try these server properties with recent versions of HyperSQL, you will probably get an error message as your server properties are not correct. The properties "server.username" and "server.password" are not valid. And the dbname.0 property must be in lowercase.

If you want to create a server database with a user name other than SA, you can append the user and password to the database path:

server.database.0 = file:E:/DB/myDB;user=testuser;password=testpw
server.dbname.0 = mydb

After the server is shutdown, there is no need to include the user and password. The credentials are used only to create the database. After that, the credentials are checked when a connection is made to the server.

2020 update with additional information due to recent questions in comments:

1. The user name and password specified for database.0 are taken into account only when a new database is created by starting the server. If the database files exist before starting the server, user name and password are unnecessary and are simply ignored.

2. Other settings for a new database, such as hsqldb.tx=mvcc, can be appended to the database.0 string.

3. You must have properties for database.0 for your server. You can add properties for database.1 if your server is serving two different databases.

4. The file path specified for database.0 is hidden from the users that connect to the server. Only the dbname.0 value is used for access, for example: DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb;uer=testuser;password=testpw")

5. In the getConnection call, it is better to state the user and password separately to keep the code clear:DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "testuser", "testpw")

6. See the Guide http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html for all the details.