This page last changed on Mar 03, 2011 by rosie@atlassian.com.
These instructions will help you connect JIRA to a Microsoft SQL Server 2005 database.
 | Note
- SQL Server Express is not one of our recommended databases; however, it is possible to set up JIRA to work with this database. This external blog post contains instructions on installing JIRA on SQL Server Express 2005 that may be helpful, if you want to try this.
- Due to numerous reported performance issues with SQL Server 2000, it is recommended that you use SQL Server 2005 instead. The following instructions apply only to SQL Server 2005, not to SQL Server 2000, which is no longer supported.
|
On this page:
1. Before you begin:
1.1 Export your existing JIRA data
If you are already using JIRA, 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.
1.2 Shut down JIRA
2. Configure SQL Server
- Create a database for JIRA to store issues in (e.g. jiradb). Note that the collation type must be case insensitive, e.g.: 'SQL_Latin1_General_CP437_CI_AI' is case insensitive. If it is using your server default, check the collation type of your server.
 | SQL Server uses Unicode encoding to store characters. This is sufficient to prevent any possible encoding problems. |
- Create a database user which JIRA will connect as (e.g. jirauser). Note that jirauser should not be the database owner, but should be in the db_owner role. (See SQL Startup Errors for details.)
- Create an empty 'schema' in the database (e.g. jiraschema) for the JIRA tables. Please note that 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.
- Ensure that the user has permission to connect to the database, and create and populate tables in the newly-created schema.
- Ensure that TCP/IP is enabled on SQL Server and listening on the correct port (the port is 1433 for the default instance of SQL Server). 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.
- 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'
- 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 2005. Ensure that the SET NOCOUNT option is not selected:

3. Copy the SQL Server driver to your application server
 | Skip this step if you are using JIRA Standalone, as JIRA Standalone includes the driver. |
- Download the SQL Server JDBC driver (v1.2.3) from JTDS.
 | Microsoft have their own JDBC driver but we strongly recommend avoiding it after receiving many reports of intermittent disconnections ( JRA-5760, JRA-6872 ), workflow problems ( JRA-8443 ) and Chinese character problems ( JRA-5054 ). |
- Add the SQL Server JDBC driver jar (jtds-1.2.3.jar) to the <Tomcat install>/lib/ directory.
4. Use the JIRA Configuration Tool
 | The JIRA Configuration Tool is a GUI tool only available if you are using JIRA Standalone distribution. If you are running a WAR-EAR distribution of JIRA, or have a text-only connection to the JIRA server, you will need to configure the files manually.
The JIRA Configuration Tool requires that Java be installed and configured on your operating system. We recommend using a Java platform supported by JIRA — refer to JIRA Requirements for details. |
The Configuration Tool will display your current configuration settings if any are already set.
- Click the 'Database' tab.
- From the 'Database type' drop-down choose 'SQL Server'.

- Fill in the connection details for your SQL Server database:
- Hostname — The name or IP address of the machine that SQL Server is installed on.
- Port — The TCP/IP port that SQL Server is listening on. You can leave this blank to use the default port.
- Database — The database that you will be connecting to.
- Username — The user to connect to SQL Server as.
- Password — The password to use to authenticate with SQL Server.
- Schema — The schema that you want the DB tables created under.
- After typing in your settings, use the 'Test Connection' button to test the connection settings. The tool will attempt to connect to the database, and give a message with the results.
- Click 'Save' to save your settings when you are done.
- Restart JIRA in order for your new settings to take effect.
Congratulations — you have finished!
5. Configure the database connection manually
 | Skip this step if you used the JIRA Configuration Tool (see above). |
5.1 Configure your application server to connect to SQL Server
- Edit the server configuration file and customise the username , password , driverClassName and url parameters for the Datasource, as shown in the code sample below.
- If you are using JIRA Standalone, the server configuration file that you need to edit is conf/server.xml .
- If you are using JIRA WAR/EAR, edit the appropriate file on your application server, e.g. for Tomcat, edit conf/Catalina/localhost/jira.xml . (Note: if you can't find this section at all, you've probably got the wrong file — search for mentions of 'jira' in the files under conf/ .)
<Server port="8005" shutdown="SHUTDOWN">
<Service name="Catalina">
<Connector port="8080"
maxHttpHeaderSize="8192" maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="8443" acceptCount="100"
connectionTimeout="20000" disableUploadTimeout="true" />
<Engine name="Catalina" defaultHost="localhost">
<Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true">
<Context path="" docBase="${catalina.home}/atlassian-jira" reloadable="false">
<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
username="[enter db username]"
password="[enter db password]"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
url="jdbc:jtds:sqlserver://localhost:1433/jiradb"
[ delete the minEvictableIdleTimeMillis and timeBetweenEvictionRunsMillis params here ]
maxActive="20"
[ if maxActive is not set it uses the default number of the connections (8), which may be too little and can cause performance issues ]
/>
<Resource name="UserTransaction" auth="Container" type="javax.transaction.UserTransaction"
factory="org.objectweb.jotm.UserTransactionFactory" jotm.timeout="60"/>
<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>
</Context>
</Host>
</Engine>
</Service>
</Server>
 | If you are using JIRA Standalone, you will also need to edit conf/server.xml , and delete the minEvictableIdleTimeMillis and timeBetweenEvictionRunsMillis attributes. These attributes are only needed for HSQL, and will degrade performance if they are not removed.
In the example above, jiradb is the connection name. You may also have an instance name such that the connection string is jdbc:jtds:sqlserver://localhost:1433/jiradb. |
5.2 Configure the JIRA Entity Engine
- Edit the JIRA Entity Engine configuration file and change the field-type-name attribute to mssql .
- If you are using JIRA Standalone, the JIRA Entity Engine configuration file that you need to edit is atlassian-jira/WEB-INF/classes/entityengine.xml .
- If you are using JIRA WAR/EAR, the JIRA Entity Engine configuration file that you need to edit is edit-webapp/WEB-INF/classes/entityengine.xml . If you forget to do to make this change and start JIRA, it may create database tables incorrectly. See this page if this happens to you.
- Change schema-name="PUBLIC" to the name of the schema associated with the database (i.e. the schema you created in step 2 above), e.g. schema-name="jiraschema" . Note that the schema must exist in the database before you perform this step.
<!-- DATASOURCE - You will need to update this tag for your installation.
-->
<datasource name="defaultDS" field-type-name="mssql"
schema-name="jiraschema"
helper-class="org.ofbiz.core.entity.GenericHelperDAO"
check-on-start="true"
use-foreign-keys="false"
...
 | If you are using JIRA WAR/EAR, your application server may require other changes to entityengine.xml (e.g. to customise the jndi-jdbc tag). |
5.3 Next steps
You should now have an application server configured to connect to a database, and JIRA configured to use the correct database type. The next step is to start it up!
- If you are using JIRA Standalone, start it up and watch the logs for any errors.
- If you are using JIRA WAR/EAR, rebuild and redeploy the webapp in your application server.
Once you have the JIRA server running, you can try accessing the JIRA application in your browser.
Installation notes
Please see JIRA and MS SQL Server 2005.
|