7

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?

ParagJ
  • 1,566
  • 10
  • 38
  • 56
  • Can you show the exact command you used to start the server? – Eli Acherkan Jan 11 '12 at 10:34
  • 1
    How did you created the account with user=conn1 and password=conn1 ?? –  Jan 11 '12 at 11:36
  • @EliAcherkan: I am setting the following properties: server.database.0 = file:/E:\DB/myDB server.dbname.0 = myDB server.port = 9001 server.username = test server.password = test. I am then starting the server by server.start(). – ParagJ Jan 11 '12 at 12:21
  • Also noticed that if I start the HSQLDB in persistent mode (but non-server) the script file myDB.script contains the below statement: CREATE USER "test" PASSWORD DIGEST '098f6bcd4621d373cade4e832627b4f6' Thus it creates the user "test" with the password supplied. However this doesn't happen when I try to do the same with the server mode. The myDB.script in this case contains CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e' Means the default user SA is being created in server mode even if I set the other username using server.username property. – ParagJ Jan 11 '12 at 12:22
  • dear ParagJ, you seem to be working with spring-boot framework using properties. I am using a simple java app in netbeans with hsql url as con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mdb;file:E:/DB/mdb", "test", "test") but before that when i start server using cmd, i am unable to write the specific format in which username and password can be mentioned. If you can kindly share sample code using which u connected by giving ur specific username/password for server – Maha Saad Nov 03 '20 at 09:44

4 Answers4

14

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.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • I start the server with user=test and password=test. I then stop it by issuing SHUTDOWN. However while starting it again I don't need to provide the credentials now, why? I expect it should have asked me the credentials everytime I start the server, but that doesn't seem to happen. – ParagJ Jan 12 '12 at 08:42
  • Thanks. Now I am not sure if this this is an issue. I can start the server with user=test and password=test. Now when connecting to the server if I give the same credentials it doesn't connect. It asks for the same username, good. But I had to supply the blank password. Why is this happening? – ParagJ Jan 12 '12 at 11:57
  • Because you originally created the database with a blank password. The user/password in the server properties only applies to a new database, not an existing one. – fredt Jan 12 '12 at 15:05
  • Right. I must have created a database with blank password. I stopped the server, re-created the database and now I can connect to it by supplying the exact credentials. That's how it should work. Thanks @fredt for all your help. – ParagJ Jan 13 '12 at 10:08
  • Helped in setting the drive letter properly. Thanks. – Ayusman Sep 09 '13 at 16:52
  • @fredt java -cp hsqldb-2.5.0.jar org.hsqldb.server.Server --database.0 file:mydb;user=test;password=test --dbname.0 xdb I start server test user password but not able to connect – Ravat Tailor Jun 19 '20 at 06:18
  • when ruuning HSql UI:- url=jdbc:hsqldb:hsql://localhost/xdb, user=test & password =test – Ravat Tailor Jun 19 '20 at 06:19
  • @fredt Help; I have written java code as Server server = new Server(); HsqlProperties p = new HsqlProperties(); p.setProperty("server.database.1", "file:E:/db1/mwarePW;user=test;password=test"); p.setProperty("server.dbname.1", "mwarePW");server.setProperties(p); server.start(); Class.forName("org.hsqldb.jdbc.JDBCDriver"); con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mware;file:E:/db1/mwarePW;user=test;password=test"); //Exception: java.sql.SQLInvalidAuthorizationSpecException: invalid authorization specification - not found: test – Maha Saad Nov 03 '20 at 13:11
4

Appears the problem you were running into (at least initially) is that, for HSQL in memory databases, if it's the "first" in memory database (i.e. process just started), the username "has to be" sa (username "sa" is not case sensitive, or it can be empty username, which implies the "default" which is also sa). You can use a blank password, or specify a password. Based on some trial and error, if you want to reconnect to the same (in memory) DB later, you'll have to re-use the same password (blank or otherwise). If you want to use a user other than SA you'd probably have to first connect to your database using SA and execute some "create user" type commands to create new users. Then reconnect using that user (assuming your DB is all in memory).

You can use multiple different in-memory databases (if that's what you're trying to accomplish by specifying a different user) like this:

// change the MySpecialTestDb String for multiple different in memory databases
// or reuse the same value
// to reconnect to a previously created in memory database [i.e. within the same process previously].
String DB_CONNECTION_STR = "jdbc:hsqldb:mem:MySpecialTestDb"; 
String DB_USERNAME_STR = "sa";
String DB_USERNAME_PASSWORD = "";
DriverManager.getConnection(DB_CONNECTION_STR, DB_USERNAME_STR, DB_USERNAME_PASSWORD);

Each new database you create follows the same system (it must be initial user SA and "adopts" whatever first password you give).

ref: http://www.hsqldb.org/doc/1.8/guide/guide.html#advanced-chapter

Or if you want to just "reset" an in memory database, like between each unit test, see here.

Note that documentation also says "...This feature [default user SA] has a side effect that can confuse new users. If a mistake is made in specifying the path for connecting to an existing database, a connection is nevertheless established to a new database. For troubleshooting purposes, you can specify a connection property ifexists=true ..."

rogerdpack
  • 62,887
  • 36
  • 269
  • 388
2

Point no 1) Whenever you create a DB, you have to specify the username and password. You can keep it both blank; But same username and password has to be used while connecting to server.

If you observe script file of your DB, you can see commands like :-

CREATE USER "usr" PASSWORD DIGEST '9003d1df22eb4d3820015070385194c8'
ALTER USER "usr" SET LOCAL TRUE
GRANT DBA TO "usr"

I had created DB with user name "usr" so it appeared in script file in those commands. Now while starting server I do not need to specify user name or password. It will IGNORE this information.

While connecting server you have to give exactly same username and password, you gave while creating DB.

Point no 2) Make sure that there is no space in path of your DB files. If there is space then enclose the whole path in double quotes. I struggled a lot to find out this silly mistake of mine.

Now if I start the server wil below command it starts correctly

1) Go to lib of HSQL 

cd C:\Users\owner\Documents\Java Project\hsqldb-2.2.9\hsqldb\lib

Then give command 

java -cp hsqldb.jar org.hsqldb.Server -database.0 file:"C:\Users\owner\Documents\Java Project\hsqldb-2.2.9\TmpDBLocation\myKauDB" -dbname.0 xdb

2) In other command prompt went to lib location 

cd C:\Users\owner\Documents\Java Project\hsqldb-2.2.9\hsqldb\lib

Then connected the Swing UI of HSQL DB by giving command in other command prompt window

java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing --driver org.hsqldb.jdbcDriver --URL jdbc:hsqldb:hsql://localhost/xdb --user "usr" --password ""
Kaushik Lele
  • 6,439
  • 13
  • 50
  • 76
0

In my brand new 2.3.2 installation, after clicking bin/runServer.bat, I managed to connect (with Squirrel) using:

URL: jdbc:hsqldb:hsql://localhost:9001
User: SA
Password: <blank>
Agustí Sánchez
  • 10,455
  • 2
  • 34
  • 25