1. Configuring SQL Server

Before you connect Bamboo to a SQL Server, you need to configure SQL Server appropriately.

• Change server authentication to 'SQL Server and Windows Authentication mode' — On a typical SQL Server installation, Windows Authentication mode is the default security mode. However, if you try to connect to the database with a database user using this authentication mode, SQL Server will throw an error. You need to change the server authentication mode to SQL Server and Windows Authentication mode in SQL Server before you can connect Bamboo to SQL Server. See this MSDN article for instructions on how to do this.
  
  Screenshot: Changing the SQL Server authentication mode
  
  
• Configure your firewall to allow SQL Server access — If you need to access SQL server through a firewall, you will need to configure your firewall appropriately. The following MSDN article describes how to configure a Windows firewall to allow SQL Server access, however the instructions are applicable to other firewalls: Configuring the Windows Firewall to Allow SQL Server Access.
• Enable the TCP/IP protocol for your database instance — You must enable the TCP/IP protocol for your SQL Server database instance by following the instructions in this MSDN article.

2. Creating your database

After configuring the SQL Server, you need to create the SQL database.

• Create the database for Bamboo — see this MSDN article for instructions.
• Assign the 'db-owner' role on the database for the user that will access the Bamboo database — the 'db_owner' fixed database role allows the user to perform all configuration and maintenance activities on the database. You need to add this role to the Bamboo user used to access your database by updating the login properties for your database user in SQL Server. Read more about login properties for SQL Server.
  
  Screenshot: Adding the 'db_owner' database role to a database user in SQL Server
  
  
  
  Please ensure that you use a SQL Server user account to log into your database, not a Windows user account.
• Configure the database to use case-sensitive collation — to make the SQL Server database respect case differences in the data it stores (which is required for Bamboo), ensure that you configure it using a case-sensitive collation option such as 'Latin1_General_CS_AS'. To access this feature in SQL Server Management Studio, right-click on the database name, select Properties from the resulting menu, then select the Options page.
  
  Screenshot: Configuring the Bamboo database to use 'Latin1_General_CS_AS' collation
  
  

• Configure the database to use the correct isolation level— Ensure that the new database was set to use Read Committed with Row Versioning as its isolation level. You can apply the new isolation by executing the following query:

  ALTER DATABASE <database name>
     SET READ_COMMITTED_SNAPSHOT ON
     WITH ROLLBACK IMMEDIATE;
  

  To verify the changes, use this query which should result in '1':
  
  

  SELECT sd.is_read_committed_snapshot_on
  FROM sys.databases AS sd
  WHERE sd.[name] = '<database name>';
  

3. Connecting Bamboo to SQL Server

Bamboo provides two ways to connect to a Microsoft SQL Server database — using JDBC or using a datasource. JDBC is generally simpler and is the recommended method.

Connect to SQL Server using JBDC

1. Run the Setup Wizard and choose the Custom Installation method.
2. On the Choose a Database Configuration page, choose External Database > Microsoft SQL Server and click Continue. 

3. Ensure that Direct JDBC connection has been selected and complete the following fields (as shown in the screenshot below):

   Setting	Description
   Driver Class Name	Type com.microsoft.sqlserver.jdbc.SQLServerDriver (if different from the default)
   Database URL	The URL where Bamboo will access your database, e.g. jdbc:sqlserver://localhost:1433;databaseName=bamboo If you are connecting to a Named Instance, you will need to append ;instance=mssqlnamehere to the connection string, where mssqlnamehere is the name of your named instance. For more details about syntax, please refer to the Microsoft SQL Server documentation.
   Username	The username that Bamboo will use to access your database.
   Password	The password that Bamboo will use to access your database.

4. Select Overwrite existing data if you wish Bamboo to overwrite any tables that already exist in the database.
5. Click Continue.

Screenshot: Set up JDBC connection

Connect to SQL Server using a datasource

1. Configure a datasource in your application server (consult your application server documentation for details).
   For details about the syntax to use for the SQL Server database URL, please refer to the Microsoft SQL Server documentation.
2. Run the Setup Wizard and choose the Custom Installation method.
3. On the 'Choose a Database Configuration' page, choose External Database > Microsoft SQL Server and click Continue. 
4. Choose Connect via a datasource (configured in the application server), as shown in the screenshot below.
5. In the JNDI name field, type the JNDI name of your datasource, as configured in your application server.
   If java:comp/env/jdbc/DataSourceName does not work, try jdbc/DataSourceName (and vice versa).
6. Select Overwrite existing data if you wish Bamboo to overwrite any tables that already exist in the database.
7. Click Continue.

Screenshot: Set up Datasource connection