Setting up your JIRA database for MS SQL Server 2005

Skip to end of metadata

• Attachments:7
• Added by Andrew Lui [Atlassian Technical Writer], last edited by Felix Schmitz [Atlassian] on Oct 21, 2009  (view change)
• show comment

Go to start of metadata

On this page:

• Overview
• Before you start
• 1. Enable network connectivity for SQL Server
• 2. Configure SQL Server with the appropriate Authentication Mode
• 3. Disable the 'SET NOCOUNT' option in SQL Server
• Setting up the JIRA database
• 1. Create a new database
• 2. Create a new database user
• 3. Create a JIRA database schema

Overview

This page supplements the documentation for Connecting JIRA to SQL Server 2005. It provides detailed instructions on setting up your JIRA database for a straightforward integration of JIRA with SQL Server 2005. Unfortunately we do not provide support for advanced database configuration, such as hardening or performance tuning. If you require a more complex solution, refer to MS SQL 2005 Documentation and, if necessary, consult with someone in your organisation who is knowledgeable in the configuration of SQL Server 2005.

Before you start

1. Enable network connectivity for SQL Server Ensure that your instance of SQL Server allows TCP/IP connection and is listing on the default port. Please note that network connectivity is disabled by default in some versions of SQL Server (e.g. SQL Server 2005 Express edition). Hence, you will have to enable it, as described below: To enable TCP/IP for SQL Server, Open the 'SQL Server Configuration Manager'. Expand 'SQL Server 2005 Network Configuration' in the console pane. Click 'Protocols for <instance name>'. The details pane will display (see screenshot below). Right-click 'TCP/IP' and click 'Enable'. Click 'SQL Server 2005 Services' in the console pane. The details pane will display. Right-click 'SQL Server (<instance name>)' and click 'Restart' to stop and restart the SQL Server service. Screenshot: Enabling TCP/IP for SQL Server 2005 2. Configure SQL Server with the appropriate Authentication Mode 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 for instructions on changing the authentication mode. 3. Disable the 'SET NOCOUNT' option in SQL Server To disable the 'SET NOCOUNT' option in SQL Server, Open the 'SQL Server Management Studio' Navigate to ''Tools' -> 'Options' -> 'Query Execution' -> 'SQL Server' -> 'Advanced'. The advanced settings for SQL Server will display. Ensure that the 'SET NOCOUNT' option is not selected, as per the screenshot below: Screenshot: Disabling 'SET NOCOUNT' for SQL Server

Setting up the JIRA database

To set up your JIRA database for SQL Server 2005, 1. Create a new database Open the 'SQL Server Management Studio'. Connect to the SQL Server that you want to integrate JIRA with. By default this will be 'localhost'. Navigate to '<your server name>' -> 'Databases' in the left menu of the 'SQL Server Management Studio'. Right-click 'Databases' under the server name of your SQL Server and select the 'New Database...' option from the dropdown menu that appears. The 'New Database' window will display. Select the 'General' option in the left menu. The 'General' page will display (see screenshot below). Enter jiradb in the 'Database name' field. Select the 'Options' option in the left menu. Check the collation type, the collation type has to 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. Screenshot: Create jiradb database Click the 'OK' button to create the database. 2. Create a new database user Navigate to '<your server name>' -> 'Security' -> 'Logins' in the left menu of the 'SQL Server Management Studio'. _Right-click the 'Logins' folder and select 'New Login'. The 'Login - New' window will display. Select the 'General' option in the left menu. Enter the database user details into the window that displays (see screenshot below), as follows: Enter 'jirauser' in the 'Login name' field. Select 'SQL Server authentication'. Enter 'jirauser' as the password, and enter 'jirauser' again in the 'Confirm password' field. If you wish to enforce a password policy, check the 'Enforce password policy' checkbox. However, please be aware that you may need to modify the previously entered password ('jirauser') to meet your password policy rules (e.g. your password policy may require numeric characters in all passwords). Ensure that the 'Enforce password expiration' checkbox is unchecked. It will be automatically unchecked and disabled, if you have previously unchecked the 'Enforce password policy' checkbox. Ensure that the 'User must change password at next login' checkbox is unchecked. It will be automatically unchecked and disabled, if you have previously unchecked the 'Enforce password policy' checkbox. Screenshot: Create jirauser user Select the 'User Mapping' option in the left menu. The User Mapping fields for jiradb will display (see screenshot below). Tick the 'jiradb' checkbox. The 'Database role membership for:jiradb' panel will display in the bottom panel of the window. Tick the 'db_owner' checkbox. Click the 'OK' button to save your changes. Screenshot: Create user mapping for jirauser 3. Create a JIRA database schema Navigate to '<your server name>' -> 'Databases' -> 'jiradb' -> 'Security' -> 'Schemas' in the left menu of the 'SQL Server Management Studio'. Right-click the 'Schemas' folder and select 'New Schema' The 'Schema - New' window will display. Select the 'General' option in the left menu. The 'General' page will display (see screenshot below). Fill in the fields, as follows: Enter jiraschema in the 'Schema name' field. Enter jirauser in the 'Schema owner' field. Screenshot: Create JIRA database schema Select the 'Permissions' option in the left menu. The 'Permissions' page will display (see screenshot below). Click the 'Add...' button. Enter 'jirauser' in the 'Enter the object names to select (examples):' field on the pop-up window that displays. Click 'OK' to save your update and close the pop-up window. Specify the schema permissions in the 'Explicit permission for jirauser' table on the 'Permissions' page, as follows: Alter — check the 'Grant' checkbox. Delete — check the 'Grant' checkbox. Insert — check the 'Grant' checkbox. References — check the 'Grant' checkbox. Select — check the 'Grant' checkbox. Update — check the 'Grant' checkbox. Click the 'OK' button to save your changes. Screenshot: Create Permissions for JIRA Schema Congratulations, you have set up a JIRA database for SQL Server 2005. Please refer back to the Connecting JIRA to SQL Server 2005 page to continue integrating SQL Server 2005 with JIRA.

Posted in: Others