Documentation for JIRA 6.3 EAP developer (EAP) releases only. Not using this? See below:
(JIRA 6.2.x documentation | JIRA OnDemand documentation | earlier versions of JIRA)

Skip to end of metadata
Go to start of metadata

These instructions will help you connect JIRA to a Microsoft SQL Server 2005 database.

Before you begin

  • Check whether your version of SQL Server is supported. See Supported Platforms.
    Note, SQL Server Express is not supported, however, it is possible to set up JIRA to work with this database. As it's not supported, we have no docs available for this configuration.
  • If you are Migrating JIRA to Another Server, create an export of your data as an XML backup. You will then be able to transfer data from your old database to your new database, as described in Switching databases.
  • Shut down JIRA before you begin, unless you are running the Setup Wizard.

On this page:

1. Create and Configure the SQL Server Database

  1. Create a database for JIRA to store issues in (e.g. jiradb).
    Remember your database name, as it will be used to configure JIRA's connection to this database in subsequent steps.
    • Collation type must be case-insensitive, for example, 'SQL_Latin1_General_CP437_CI_AI' is case-insensitive collation type. 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 (e.g. jiradbuser).
    Remember your database user name, as it will be used to configure JIRA's connection to this database in subsequent steps.
    • This database user should not be the database owner, but should be in the db_owner role (see SQL Server Startup Errors for details).
  3. Create an empty 'schema' in the database (e.g. jiraschema) for the JIRA tables.
    Remember this database schema name, as it will be used to configure JIRA's connection to this database in subsequent steps.
    • A 'schema' in SQL Server 2005 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 2005 schemas in the relevant Microsoft documentation.
  4. Ensure that the database user has permission to connect to the database, and create and populate tables in the newly-created schema.
  5. Ensure that TCP/IP is enabled on SQL Server and listening on the correct port (which is 1433 for a default SQL Server installation).
    Remember this port number, as it will be used to configure JIRA's connection to this database in subsequent steps.
    • Read the Microsoft documentation for information on how to enable a network protocol (TCP/IP) and how to configure SQL server to listen on a specific port.
  6. Ensure that SQL Server is operating in the appropriate authentication mode.
    • By default, SQL Server operates in 'Windows Authentication Mode'. However, if your user is not associated with a trusted SQL connection, i.e. 'Microsoft SQL Server, Error: 18452' is received during JIRA startup, you will need to change the authentication mode to 'Mixed Authentication Mode'. Read the Microsoft documentation on authentication modes and changing the authentication mode to 'Mixed Authentication Mode'
  7. Turn off the SET NOCOUNT option (see the Error caused by SET NOCOUNT in MS SQL Serverfor an explanation). To turn off SET NOCOUNT:
    • Open SQL Server Management Studio and navigate to Tools > Options > Query Execution > SQL Server > Advanced. The following screenshot displays the configuration panel for this setting in MSSQL Server 2005. Ensure that the SET NOCOUNT option is not selected:
    • Access the Query Console by right clicking on the newly created database and selecting 'New Query'. Execute the following command to set the isolation level.

2. Copy the SQL Server JDBC Driver to Your Application Server (JIRA WAR Only)

(warning) Skip this step if you installed a 'Recommended' distribution of JIRA, which includes the SQL Server JDBC driver. The JIRA WAR distribution does not include this driver.

  1. Download the SQL Server JDBC driver (v1.2.4) from JTDS.
    (info) Microsoft have their own JDBC driver but we strongly recommend avoiding it after receiving many reports of intermittent disconnections (JRA-5760 and JRA-6872), workflow problems (JRA-8443) and Chinese character problems (JRA-5054).
  2. Add the SQL Server JDBC driver jar (jtds-1.2.4.jar) to the <Tomcat install>/lib/ directory.

3. Configure Your JIRA Server to Connect to Your SQL Server 2005 Database

There are three ways to configure your JIRA server to connect to your SQL Server database:

  • Using the JIRA setup wizard (not applicable to JIRA WAR) — Use this method, if you have just installed JIRA and are setting it up for the first time. Your settings will be saved to the dbconfig.xml file in your JIRA Home Directory.
  • Using the JIRA configuration tool (not applicable to JIRA WAR) — Use this method, if you have an existing JIRA instance. Your settings will be saved to the dbconfig.xml file in your JIRA Home Directory.
  • Manually — Only use this method if you have a JIRA WAR instance or you have a console-only connection to your JIRA server. You will be required to manually update the dbconfig.xml file in your JIRA Home Directory.

Instructions for each configuration method

JIRA setup wizard

The JIRA setup wizard will display when you access JIRA for the first time in your browser.

  1. In the first screen, 'Configure Language and Database', set Database Connection to My own database.
  2. Set Database Type to SQL Server.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.
 

JIRA configuration tool

  1. Run the JIRA configuration tool as follows:
  2. Navigate to the Database tab and set Database type to SQL Server.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.
  5. Restart JIRA. 
 

Manually

  1. Locate the dbconfig.xml file at the root of your JIRA Home Directory.
    • If this file does not exist, create the file, copy and paste the example XML code (shown below) into this file.
  2. Update the file, as described in the Database connection fields section below. Escape any '&' characters by adding 'amp;' to the end of each one.
    • Note, the <database-type/> element must specify your type of database, e.g. mssql. If you forget to do this and you start JIRA, your database tables may be created incorrectly. See Incorrect database type specified if this happens to you.
  3. Save the file and restart JIRA.

Database connection fields

Setup Wizard / Configuration Tooldbconfig.xmlDescription
Hostname

Located in the <url> tag (bold text in example below):<url>jdbc:jtds:sqlserver://dbserver:1433/jiradb</url>

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

Located in the <url> tag (bold text in example below):
<url>jdbc:jtds:sqlserver://dbserver:1433/jiradb</url>

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

Located in the <url> tag (bold text in example below):
<url>jdbc:jtds:sqlserver://dbserver:1433/jiradb</url>

The name of your SQL Server database (into which JIRA will save its data). You should have created this in Step 1 above.
Username

Located in the <username> tag (see bold text in example below):
<username>jiradbuser</username>

The user that JIRA uses to connect to the SQL Server server. You should have created this in Step 1 above.
PasswordLocated in the <password> tag (see bold text in example below):
<password>jiradbuser</password>
The user's password — used to authenticate with the SQL Server server.
SchemaLocated in the <schema-name> tag (see bold text in example below):
<schema-name>dbo</schema-name>

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

Sample dbconfig.xml file

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

4. Start JIRA

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

  • If you are using a 'recommended' distribution of JIRA, start it up and watch the logs for any errors.
  • If you are using the JIRA WAR distribution, rebuild and redeploy the webapp in your application server.

(tick) Congratulations, you now have JIRA connected to your SQL Server database.

Installation notes

Please see JIRA and MS SQL Server 2005.

11 Comments

  1. Just an FYI if anyone is trying to run JIRA using Windows Authentication with SQL Server (2005 in this case)...
    The documentation says to enter your username and password in the server.xml file. However, this username and password is only for SQL Server Authentication. If you use Windows Authentication, all you have to do is delete these two parameters from the xml file, and (assuming you followed the SSO directions in the jTDS readme, i.e. placing the ntlmauth.dll in the system path) JIRA will start up just fine.

    We have a security policy that requires us to use Windows Authentication with SQL Server, and I'm sure there are others that may have the same requirement, so I figured I'd post this here.

    It took me over a week to figure this out, since there was no documentation concerning it, and since Atlassian's technical support team all said Windows Authentication "should" work, but they weren't sure how to get it to work...

    1. Anonymous

      Cheers for that Leif.

      Boris

  2. Anonymous

    If you need to specificate an Instance name of your SQL Server in the Config tool wirte it in the DataBase field like this: <DatabaseName>;instance=<InstanceName>

    in the Filed Hostname you only speficate the Server name. Its something different in java to the connectionString you known normally

    1. Anonymous

      Additional:

      If your config tool doesn't open check if you have an envirement Variable "JAVA_HOME" in Windows. If not make sure you have instalkled java and make an variable JAVA_HOME = "C:\Program Files\Java\jre6".

      See this post for more infos: http://jira.atlassian.com/browse/JRA-21502

    2. Anonymous

      Thank you very much, I was wracking my brain on this for awhile with no avail.

  3. Anonymous

    In the latest 4.3 download (atlassian-jira-4.3.tar.gz),  there is no <install dir>/bin/config.sh file, and no <install dir>/conf/server.xml file.

    (Similarly, there is no <install dir>/atlassian-jira/WEB-INF/classes/jira-application.properties file).

    Are these supposed to be provided now,  or does this document need to be updated?

    1. I have just downloaded and unzipped http://www.atlassian.com/software/jira/downloads/binary/atlassian-jira-4.3.tar.gz and I can confirm that all those files exist under the given paths.

      Perhaps you should try again, and contact https://support.atlassian.com/ if you continue to have problems.

  4. Anonymous

    OK, for other people having difficulty getting JIRA to connect to a NAMED SQL server instance, check this:

    In my case, my PC is called PATPC, my instance is called SQLEX2008, my database is called "jira", schema is dbo.

    My settings look like this (without quotes):

    Database Type: "Microsoft SQL Server"

    Hostname: "localhost"

    Port: "1433"

    Database: "jira:instance=SQLEX2008"    <--- This is the key to getting it to connect to the correct instance.

    Schema: "dbo"

     

    ALSO, the other thing is that TCP/IP connections MUST be enabled for the Java driver to connect. By default this is NOT enabled. To enable this, run the "SQL Server Configuration Manager".

    Change setting under "SQL Server Network Configuration" -> Protocols ->TCP/IP = enable.

     

    Once I did this, it worked.

  5. Anonymous

    Is there any special treatment when the connection string must be in the form of "<server_name>,<port>", please notice the comma between the parameters.

  6. Note to the Atlassian authors: the "external blog post" mentioned in the first paragraph of "Before you begin" no longer exists.

    1. Thanks Scott, I've fixed this up.