Connecting Jira applications to SQL Server 2022

These instructions will help you connect Jira to the Microsoft SQL Server 2022 database.

Before you begin

1. Create and configure the SQL Server database

When creating the database, remember your database name, user name, schema name, and port number. You'll need them later to connect Jira to your database.

  1. Create a database for Jira (for example, jiradb). 

    • Make sure the collation type is case-insensitive.

      Supported collation types...

      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.

  2. Create a database user which Jira will connect as (for example, jiradbuser). This user shouldn't be the database owner, but should be in the db_owner role.

  3. Create an empty schema in the database for the Jira tables (for example, jiraschema).

    What is a schema?

    A schema in SQL Server 2022 is a distinct namespace used to contain objects and is different from a traditional database schema. You are not required to create any of Jira's tables, fields, or relationships. Jira will create these objects in your empty schema when it starts for the first time. You can read more on SQL Server 2022 schemas in the relevant Microsoft documentation.

  4. Make sure that the database user has permission to connect to the database, and to create and populate tables in the newly-created schema.

  5. Make sure that TCP/IP is enabled on SQL Server and is listening on the correct port. A default SQL Server installation uses port number 1433.
  6. Make sure that SQL Server is operating in the appropriate authentication mode.

    What is an authentication mode?

    By default, SQL Server operates in the Windows authentication mode. But if your user isn’t associated with a trusted SQL connection and Microsoft SQL Server, Error: 18452 is received during the Jira startup, you should change the authentication mode to the Mixed authentication mode. Check the Microsoft documentation on authentication modes

  7. Turn off the SET NOCOUNT option.
    1. Open SQL Server Management Studio.
    2. Go to Tools > Options > Query Execution > SQL Server > Advanced and clear the SET NOCOUNT check box.
      SET NOCOUNT option in SQL Server Management Studio.

    3. Right-click your server in the Object Explorer and go to Properties > Connections > Default Connections. Clear the no count option.

  8. Access the Query Console by right-clicking on the created database and selecting New Query. Run the following command to set the isolation level:


2. Configure Jira to connect to the database

There are two ways to configure the Jira server to connect to the SQL Server database.

Database connection fields

The table shows the fields you'll need to fill out when connecting Jira to your database. You can also refer to them and to the sample dbconfig.xml file under the table if you'd like to create or edit the dbconfig.xml file manually.

Setup wizard / Configuration tooldbconfig.xmlDescription

Located in the <url> tag. In the following example of the URL, dbserver is the hostname: <url>jdbc:sqlserver://dbserver :1433;databaseName=jiradb</url>

The name or IP address of the machine that the SQL Server server is installed on

Located in the <url> tag. In the following example of the URL, 1433 is the port:
<url>jdbc:sqlserver://dbserver :1433;databaseName=jiradb</url>

The TCP/IP port that the SQL Server database server is listening to. You can leave this parameter blank to use the default port.

Located in the <url> tag. In the following example of the URL, jiradb is the database:
<url>jdbc:sqlserver://dbserver :1433;databaseName=jiradb</url>

The name of your SQL Server database to which Jira will save data. You should have created it in Step 1.

Located in the <username> tag:
<username> jiradbuser </username>

The user that Jira uses to connect to the SQL Server database server. You should have created it in Step 1.
PasswordLocated in the <password> tag:
<password> jiradbuser </password>
The user's password — used to authenticate with the SQL Server server.
SchemaLocated in the <schema-name> tag:
<schema-name> dbo </schema-name>

The name of the schema that your SQL Server database uses. You should have created it in Step 1.

Sample dbconfig.xml file

For more information about the child elements of <jdbc-datasource/> beginning with pool in the dbconfig.xml file, see Tuning database connections.

  <validation-query>select 1</validation-query>


Both the Jira setup wizard and database configuration tool also add the element <validation-query>select 1</validation-query> to the dbconfig.xml file, which is usually required when running Jira with default MySQL installations. See Surviving connection closures for details.

3. Start Jira

You should now have Jira configured to connect to your SQL Server database. The next step is to start it up!

Known issues

The following table lists known issues that might occur during the database operation or the execution of database procedures. We are aware of these issues and have planned their resolution in future releases.

SQL Server doesn't allow more than 2000 parameters in a query.

This is a known limitation set by SQL Server. According to SQL Docs, a procedure can have a maximum of 2100 parameters.

The issue is tracked in the ticket JRASERVER-63290 - Getting issue details... STATUS

Feel free to leave comments on the ticket so we know your use cases better and understand how this issue is impacting your operations.

Last modified on Jul 6, 2023

Was this helpful?

Provide feedback about this article
Powered by Confluence and Scroll Viewport.