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 2008 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.
  • 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 2008 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 2008 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. (The JIRA on MS SQL Server document provides details on the errors that occur if SET NOCOUNT is set.) 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 2008. Ensure that the SET NOCOUNT option is not selected:

    • You will also need to access the Server > Properties > Connections > Default Connections properties box and clear the no count option.
    • 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.
    (warning) Microsoft have their own JDBC driver but we have not tested JIRA with it. Previous versions of the MS JDBC driver have been known to cause issues: (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 lib/ directory.

3. Configure Your JIRA Server to Connect to Your SQL Server 2008 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 2008.

28 Comments

  1. SQL Server can run multiple "named instances". To specify a named instance, the url in conf/server.xml should be in the form:

    url="jdbc:jtds:sqlserver://<server>:<port>/<database>;instance=<value>"

    for example:

    url="jdbc:jtds:sqlserver://192.168.1.1:1433/jiradb;instance=MSSQLSERVER2008"

    See http://jtds.sourceforge.net/faq.html#urlFormat

    1. SQL Server can run also a single named instance, a single instance with a name not to default (MSSQLSERVER). In the JIRA Setup Wizard, append the instance name in the database field textbox, like:

      jiradb;instance=MyInstanceName

  2. Setup Considerations for Delegating Confluence User Management to JIRA

    If you're planning on delegating USER MANAGEMENT in Confluence to the JIRA DB - BE CAREFUL. The suggestion above to set the schema to something like "jiraschema" will end up causing issues when you try and delegate User Management in Confluence to your new JIRA DB.

    Issue

    Both in this document and in the latest "Delegate User Management to Confluence" document (link), there is some missing/unclear information regarding a known issue with hard coded schema references in Confluence (see KB article).

    If you setup a new JIRA instance connecting to Microsoft SQL 2005 or 2008, as per the installation instructions above, you will end up with a DB SCHEMA called "jiraschema" or something else (e.g. something other than "dbo").

    Create an empty 'schema' in the database (e.g. jiraschema)
    Section 2. Configure SQL Server, Step 3

    Attempts at delegating user management from Confluence to JIRA will fail - presenting as a 'bad username or password' on the Confluence login screen and some obscure log file errors in Confluence.

    Solution "before JIRA database creation"

    Create the JIRA DB to use the 'dbo' schema. (easy)

    Solution "after the fact"

    Follow the KB article referenced above. (not so easy)

    Make a backup copy of your JIRA database. Change the JIRA tables to be under the dbo schema. Update JIRA's entityengine.xml to change the schema name to dbo.

    Backups and updating the 'entityengine.xml' are easy enough - but changing schemas for all the JIRA tables posed a problem.

    Use at Your Own Risk!!!

    Icon

    This code will replace all the schema/ownership in your DB with 'dbo' in one pass on Microsoft SQL 2008 (should also work, in theory, on Microsoft SQL 2005)

    Be Careful. Always take backups and test!

  3. Anonymous

    Be careful not to use any unusual chars in the user and/or schema name. I created jira-user and jira-schema and spent several hours of troubleshooting the problem. Underscores are OK (i.e.: jira_user, jira_schema).

  4. We've seen a few instances where the configuration in server.xml included an instance url, like:

  5. Anonymous

    This took me ages to get Jira working on SQL Server 2008 R2 Named Instance.
    Here is how I did it.
    Launch Connect.bat in the bin folder.
    Database type: SQL Server
    Hostname: OK, say your named instance is "MYSERVER\SQLEXPRESS" - put in "MYSERVER" only here.
    Port: For a named instance you need to get this out of the registry, i.e.
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp
    Where "SQLEXPRESS" in that key will be your instance name.
    Now put in Database, Username, Password and Security Schema, then Test and all should be ok.
    Hi, took me ages to get Jira working on SQL Server 2008 R2 Named Instance despite look at lots of docs. Here is how I did it...

    Launch Connect.bat in the bin folder.

    Database type: SQL Server

    Hostname: OK say your named instance is "MYSERVER\SQLEXPRESS" - put in "MYSERVER" only here.

    Port: For a named instance you need to get this out of the registry, i.e.

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name e.g. SQLEXPRESS>\MSSQLServer\SuperSocketNetLib\Tcp

    Now put in Database, Username, Password and Security Schema, then Test and all should be ok.

    1. Anonymous

      Yes it is working for me SQL Express tcp port was different 50111

       

      Thanks

      Nitin

    2. Anonymous

      Thanks for your post!! it worked for me!

      It was like you sais, checking the registry record.

  6. Anonymous

    Trying to launch config.bat from this path ... C:\Program Files (x86)\Atlassian\JIRA 4.XXXXX\bin
    I get this error on fresh install on Windows 2008 x64. Looks like Java.exe not found under any path PATH variable

    Java is not recognized as an internal or external command. operable program or batch file

    Looks like adding the following path to PATH variable fixed my issue (My Computer->Properties->Advanced Settings->Environment Variable -> PATH (Append the following path )

    C:\Program Files (x86)\Atlassian\JIRA 4.XXXXX\jre\bin

  7. Anonymous

    Microsoft SQL users:

    Anonymous Nov 22 above is indeed correct. Your connection should look like this:

    JIRA Configuration Tool -> Database Tab:

    Database type: SQL Server

    Hostname: MyServer (Your server name and NOT "MyServer\SQLExpress")

    Port: 49374 ( Found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp for your SQL instance name. i.e. SQLEXPRESS) 

    Database: JiraDB

    Username: JiraUser

    Password: jira123

    Schema: dbo

    Connection Pool: 20

    1. Anonymous

      Thanks very much for the info provided on connecting to R2 - this was just what I needed!

    2. Anonymous

      Excellent, exactly what I needed. This should be added as a sidenote into the main document above for MSSQL Named Instances.

  8. Anonymous

    Had a problem with the batch file not finding java - hard coded the path to java so replaced
    set _RUNJAVA="java"
    with
    set _RUNJAVA="C:\Program Files (x86)\Java\jre6\bin\java.exe"

    Then could not connect to sql2008 instance as I didn't have anywhere to enter it.  After trying to configure things manualy - which failed - I went back to the configuration tool and entered the database name followed by the instance as in the database field ie:

    jiradb;instance=sql2008

    This worked.

  9. Anonymous

    2nd the above. For connecting DBs with Instances, you need to specify the DB name followed by the Instance Name.

     

    DBName = [YOURDBName];instance=[YOURINSTANCENAME]

    Can someone please update the guide?

    1. Hi there,

      Please be aware that we've mentioned the following in the documentation above:

      If you have a named SQL Server instance, your <url/> element's content may look similar to — jdbc:jtds:sqlserver://dbserver:1433/jiradb;instance=instance_name

      If this isn't sufficiently clear of if there is anything else that's missing (or that you can recommend), please let us know.

      Regards,

      Giles.

  10. Can you please clarify the level of support of 2008 R2? I can see some people have got it working but I'm unclear if it is officially supported or not. If it's not supported can you indicate when it might be. Thanks

  11. Anonymous

    Dear

    My question is that i am create a new Microsoft forefront Threat Managment Gatway with operating system server 2008 SP2 with coplete update

    but all web services are working on the user side one thing is not working properly my link is that:

    http://onlineiobm.iobm.edu.pk:7778/forms/frmservlet?config=online64

    not working but i on all outbound trafic and apply the setttings and check this dite is working

    and i create a simple protocol just like HTTP & HTTPS then this link is not working

    let me help you kindly i very thankfull to you

     

    Regards,

    Ali

     

     

  12. M

    When I finish the whole sequence of steps in the JIRA walkthrough , I get this message in my browser window: 

    Oops - an error has occurred
    System Error
    Cause:
    com.google.common.util.concurrent.UncheckedExecutionException: java.lang.NullPointerException .

    After that there are stack trace, build info, server info, etc. (full report is on my goo-docs: https://docs.google.com/open?id=0BwJaBRG-2a9gNGNwY2ZESlMzT0E )

    I've searched in google, but didn't find anything relevant.

    Also I've tried to connect Jira to MySQL 5.5, and tried to do it on another machine (with win 7), with the very same result. Here is the process i'm following:
    create db "jiradb" in SQL Server Management Studio
    create login "jiradbuser", disable password policy, set default db to "jiradb", set user mappings as it is in documentation: jiradbuser has roles: public, db_owner
    Enable tcp/ip on server
    set mixed auth on server
    configure Jira with Jira Configuration Tool, test connection (successful).

    Also I've installed new Jira copy and set SQL Server 2008 database while installing (instead of internal hsql db), no effect, as with mySQL too.

    My system settings:
    Win xp sp3
    Jira 5.1

    Thanks in advance!

  13. These resources helped me get JIRA to connect to SQL Server using a trusted/NTLM/windows authentication connection:
    https://confluence.atlassian.com/plugins/viewsource/viewpagesrc.action?pageId=262471708
    http://tomcat.apache.org/tomcat-4.1-doc/config/context.html#Resource%20Definitions

    JIRA 4.0.1 had been connecting to SQL Server using a trusted connection, running the service as a domain account with a windows authentication login to the SQL Server simply by editing the server.xml JDBC connection string to remove username and password, and ensuring that ntlmauth.dll is in the system path.  However, the upgrade to JIRA 4.4 and beyond has broken this by using dbconfig.xml and the JIRA config tool

    To get windows athentication working again I had to
    - manually edit dbconfig.xml to use a JNDI connection, and
    - add a JNDI resource under the context section of the server.xml file which specifies the SQL Server connection properties.

  14. M

    i solved this issue. my db user was incorrect, and some other data. so atlassians tutorial is fully correct, but it dont says about how to specificate dbuser well.

  15. Anonymous

    I had to turn off Dynamic Ports by doing the following:

    1. Go to Sql Server Configuration Manager\Protocols for SQLEXPRESS
    2. Right Click and Properties on TCP/IP
    3. Set Listen All to False under Protocol
    4. Pick IP2 and Go to IPAddresses tab and Set TCP Dynamic Ports to blank.
    5. Make sure IP address of server is correct.
    6. Set TCP Port to 1433
    7. Set all other IPs to Eabled = No (except IP2)
    8. Then you can use the IP address of the server in JIRA Setup screen

     

  16. I got it working with a SQL Named instance, but through trial and error.  In the web configuration wizard, entering in the hostname as "hostname;instance=myinstance" and database as "mydb", it was successfully connecting, but when I clicked Next it was attempting to connect to my database and create tables, but it was trying to create them in the master db (and erroring).  Inspecting the dbconfig.xml, this was because the (incorrect) URL it constructed was:

    jdbc:jtds:sqlserver://hostname;instance=myinstance:1433/mydb

    So I shutdown JIRA and manually changed the dbconfig.xml to have the following:

    schema-name = dbo
    jdbc url = jdbc:jtds:sqlserver://hostname:1433/mydb;instance=myinstance

    restarted JIRA and it created all the tables successfully.

    In retrospect, I am wondering if in the web based wizard you put just the hostname for hostname and then for the database name put BlueS"mydb;instance=myinstance", that might fudge it into constructing the correct jdbc url.

  17. Anonymous

    Does JIRA connect with SQL Server 2012?

    1. Yes, it seems to work, but its not officially supported.  JRA-30019 - Official Support for MS SQL Server 2012 Open

  18. Anonymous

    Just in case anyone has multiple instances of SQL Server on the same machine or cluster, SQL Server dynamically assigns the port when an instance is started. The SQL Server Browser service then handles requests to the specific instance(s) so the port number is not necessary and in some cases will not work. So for any named instance the following connection string should work.

    jdbc url = jdbc:jtds:sqlserver://server_name/jira_db_name;instance=instance_name

    Hope this is helpful for anyone running SQL Server on an active/active cluster with multiple SQL Server instances.

    1. I found success with my cluster using these settings:
      HostName: ServerName/JiraDBName;instance=InstanceName
      Port: 1433
       

  19. Anonymous

    How to call specific sp in sqlserver via JIRA connectivity?

     

  20. Anonymous

    Can someone please make one coherent guide, and stop refering to it as a "document" it's a website. I am completely weirded out by this setup guide.