Database Setup for SQL Server

This page provides instructions for configuring Confluence to use a Microsoft SQL Server database.

Before you start

Check the following before you start:

  • See Supported Platforms to check your version of SQL Server is supported. You may need to upgrade your database before installing Confluence.
  • If you're switching from another database, including the embedded evaluation database, read Migrating to Another Database before you begin.

1. Install SQL Server

If you don't already have Microsoft SQL Server installed, download and install it now. See Installation for SQL Server on MSDN for step-by-step instructions.

Note about authentication modes...

SQL Server allows two types of authentication: SQL Server Authentication and Windows Authentication. To make sure Confluence will be able to connect to your database you'll need to set your SQL server to allow Mixed Authentication (both SQL Server and Windows modes). This setup is generally found under Properties > Security > Server Authentication.


2. Create a database and database user

Once you've installed SQL Server, create a database user and database for Confluence as follows:

  1. Using your SQL administrator permissions, create a new database (for example confluence)
  2. Set the default collation for the database to SQL_Latin1_General_CP1_CS_AS (case sensitive).  

    ALTER DATABASE <database-name> COLLATE SQL_Latin1_General_CP1_CS_AS

    If you see a 'database could not be exclusively locked to perform the operation' error, you may need to prevent other connections by setting the mode to single user for the transaction

    ALTER DATABASE <database-name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    <your ALTER DATABASE query>
    ALTER DATABASE <database-name> SET MULTI_USER;
  3. Check the database isolation level of READ_COMMITTED_SNAPSHOT is ON.  

    SELECT is_read_committed_snapshot_on FROM
    sys.databases WHERE name= 'database-name' 

    If this query returns 1, then READ_COMMITTED_SNAPSHOT is ON, and you're good to go. 

    If this query returns 0, READ_COMMITTED_SNAPSHOT option is OFF and you will need to turn it on as follows:

    ALTER DATABASE <database-name>
       SET READ_COMMITTED_SNAPSHOT ON
       WITH ROLLBACK IMMEDIATE;
  4. Using your SQL administrator permissions, create a new SQL user account for Confluence (for example, confluenceuser). 

  5. Give this user the default schema as follows:

    ALTER USER <confluenceuser> WITH DEFAULT_SCHEMA = dbo
  6. Make sure this user has full create, read and write permissions for the database tables. Confluence must be able to create its own schema, and have the ability to create/drop triggers and functions. Refer to the SQL Server documentation for more information. 

3. Install Confluence 

Check out the Confluence Installation Guide for step-by-step instructions on how to install Confluence on your operating system. 

4. Enter your database details

The Confluence setup wizard will guide you through the process of connecting Confluence to your database. 

Use a JDBC connection (default)

JDBC is the recommended method for connecting to your database.

The Confluence setup wizard will provide you with two setup options:

  • Simple - this is the most straightforward way to connect to your database.
  • By connection string - use this option if you want to specify additional parameters and are comfortable constructing a database URL. 

Depending on the setup type, you'll be prompted for the following information.

Setup typeFieldDescription
Simple HostnameThis is the hostname or IP address of your database server.  
Simple PortThis is the SQL Server port. If you didn't change the port when you installed SQL Server, it will default to 1433.
SimpleDatabase name This is the name of your confluence database. In the example above, this is confluence
SimpleInstance name

To find out your instance name, connect to your database and run one of the following:

select @@SERVICENAME;
SELECT SERVERPROPERTY('InstanceName');

If you have a default named instance setup in SQL Server, you won't need to specify this parameter.

By connection stringDatabase URLThe database URL is entered in this format:
jdbc:sqlserver://<hostname>:<port>;databaseName=<database>

For example:  
jdbc:sqlserver://yourserver:1433;databaseName=confluence

BothUsernameThis is the username of your dedicated database user. In the example above, this is confluenceuser.
BothPasswordThis is the password for your dedicated database user.

Use a JNDI datasource

If you want to use a JNDI datasource, see Configuring a datasource connection for the steps you'll need to take before you set up Confluence, as the setup wizard will only provide the option to use a datasource if it detects a datasource in your Tomcat configuration. 

5. Test your database connection

In the database setup screen, hit the Test connection button to check:

  • Confluence can connect to your database server
  • the database collation and isolation level is correct
  • your database user has appropriate permissions for the database

Once the test is successful, hit Next to continue with the Confluence setup process. 

Database driver changes

In Confluence 6.6 we replaced the open source jTDS driver for Microsoft SQL Server with the official Microsoft JDBC Driver for SQL Server. You will be automatically migrated to the new driver when you upgrade to 6.6 or later.  

If for some reason the automatic migration fails (for example, if you're using a datasource connection), you'll need to make this change manually. See Migrate from the jTDS driver to the supported Microsoft SQL Server driver in Confluence 6.4 or later.  

Troubleshooting

  • If you get the following error message, check you've given the confluenceuser user all the required database permissions when connecting from localhost.

    Could not successfully test your database: : Server connection failure during transaction. Due to underlying exception: 'java.sql.SQLException: Access denied for user 'confluenceuser'@'localhost' (using password: YES)'
Last modified on Jan 31, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.