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:
1. Install SQL Server
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:
- Using your SQL administrator permissions, create a new database (for example
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;
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;
Using your SQL administrator permissions, create a new SQL user account for Confluence (for example,
Give this user full create, read and write permissions for the database tables. Confluence must be able to create its own schema. Refer to the SQL Server documentation for how to do this.
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.
|Simple||Hostname||This is the hostname or IP address of your database server.|
|Simple||Port||This is the SQL Server port. If you didn't change the port when you installed SQL Server, it will default to |
|Simple||Database name||This is the name of your confluence database. In the example above, this is |
To find out your instance name, connect to your database and run one of the following:
If you have a default named instance setup in SQL Server, you won't need to specify this parameter.
|By connection string||Database URL||The database URL is entered in this format:|
|Both||Username||This is the username of your dedicated database user. In the example above, this is |
|Both||Password||This 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.
If you get the following error message
,check you've given the
confluenceuseruser all the required database permissions when connecting from
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)'