You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Michael Remijan <mj...@live.com> on 2021/03/19 14:05:44 UTC
Embedded database, authentication, and derby.system.home
Greetings,
I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
/projectname
/bin
start.sh // starts the application
/data
/projectdb // this is the derby database.
My projectdb database also has a specific username, password, and permissions/roles on that user.
When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
... 67 more
Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 84 more
Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
Thanks!
Mike
Re: Embedded database, authentication, and derby.system.home
Posted by Rick Hillegas <ri...@gmail.com>.
The ij CONNECT command issues a DriverManager.getConnection(jdbcURL)
command. What does your connection logic look like?
Thanks,
-Rick
On 3/23/21 4:59 AM, Michael Remijan wrote:
> I'll see if I can reproduce it with ij, but I'm not sure. The error is with the Java client connecting to and starting an embedded database. There's no SQL at play since it's the connecting and starting that's failing. If I can reproduce it with ij I'll let you know.
>
> -----Original Message-----
> From: Rick Hillegas <ri...@gmail.com>
> Sent: Monday, March 22, 2021 1:09 PM
> To: derby-user@db.apache.org
> Subject: Re: Embedded database, authentication, and derby.system.home
>
> Sorry. I need SQL scripts. I can't make sense of a compiled application
> with no source code.
>
> On 3/22/21 7:05 AM, Michael Remijan wrote:
>> Hi Rick,
>>
>> Here is a working Java example for you. It's fully self-contained with its own JRE so all you need do is download it and run it. This bin distribution is for Windows, so sorry if you're on another platform. It's too big for email, so I have it on my OneDrive https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2F1drv.ms%2Fu%2Fs!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ%3Fe%3DnpfMBd&data=04%7C01%7C%7Cc6fb22f26340407b616708d8ed5d8d2e%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637520333338352293%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=jBxtsRB9m9TvwKtQyrIrTFjajbrz%2FUTDqXbECfFuhD4%3D&reserved=0
>>
>> First, download, unzip, then run /bin/start-resiste.bat. You'll see the following:
>> [cid:image001.png@01D71EF8.C4AFB090]
>>
>>
>> Next, m move the /data directory to a different location and then update /conf/db.properties.
>> [cid:image002.png@01D71EF9.233513D0]
>>
>>
>> Next, run /bin/start-resiste.bat again. You'll see the following error:
>> [cid:image003.png@01D71EF9.233513D0]
>>
>>
>> Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync with the new location of the database:
>> [cid:image004.png@01D71EF9.3A7B5450]
>>
>> Next, run /bin/start-resiste.bat again. You'll see it's now successful with /conf/db.properties and /bin/start-resiste.bat in sync
>> [cid:image001.png@01D71EF8.C4AFB090]
>>
>>
>> Now here is a big Caveat. I discovered this while getting this demo together. I have Derby 10.14.2.0 installed on my computer. For my first attempt at this demo, I created the database from scratch using 10.14.2.0. When I did this, I DID NOT experience the connection error as described above. However, when I use my original database, I DO experience the error as demonstrated above. Unfortunately, I do not remember which version of Derby I used to create the original database, but obviously it was an older version. So perhaps this has something to do with an older database version?
>>
>> Mike
>>
>>
>>
>>
>>
>> From: Rick Hillegas <ri...@gmail.com>
>> Sent: Friday, March 19, 2021 4:04 PM
>> To: derby-user@db.apache.org
>> Subject: Re: Embedded database, authentication, and derby.system.home
>>
>> Hi Michael,
>>
>> I must be missing something in your description of the problem, because I cannot reproduce it. I have created the following scripts in order to reproduce the behavior of both changing the value of derby.system.home and moving the database to another directory:
>>
>> First SQL script nativeTest.sql:
>>
>> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
>>
>>
>>
>> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
>>
>>
>>
>> -- shutdown in order to enable NATIVE authentication
>>
>> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
>>
>>
>>
>> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
>>
>>
>>
>> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
>>
>>
>>
>> CREATE TABLE t(a INT);
>>
>> INSERT INTO t VALUES (1), (2);
>>
>>
>> Second SQL script nativeTest2.sql:
>>
>> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
>>
>>
>>
>> SELECT * FROM t;
>>
>>
>> Then a shell script (z1) to run nativeTest.sql, move the database to another directory, then run nativeTest2.sql:
>>
>> #! /bin/bash
>>
>> #
>>
>> # Run a derby test
>>
>>
>>
>> rm -rf z/db1
>>
>> rm -rf zz/db1
>>
>> rm zzz/*
>>
>> rm zzzz/*
>>
>>
>>
>> . setupClasspath
>>
>>
>>
>> java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest.sql
>>
>>
>>
>> mv z/db1 zz/
>>
>>
>>
>> java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest2.sql
>>
>>
>> When I run z1, I get the following output:
>>
>> ij version 10.16
>>
>> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
>>
>> ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
>>
>> 0 rows inserted/updated/deleted
>>
>> ij> -- shutdown in order to enable NATIVE authentication
>>
>> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
>>
>> ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.
>>
>> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
>>
>> ij(CONNECTION1)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
>>
>> 1
>>
>> --------------------------------------------------------------------------------------------------------------------------------
>>
>> NATIVE::LOCAL
>>
>>
>>
>> 1 row selected
>>
>> ij(CONNECTION1)> CREATE TABLE t(a INT);
>>
>> 0 rows inserted/updated/deleted
>>
>> ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);
>>
>> 2 rows inserted/updated/deleted
>>
>> ij(CONNECTION1)> ij version 10.16
>>
>> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
>>
>> ij> SELECT * FROM t;
>>
>> A
>>
>> -----------
>>
>> 1
>>
>> 2
>>
>>
>>
>> 2 rows selected
>>
>>
>> Can you post a similar set of scripts which demonstrate the problem you are seeing?
>>
>> Thanks,
>> -Rick
>>
>> On 3/19/21 11:14 AM, Michael Remijan wrote:
>>
>> NATIVE.
>>
>>
>>
>> After creating the database I, I set the 'sa' user like this:
>>
>>
>>
>> ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxxxxxxxxxxxxxx');
>>
>>
>>
>> Then I create the application user like this:
>>
>>
>>
>> CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxxxxxxxxxxxxxx');
>>
>>
>>
>> And I have the following in derby.properties
>>
>>
>>
>> # Passwords don't expire for 20 years
>>
>> derby.authentication.native.passwordLifetimeMillis=631138520000
>>
>> # Use the best hash algorithm you can
>>
>> derby.authentication.builtin.algorithm=SHA-512
>>
>> # Use a larger salt length for better security
>>
>> derby.authentication.builtin.saltLength=128
>>
>> # Re-hash this number of times for better security
>>
>> derby.authentication.builtin.iterations=1564
>>
>>
>>
>> BTW, I've updated code to dynamically System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of user configuration. That been successful. So if I move the database to somewhere other than my application's default location, I set derby.system.home at runtime based on that configuration.
>>
>>
>>
>> I also found that this is only a factor with embedded databases where the path to the database is a fully qualified file system path. If the configuration uses a remote database, the value for derby.system.home doesn't seem to matter.
>>
>>
>>
>> -----Original Message-----
>>
>> From: Rick Hillegas <ri...@gmail.com>
>>
>> Sent: Friday, March 19, 2021 11:22 AM
>>
>> To: derby-user@db.apache.org<ma...@db.apache.org>
>>
>> Subject: Re: Embedded database, authentication, and derby.system.home
>>
>>
>>
>> Hi Mike,
>>
>>
>>
>> What kind of authentication are you using: LDAP, NATIVE, or custom?
>>
>>
>>
>> On 3/19/21 7:05 AM, Michael Remijan wrote:
>>
>> Greetings,
>>
>>
>>
>> I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
>>
>>
>>
>> I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
>>
>>
>>
>> /projectname
>>
>> /bin
>>
>> start.sh // starts the application
>>
>> /data
>>
>> /projectdb // this is the derby database.
>>
>>
>>
>> My projectdb database also has a specific username, password, and permissions/roles on that user.
>>
>>
>>
>> When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
>>
>>
>>
>> Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
>>
>>
>>
>> Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
>>
>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>>
>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>>
>> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>>
>> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>>
>> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
>>
>> at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
>>
>> at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
>>
>> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
>>
>> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
>>
>> at java.security.AccessController.doPrivileged(Native Method)
>>
>> at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
>>
>> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
>>
>> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
>>
>> at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
>>
>> at java.sql.DriverManager.getConnection(DriverManager.java:664)
>>
>> at java.sql.DriverManager.getConnection(DriverManager.java:247)
>>
>> at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
>>
>> ... 67 more
>>
>> Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
>>
>> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>>
>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
>>
>> ... 84 more
>>
>>
>>
>> Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
>>
>>
>>
>> Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
>>
>>
>>
>> Thanks!
>>
>> Mike
>>
>>
>>
>>
>>
>>
RE: Embedded database, authentication, and derby.system.home
Posted by Michael Remijan <mj...@live.com>.
I'll see if I can reproduce it with ij, but I'm not sure. The error is with the Java client connecting to and starting an embedded database. There's no SQL at play since it's the connecting and starting that's failing. If I can reproduce it with ij I'll let you know.
-----Original Message-----
From: Rick Hillegas <ri...@gmail.com>
Sent: Monday, March 22, 2021 1:09 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home
Sorry. I need SQL scripts. I can't make sense of a compiled application
with no source code.
On 3/22/21 7:05 AM, Michael Remijan wrote:
> Hi Rick,
>
> Here is a working Java example for you. It's fully self-contained with its own JRE so all you need do is download it and run it. This bin distribution is for Windows, so sorry if you're on another platform. It's too big for email, so I have it on my OneDrive https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2F1drv.ms%2Fu%2Fs!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ%3Fe%3DnpfMBd&data=04%7C01%7C%7Cc6fb22f26340407b616708d8ed5d8d2e%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637520333338352293%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=jBxtsRB9m9TvwKtQyrIrTFjajbrz%2FUTDqXbECfFuhD4%3D&reserved=0
>
> First, download, unzip, then run /bin/start-resiste.bat. You'll see the following:
> [cid:image001.png@01D71EF8.C4AFB090]
>
>
> Next, m move the /data directory to a different location and then update /conf/db.properties.
> [cid:image002.png@01D71EF9.233513D0]
>
>
> Next, run /bin/start-resiste.bat again. You'll see the following error:
> [cid:image003.png@01D71EF9.233513D0]
>
>
> Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync with the new location of the database:
> [cid:image004.png@01D71EF9.3A7B5450]
>
> Next, run /bin/start-resiste.bat again. You'll see it's now successful with /conf/db.properties and /bin/start-resiste.bat in sync
> [cid:image001.png@01D71EF8.C4AFB090]
>
>
> Now here is a big Caveat. I discovered this while getting this demo together. I have Derby 10.14.2.0 installed on my computer. For my first attempt at this demo, I created the database from scratch using 10.14.2.0. When I did this, I DID NOT experience the connection error as described above. However, when I use my original database, I DO experience the error as demonstrated above. Unfortunately, I do not remember which version of Derby I used to create the original database, but obviously it was an older version. So perhaps this has something to do with an older database version?
>
> Mike
>
>
>
>
>
> From: Rick Hillegas <ri...@gmail.com>
> Sent: Friday, March 19, 2021 4:04 PM
> To: derby-user@db.apache.org
> Subject: Re: Embedded database, authentication, and derby.system.home
>
> Hi Michael,
>
> I must be missing something in your description of the problem, because I cannot reproduce it. I have created the following scripts in order to reproduce the behavior of both changing the value of derby.system.home and moving the database to another directory:
>
> First SQL script nativeTest.sql:
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
>
>
>
> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
>
>
>
> -- shutdown in order to enable NATIVE authentication
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
>
>
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
>
>
>
> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
>
>
>
> CREATE TABLE t(a INT);
>
> INSERT INTO t VALUES (1), (2);
>
>
> Second SQL script nativeTest2.sql:
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
>
>
>
> SELECT * FROM t;
>
>
> Then a shell script (z1) to run nativeTest.sql, move the database to another directory, then run nativeTest2.sql:
>
> #! /bin/bash
>
> #
>
> # Run a derby test
>
>
>
> rm -rf z/db1
>
> rm -rf zz/db1
>
> rm zzz/*
>
> rm zzzz/*
>
>
>
> . setupClasspath
>
>
>
> java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest.sql
>
>
>
> mv z/db1 zz/
>
>
>
> java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest2.sql
>
>
> When I run z1, I get the following output:
>
> ij version 10.16
>
> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
>
> ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
>
> 0 rows inserted/updated/deleted
>
> ij> -- shutdown in order to enable NATIVE authentication
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
>
> ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.
>
> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
>
> ij(CONNECTION1)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
>
> 1
>
> --------------------------------------------------------------------------------------------------------------------------------
>
> NATIVE::LOCAL
>
>
>
> 1 row selected
>
> ij(CONNECTION1)> CREATE TABLE t(a INT);
>
> 0 rows inserted/updated/deleted
>
> ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);
>
> 2 rows inserted/updated/deleted
>
> ij(CONNECTION1)> ij version 10.16
>
> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
>
> ij> SELECT * FROM t;
>
> A
>
> -----------
>
> 1
>
> 2
>
>
>
> 2 rows selected
>
>
> Can you post a similar set of scripts which demonstrate the problem you are seeing?
>
> Thanks,
> -Rick
>
> On 3/19/21 11:14 AM, Michael Remijan wrote:
>
> NATIVE.
>
>
>
> After creating the database I, I set the 'sa' user like this:
>
>
>
> ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxxxxxxxxxxxxxx');
>
>
>
> Then I create the application user like this:
>
>
>
> CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxxxxxxxxxxxxxx');
>
>
>
> And I have the following in derby.properties
>
>
>
> # Passwords don't expire for 20 years
>
> derby.authentication.native.passwordLifetimeMillis=631138520000
>
> # Use the best hash algorithm you can
>
> derby.authentication.builtin.algorithm=SHA-512
>
> # Use a larger salt length for better security
>
> derby.authentication.builtin.saltLength=128
>
> # Re-hash this number of times for better security
>
> derby.authentication.builtin.iterations=1564
>
>
>
> BTW, I've updated code to dynamically System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of user configuration. That been successful. So if I move the database to somewhere other than my application's default location, I set derby.system.home at runtime based on that configuration.
>
>
>
> I also found that this is only a factor with embedded databases where the path to the database is a fully qualified file system path. If the configuration uses a remote database, the value for derby.system.home doesn't seem to matter.
>
>
>
> -----Original Message-----
>
> From: Rick Hillegas <ri...@gmail.com>
>
> Sent: Friday, March 19, 2021 11:22 AM
>
> To: derby-user@db.apache.org<ma...@db.apache.org>
>
> Subject: Re: Embedded database, authentication, and derby.system.home
>
>
>
> Hi Mike,
>
>
>
> What kind of authentication are you using: LDAP, NATIVE, or custom?
>
>
>
> On 3/19/21 7:05 AM, Michael Remijan wrote:
>
> Greetings,
>
>
>
> I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
>
>
>
> I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
>
>
>
> /projectname
>
> /bin
>
> start.sh // starts the application
>
> /data
>
> /projectdb // this is the derby database.
>
>
>
> My projectdb database also has a specific username, password, and permissions/roles on that user.
>
>
>
> When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
>
>
>
> Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
>
>
>
> Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
>
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
>
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
>
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
>
> at java.security.AccessController.doPrivileged(Native Method)
>
> at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
>
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
>
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
>
> at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
>
> at java.sql.DriverManager.getConnection(DriverManager.java:664)
>
> at java.sql.DriverManager.getConnection(DriverManager.java:247)
>
> at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
>
> ... 67 more
>
> Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
>
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
>
> ... 84 more
>
>
>
> Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
>
>
>
> Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
>
>
>
> Thanks!
>
> Mike
>
>
>
>
>
>
Re: Embedded database, authentication, and derby.system.home
Posted by Rick Hillegas <ri...@gmail.com>.
Sorry. I need SQL scripts. I can't make sense of a compiled application
with no source code.
On 3/22/21 7:05 AM, Michael Remijan wrote:
> Hi Rick,
>
> Here is a working Java example for you. It’s fully self-contained with its own JRE so all you need do is download it and run it. This bin distribution is for Windows, so sorry if you’re on another platform. It’s too big for email, so I have it on my OneDrive https://1drv.ms/u/s!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ?e=npfMBd
>
> First, download, unzip, then run /bin/start-resiste.bat. You’ll see the following:
> [cid:image001.png@01D71EF8.C4AFB090]
>
>
> Next, m move the /data directory to a different location and then update /conf/db.properties.
> [cid:image002.png@01D71EF9.233513D0]
>
>
> Next, run /bin/start-resiste.bat again. You’ll see the following error:
> [cid:image003.png@01D71EF9.233513D0]
>
>
> Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync with the new location of the database:
> [cid:image004.png@01D71EF9.3A7B5450]
>
> Next, run /bin/start-resiste.bat again. You’ll see it’s now successful with /conf/db.properties and /bin/start-resiste.bat in sync
> [cid:image001.png@01D71EF8.C4AFB090]
>
>
> Now here is a big Caveat. I discovered this while getting this demo together. I have Derby 10.14.2.0 installed on my computer. For my first attempt at this demo, I created the database from scratch using 10.14.2.0. When I did this, I DID NOT experience the connection error as described above. However, when I use my original database, I DO experience the error as demonstrated above. Unfortunately, I do not remember which version of Derby I used to create the original database, but obviously it was an older version. So perhaps this has something to do with an older database version?
>
> Mike
>
>
>
>
>
> From: Rick Hillegas <ri...@gmail.com>
> Sent: Friday, March 19, 2021 4:04 PM
> To: derby-user@db.apache.org
> Subject: Re: Embedded database, authentication, and derby.system.home
>
> Hi Michael,
>
> I must be missing something in your description of the problem, because I cannot reproduce it. I have created the following scripts in order to reproduce the behavior of both changing the value of derby.system.home and moving the database to another directory:
>
> First SQL script nativeTest.sql:
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
>
>
>
> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
>
>
>
> -- shutdown in order to enable NATIVE authentication
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
>
>
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
>
>
>
> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
>
>
>
> CREATE TABLE t(a INT);
>
> INSERT INTO t VALUES (1), (2);
>
>
> Second SQL script nativeTest2.sql:
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
>
>
>
> SELECT * FROM t;
>
>
> Then a shell script (z1) to run nativeTest.sql, move the database to another directory, then run nativeTest2.sql:
>
> #! /bin/bash
>
> #
>
> # Run a derby test
>
>
>
> rm -rf z/db1
>
> rm -rf zz/db1
>
> rm zzz/*
>
> rm zzzz/*
>
>
>
> . setupClasspath
>
>
>
> java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest.sql
>
>
>
> mv z/db1 zz/
>
>
>
> java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest2.sql
>
>
> When I run z1, I get the following output:
>
> ij version 10.16
>
> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
>
> ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
>
> 0 rows inserted/updated/deleted
>
> ij> -- shutdown in order to enable NATIVE authentication
>
> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
>
> ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.
>
> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
>
> ij(CONNECTION1)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
>
> 1
>
> --------------------------------------------------------------------------------------------------------------------------------
>
> NATIVE::LOCAL
>
>
>
> 1 row selected
>
> ij(CONNECTION1)> CREATE TABLE t(a INT);
>
> 0 rows inserted/updated/deleted
>
> ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);
>
> 2 rows inserted/updated/deleted
>
> ij(CONNECTION1)> ij version 10.16
>
> ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
>
> ij> SELECT * FROM t;
>
> A
>
> -----------
>
> 1
>
> 2
>
>
>
> 2 rows selected
>
>
> Can you post a similar set of scripts which demonstrate the problem you are seeing?
>
> Thanks,
> -Rick
>
> On 3/19/21 11:14 AM, Michael Remijan wrote:
>
> NATIVE.
>
>
>
> After creating the database I, I set the 'sa' user like this:
>
>
>
> ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxxxxxxxxxxxxxx');
>
>
>
> Then I create the application user like this:
>
>
>
> CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxxxxxxxxxxxxxx');
>
>
>
> And I have the following in derby.properties
>
>
>
> # Passwords don't expire for 20 years
>
> derby.authentication.native.passwordLifetimeMillis=631138520000
>
> # Use the best hash algorithm you can
>
> derby.authentication.builtin.algorithm=SHA-512
>
> # Use a larger salt length for better security
>
> derby.authentication.builtin.saltLength=128
>
> # Re-hash this number of times for better security
>
> derby.authentication.builtin.iterations=1564
>
>
>
> BTW, I've updated code to dynamically System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of user configuration. That been successful. So if I move the database to somewhere other than my application's default location, I set derby.system.home at runtime based on that configuration.
>
>
>
> I also found that this is only a factor with embedded databases where the path to the database is a fully qualified file system path. If the configuration uses a remote database, the value for derby.system.home doesn't seem to matter.
>
>
>
> -----Original Message-----
>
> From: Rick Hillegas <ri...@gmail.com>
>
> Sent: Friday, March 19, 2021 11:22 AM
>
> To: derby-user@db.apache.org<ma...@db.apache.org>
>
> Subject: Re: Embedded database, authentication, and derby.system.home
>
>
>
> Hi Mike,
>
>
>
> What kind of authentication are you using: LDAP, NATIVE, or custom?
>
>
>
> On 3/19/21 7:05 AM, Michael Remijan wrote:
>
> Greetings,
>
>
>
> I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
>
>
>
> I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
>
>
>
> /projectname
>
> /bin
>
> start.sh // starts the application
>
> /data
>
> /projectdb // this is the derby database.
>
>
>
> My projectdb database also has a specific username, password, and permissions/roles on that user.
>
>
>
> When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
>
>
>
> Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
>
>
>
> Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
>
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
>
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
>
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
>
> at java.security.AccessController.doPrivileged(Native Method)
>
> at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
>
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
>
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
>
> at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
>
> at java.sql.DriverManager.getConnection(DriverManager.java:664)
>
> at java.sql.DriverManager.getConnection(DriverManager.java:247)
>
> at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
>
> ... 67 more
>
> Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
>
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
>
> ... 84 more
>
>
>
> Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
>
>
>
> Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
>
>
>
> Thanks!
>
> Mike
>
>
>
>
>
>
RE: Embedded database, authentication, and derby.system.home
Posted by Michael Remijan <mj...@live.com>.
Hi Rick,
Here is a working Java example for you. It’s fully self-contained with its own JRE so all you need do is download it and run it. This bin distribution is for Windows, so sorry if you’re on another platform. It’s too big for email, so I have it on my OneDrive https://1drv.ms/u/s!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ?e=npfMBd
First, download, unzip, then run /bin/start-resiste.bat. You’ll see the following:
[cid:image001.png@01D71EF8.C4AFB090]
Next, m move the /data directory to a different location and then update /conf/db.properties.
[cid:image002.png@01D71EF9.233513D0]
Next, run /bin/start-resiste.bat again. You’ll see the following error:
[cid:image003.png@01D71EF9.233513D0]
Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync with the new location of the database:
[cid:image004.png@01D71EF9.3A7B5450]
Next, run /bin/start-resiste.bat again. You’ll see it’s now successful with /conf/db.properties and /bin/start-resiste.bat in sync
[cid:image001.png@01D71EF8.C4AFB090]
Now here is a big Caveat. I discovered this while getting this demo together. I have Derby 10.14.2.0 installed on my computer. For my first attempt at this demo, I created the database from scratch using 10.14.2.0. When I did this, I DID NOT experience the connection error as described above. However, when I use my original database, I DO experience the error as demonstrated above. Unfortunately, I do not remember which version of Derby I used to create the original database, but obviously it was an older version. So perhaps this has something to do with an older database version?
Mike
From: Rick Hillegas <ri...@gmail.com>
Sent: Friday, March 19, 2021 4:04 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home
Hi Michael,
I must be missing something in your description of the problem, because I cannot reproduce it. I have created the following scripts in order to reproduce the behavior of both changing the value of derby.system.home and moving the database to another directory:
First SQL script nativeTest.sql:
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
-- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
CREATE TABLE t(a INT);
INSERT INTO t VALUES (1), (2);
Second SQL script nativeTest2.sql:
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
SELECT * FROM t;
Then a shell script (z1) to run nativeTest.sql, move the database to another directory, then run nativeTest2.sql:
#! /bin/bash
#
# Run a derby test
rm -rf z/db1
rm -rf zz/db1
rm zzz/*
rm zzzz/*
. setupClasspath
java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest.sql
mv z/db1 zz/
java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest2.sql
When I run z1, I get the following output:
ij version 10.16
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
0 rows inserted/updated/deleted
ij> -- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
ij(CONNECTION1)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
1
--------------------------------------------------------------------------------------------------------------------------------
NATIVE::LOCAL
1 row selected
ij(CONNECTION1)> CREATE TABLE t(a INT);
0 rows inserted/updated/deleted
ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);
2 rows inserted/updated/deleted
ij(CONNECTION1)> ij version 10.16
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
ij> SELECT * FROM t;
A
-----------
1
2
2 rows selected
Can you post a similar set of scripts which demonstrate the problem you are seeing?
Thanks,
-Rick
On 3/19/21 11:14 AM, Michael Remijan wrote:
NATIVE.
After creating the database I, I set the 'sa' user like this:
ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxxxxxxxxxxxxxx');
Then I create the application user like this:
CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxxxxxxxxxxxxxx');
And I have the following in derby.properties
# Passwords don't expire for 20 years
derby.authentication.native.passwordLifetimeMillis=631138520000
# Use the best hash algorithm you can
derby.authentication.builtin.algorithm=SHA-512
# Use a larger salt length for better security
derby.authentication.builtin.saltLength=128
# Re-hash this number of times for better security
derby.authentication.builtin.iterations=1564
BTW, I've updated code to dynamically System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of user configuration. That been successful. So if I move the database to somewhere other than my application's default location, I set derby.system.home at runtime based on that configuration.
I also found that this is only a factor with embedded databases where the path to the database is a fully qualified file system path. If the configuration uses a remote database, the value for derby.system.home doesn't seem to matter.
-----Original Message-----
From: Rick Hillegas <ri...@gmail.com>
Sent: Friday, March 19, 2021 11:22 AM
To: derby-user@db.apache.org<ma...@db.apache.org>
Subject: Re: Embedded database, authentication, and derby.system.home
Hi Mike,
What kind of authentication are you using: LDAP, NATIVE, or custom?
On 3/19/21 7:05 AM, Michael Remijan wrote:
Greetings,
I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
/projectname
/bin
start.sh // starts the application
/data
/projectdb // this is the derby database.
My projectdb database also has a specific username, password, and permissions/roles on that user.
When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
... 67 more
Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 84 more
Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
Thanks!
Mike
RE: Embedded database, authentication, and derby.system.home
Posted by Michael Remijan <mj...@live.com>.
The issue I have is connecting with a Java client. I will try and put together a simple example this week.
From: Rick Hillegas <ri...@gmail.com>
Sent: Friday, March 19, 2021 4:04 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home
Hi Michael,
I must be missing something in your description of the problem, because I cannot reproduce it. I have created the following scripts in order to reproduce the behavior of both changing the value of derby.system.home and moving the database to another directory:
First SQL script nativeTest.sql:
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
-- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
CREATE TABLE t(a INT);
INSERT INTO t VALUES (1), (2);
Second SQL script nativeTest2.sql:
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
SELECT * FROM t;
Then a shell script (z1) to run nativeTest.sql, move the database to another directory, then run nativeTest2.sql:
#! /bin/bash
#
# Run a derby test
rm -rf z/db1
rm -rf zz/db1
rm zzz/*
rm zzzz/*
. setupClasspath
java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest.sql
mv z/db1 zz/
java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest2.sql
When I run z1, I get the following output:
ij version 10.16
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
0 rows inserted/updated/deleted
ij> -- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
ij(CONNECTION1)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
1
--------------------------------------------------------------------------------------------------------------------------------
NATIVE::LOCAL
1 row selected
ij(CONNECTION1)> CREATE TABLE t(a INT);
0 rows inserted/updated/deleted
ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);
2 rows inserted/updated/deleted
ij(CONNECTION1)> ij version 10.16
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
ij> SELECT * FROM t;
A
-----------
1
2
2 rows selected
Can you post a similar set of scripts which demonstrate the problem you are seeing?
Thanks,
-Rick
On 3/19/21 11:14 AM, Michael Remijan wrote:
NATIVE.
After creating the database I, I set the 'sa' user like this:
ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxxxxxxxxxxxxxx');
Then I create the application user like this:
CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxxxxxxxxxxxxxx');
And I have the following in derby.properties
# Passwords don't expire for 20 years
derby.authentication.native.passwordLifetimeMillis=631138520000
# Use the best hash algorithm you can
derby.authentication.builtin.algorithm=SHA-512
# Use a larger salt length for better security
derby.authentication.builtin.saltLength=128
# Re-hash this number of times for better security
derby.authentication.builtin.iterations=1564
BTW, I've updated code to dynamically System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of user configuration. That been successful. So if I move the database to somewhere other than my application's default location, I set derby.system.home at runtime based on that configuration.
I also found that this is only a factor with embedded databases where the path to the database is a fully qualified file system path. If the configuration uses a remote database, the value for derby.system.home doesn't seem to matter.
-----Original Message-----
From: Rick Hillegas <ri...@gmail.com>
Sent: Friday, March 19, 2021 11:22 AM
To: derby-user@db.apache.org<ma...@db.apache.org>
Subject: Re: Embedded database, authentication, and derby.system.home
Hi Mike,
What kind of authentication are you using: LDAP, NATIVE, or custom?
On 3/19/21 7:05 AM, Michael Remijan wrote:
Greetings,
I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
/projectname
/bin
start.sh // starts the application
/data
/projectdb // this is the derby database.
My projectdb database also has a specific username, password, and permissions/roles on that user.
When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
... 67 more
Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 84 more
Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
Thanks!
Mike
Re: Embedded database, authentication, and derby.system.home
Posted by Rick Hillegas <ri...@gmail.com>.
Hi Michael,
I must be missing something in your description of the problem, because
I cannot reproduce it. I have created the following scripts in order to
reproduce the behavior of both changing the value of derby.system.home
and moving the database to another directory:
First SQL script nativeTest.sql:
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
-- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
CREATE TABLE t(a INT);
INSERT INTO t VALUES (1), (2);
Second SQL script nativeTest2.sql:
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
SELECT * FROM t;
Then a shell script (z1) to run nativeTest.sql, move the database to
another directory, then run nativeTest2.sql:
#! /bin/bash
#
# Run a derby test
rm -rf z/db1
rm -rf zz/db1
rm zzz/*
rm zzzz/*
. setupClasspath
java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest.sql
mv z/db1 zz/
java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzzz -Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij $MAC_HOME/sql/nativeTest2.sql
When I run z1, I get the following output:
ij version 10.16
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
0 rows inserted/updated/deleted
ij> -- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
ij(CONNECTION1)> VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');
1
--------------------------------------------------------------------------------------------------------------------------------
NATIVE::LOCAL
1 row selected
ij(CONNECTION1)> CREATE TABLE t(a INT);
0 rows inserted/updated/deleted
ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);
2 rows inserted/updated/deleted
ij(CONNECTION1)> ij version 10.16
ij> CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';
ij> SELECT * FROM t;
A
-----------
1
2
2 rows selected
Can you post a similar set of scripts which demonstrate the problem you
are seeing?
Thanks,
-Rick
On 3/19/21 11:14 AM, Michael Remijan wrote:
> NATIVE.
>
> After creating the database I, I set the 'sa' user like this:
>
> ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxxxxxxxxxxxxxx');
>
> Then I create the application user like this:
>
> CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxxxxxxxxxxxxxx');
>
> And I have the following in derby.properties
>
> # Passwords don't expire for 20 years
> derby.authentication.native.passwordLifetimeMillis=631138520000
> # Use the best hash algorithm you can
> derby.authentication.builtin.algorithm=SHA-512
> # Use a larger salt length for better security
> derby.authentication.builtin.saltLength=128
> # Re-hash this number of times for better security
> derby.authentication.builtin.iterations=1564
>
> BTW, I've updated code to dynamically System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of user configuration. That been successful. So if I move the database to somewhere other than my application's default location, I set derby.system.home at runtime based on that configuration.
>
> I also found that this is only a factor with embedded databases where the path to the database is a fully qualified file system path. If the configuration uses a remote database, the value for derby.system.home doesn't seem to matter.
>
> -----Original Message-----
> From: Rick Hillegas <ri...@gmail.com>
> Sent: Friday, March 19, 2021 11:22 AM
> To: derby-user@db.apache.org
> Subject: Re: Embedded database, authentication, and derby.system.home
>
> Hi Mike,
>
> What kind of authentication are you using: LDAP, NATIVE, or custom?
>
> On 3/19/21 7:05 AM, Michael Remijan wrote:
>> Greetings,
>>
>> I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
>>
>> I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
>>
>> /projectname
>> /bin
>> start.sh // starts the application
>> /data
>> /projectdb // this is the derby database.
>>
>> My projectdb database also has a specific username, password, and permissions/roles on that user.
>>
>> When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
>>
>> Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
>>
>> Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
>> at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
>> at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
>> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
>> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
>> at java.security.AccessController.doPrivileged(Native Method)
>> at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
>> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
>> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
>> at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
>> at java.sql.DriverManager.getConnection(DriverManager.java:664)
>> at java.sql.DriverManager.getConnection(DriverManager.java:247)
>> at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
>> ... 67 more
>> Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
>> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>> at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
>> ... 84 more
>>
>> Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
>>
>> Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
>>
>> Thanks!
>> Mike
>>
RE: Embedded database, authentication, and derby.system.home
Posted by Michael Remijan <mj...@live.com>.
NATIVE.
After creating the database I, I set the 'sa' user like this:
ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxxxxxxxxxxxxxx');
Then I create the application user like this:
CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxxxxxxxxxxxxxx');
And I have the following in derby.properties
# Passwords don't expire for 20 years
derby.authentication.native.passwordLifetimeMillis=631138520000
# Use the best hash algorithm you can
derby.authentication.builtin.algorithm=SHA-512
# Use a larger salt length for better security
derby.authentication.builtin.saltLength=128
# Re-hash this number of times for better security
derby.authentication.builtin.iterations=1564
BTW, I've updated code to dynamically System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of user configuration. That been successful. So if I move the database to somewhere other than my application's default location, I set derby.system.home at runtime based on that configuration.
I also found that this is only a factor with embedded databases where the path to the database is a fully qualified file system path. If the configuration uses a remote database, the value for derby.system.home doesn't seem to matter.
-----Original Message-----
From: Rick Hillegas <ri...@gmail.com>
Sent: Friday, March 19, 2021 11:22 AM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home
Hi Mike,
What kind of authentication are you using: LDAP, NATIVE, or custom?
On 3/19/21 7:05 AM, Michael Remijan wrote:
> Greetings,
>
> I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
>
> I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
>
> /projectname
> /bin
> start.sh // starts the application
> /data
> /projectdb // this is the derby database.
>
> My projectdb database also has a specific username, password, and permissions/roles on that user.
>
> When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
>
> Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
>
> Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
> at java.security.AccessController.doPrivileged(Native Method)
> at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
> at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
> at java.sql.DriverManager.getConnection(DriverManager.java:664)
> at java.sql.DriverManager.getConnection(DriverManager.java:247)
> at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
> ... 67 more
> Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 84 more
>
> Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
>
> Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
>
> Thanks!
> Mike
>
Re: Embedded database, authentication, and derby.system.home
Posted by Rick Hillegas <ri...@gmail.com>.
Hi Mike,
What kind of authentication are you using: LDAP, NATIVE, or custom?
On 3/19/21 7:05 AM, Michael Remijan wrote:
> Greetings,
>
> I have an interesting issue I just ran into and it took a little while to debug and figure out exactly what is happening.
>
> I have a project that uses an embedded Derby database. My project folder organization is a typical for an open source project:
>
> /projectname
> /bin
> start.sh // starts the application
> /data
> /projectdb // this is the derby database.
>
> My projectdb database also has a specific username, password, and permissions/roles on that user.
>
> When I start the application, the "start.sh" script sets the -Dderby.system.home to the "/data" directory and my JDBC connection URL figures out the fully qualified location of the "/projectdb" directory so Derby can easily access it. This all works perfectly fine.
>
> Then I wanted to run a test where I moved the /projectdb directory to a different location. I though I would be able to do this with no problems. But I was wrong :( When I do this I get the following authentication exception
>
> Caused by: java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred. Reason: Invalid authentication..
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
> at java.security.AccessController.doPrivileged(Native Method)
> at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
> at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
> at java.sql.DriverManager.getConnection(DriverManager.java:664)
> at java.sql.DriverManager.getConnection(DriverManager.java:247)
> at org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
> ... 67 more
> Caused by: ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 84 more
>
> Which I thought was kind of odd. What I eventually discovered is the -Dderby.system.home value and the location of the database must be in sync. If they are not, I get the authentication exception. If do not set the -Dderby.system.home value, I get this authentication exception.
>
> Originally, I remember setting the -Dderby.system.home value because I wanted the derby.log files created in that directory. I didn't expect this value would also be critical for connecting to the database with a username/password. I haven't tried it unauthenticated, but it seems to me I should be able to move the location of the database around and as long as I'm connecting to the database, authentication should work OK regardless of -Dderby.system.home and the location of the database being in sync. This make sense?
>
> Thanks!
> Mike
>