JIRA 7.12.3 SQL Server 2012 - Cannot add user, all the user directories are read-only

[Dante Labate]

Can anybody help me?

I am trying to install JIRA Core 7.12.3 on SQL Server 2012 and am encountering this error.

"Cannot add user, all the user directories are read-only"

[Andy Heinzer]

I have seen this error get thrown in the setup wizard for Jira before.  It can be rather misleading, in a sense I hope to explain here.  In my experience, this tends to happen when you are trying to create the very first user in a Jira install, the admin user.  But what happens here is that the SQL database might not have been setup with all the rights/permissions needed in order to be able to properly create that account. 

If this happens, we should see very clear SQL exception errors in the $JIRAHOME/log/atlassian-jira.log file when this occurs.

It is possible there is some other cause, especially for upgrades/migrations of Jira Server from existing instaces.  But if this is a first time install, I'm betting it's a SQL problem.  Either way, I would still recommend going into the logs for Jira so that we can better understand for sure what the problem really is here.

[Dante Labate]

@Andy Heinzer 

Thanks for the answer

As I said, I am using SQL Server 2012 and from what I saw in the database configuration documentation you only need to do 4 activities.

CREATE DATABASE JIRA_OIAD
ALTER DATABASE JIRA_OIAD collate SQL_Latin1_General_CP437_CI_AI
ALTER DATABASE JIRA_OIAD SET READ_COMMITTED_SNAPSHOT ON

to give db_onwer access to the JIRA user

(as the database was created by user JIRA, it is already the db_owner)

I found in the log several SQL errors as you foreseen ..

 Error was: com.microsoft.sqlserver.jdbc.SQLServerException: Column names in each table must be unique. Column name 'actiontype' in table 'dbo.jiraaction' is specified more than once.
2018-10-24 11:42:41,499 http-nio-8080-exec-18 WARN anonymous 762x149x1 q8lw0q 10.125.193.25 /secure/SetupDatabase.jspa [o.o.c.entity.jdbc.DatabaseUtil] Field "body" of entity "Action" is missing its corresponding column "actionbody"
2018-10-24 11:42:41,501 http-nio-8080-exec-18 ERROR anonymous 762x149x1 q8lw0q 10.125.193.25 /secure/SetupDatabase.jspa [o.o.c.entity.jdbc.DatabaseUtil] Could not add column "actionbody" to table "dbo.jiraaction"
2018-10-24 11:42:41,501 http-nio-8080-exec-18 ERROR anonymous 762x149x1 q8lw0q 10.125.193.25 /secure/SetupDatabase.jspa [o.o.c.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:

can help me understand what is wrong (or missing) in the database configuration?

[Andy Heinzer]

From the error messages in the logs, it looks like Jira wasn't able to create the tables correctly.   Since this appears to be a new installation, it might just be easier to leave the existing database as is and try to create a new empty one from scratch and connect Jira to this empty database.

• Make sure the collation type is case-insensitive.

  Collapse

  We support SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AI as case-insensitive, accent-insensitive, and language neutral collation types. If your SQL Server installation's collation type settings have not been changed from their defaults, check the collation type settings.

• SQL Server uses Unicode encoding to store characters. This is sufficient to prevent any possible encoding problems.

I suspect your collation is ok here from what you have told us so far.  But I'd want to check that the SQL server itself is using unicode encoding to store characters here.

There are a few more steps than the 4 you outlined here.  While I have seen some environments using the dbo schema for a database, our own documentation on Connecting Jira to SQL 2012 suggest that you create both a new SQL user and a new SQL schema for this database to use.  There is also instructions on setting 'no count' from SQL itself.

I would try to follow the document as closely as you can when creating the database.  If you're still seeing problems with getting Jira setup with this new database.  I would be interested to see the logs you have from this instance, as well as perhaps to look at your $JIRAHOME/dbconfig.xml file.   I'm not interested in seeing your username/password, but I am interested to see the syntax of the URL, as well as the database driver package referenced

[Dante Labate]

the "no count" setting was also performed.

dbconfig.xml

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
 <name>defaultDS</name>
 <delegator-name>default</delegator-name>
 <database-type>mssql</database-type>
 <schema-name>dbo</schema-name>
 <jdbc-datasource>
 <url>jdbc:sqlserver://;serverName=172.16.2.150;portNumber=1433;databaseName=jira_oiad</url>
 <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
 <username>username</username>
 <password>password</password>
 <pool-min-size>20</pool-min-size>
 <pool-max-size>20</pool-max-size>
 <pool-max-wait>30000</pool-max-wait>
 <validation-query>select 1</validation-query>
 <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
 <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
 <pool-max-idle>20</pool-max-idle>
 <pool-remove-abandoned>true</pool-remove-abandoned>
 <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
 <pool-test-on-borrow>false</pool-test-on-borrow>
 <pool-test-while-idle>true</pool-test-while-idle>
 </jdbc-datasource>
</jira-database-config>

I'm trying to figure out how to attach file here.

[Andy Heinzer]

I would try to create a new database name both for the sake of starting over and not trying to continue to use this other database.  In addition to that, also create a unique schema for this database (as indicated in the documentation).  Then try to use these when adjusting the dbconfig.xml.  In addition to those changes, I would recommend changing your <url> tag value from:

<url>jdbc:sqlserver://;serverName=172.16.2.150;portNumber=1433;databaseName=jira_oiad</url>

into

<url>jdbc:sqlserver://172.16.2.150:1433;databaseName=jiradbname</url>

It might not make all the difference here, but this is the way we have it in the documentation, and I trust the documentation here.   You are correct that the sql user does not need to be the user that created the database, but they do need the db_owner role for this database.   It also goes into some limited detail about the permissions needed for that user in the new schema created:  ie connect, create tables, etc.