This server will be upgraded at 3pm Sydney time on December 3rd (December 2nd, 8pm PST) and will be down for up to 30 minutes.
This documentation relates to the latest version of Confluence.
If you are using an earlier version, please go to the documentation home page and select the relevant version.

Database Setup for SQL Server

Added by Mike Cannon-Brookes, last edited by Jeremy Largman on May 07, 2008  (view change)
All Versions
Click for all versions
Confluence 2.10 Documentation

Index

Use this guide in conjunction with the more general Database Setup Guide for Any Database. These instructions add some reference notes specific to SQL Server.

  1. Review Known Issues For SQL Server

  2. Identify Character Encoding
    To identify which character encoding to use, check the encoding used by your application server and Confluence now. All three must use compatible encoding. For example, the default SQL Server encoding of USC-2 is compatible with UTF-8.

  3. Create Database
    As an SQL administrator, create a new database. If you set your application server and Confluence to use an encoding incompatible with USC-2, specify that character encoding for the database.

  4. Create SQL User Account
    As an SQL administrator, create a new user account for Confluence. Provide full create, read and write permissions for the table. Note that Confluence must be able to create its own schema.

  5. Install Database Drivers
    SQL Server users are strongly recommended to install the jTDS JDBC drivers. Copy the driver file jtds-<version>.jar into your standalone's common/lib directory. If you are configuring a datasource to connect to your MS SQL server database, you may find this page helpful: http://jtds.sourceforge.net/faq.html

  6. Start Confluence and visit the home URL (eg http://localhost:8080) to start the Confluence Setup Wizard and select a Custom Install, insert the relevant connection information.

  7. When prompted for a driver class name in the database setup step enter: 
    net.sourceforge.jtds.jdbc.Driver
  8. When prompted for the jdbc url, the format to use is: 
    jdbc:jtds:sqlserver://<server>:<port>/<database>

Labels

database database Delete
sqlserver-setup sqlserver-setup Delete
sql sql Delete
server server Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (11)

 

  1. Dec 19, 2005

    Dev says:

    Confluence also reports a hibernate database error (connection could not be esta...

    Confluence also reports a hibernate database error (connection could not be established) if your data folder in the first step is not configured properly.

    Ensure that your data folder specified in [Confluence\confluence\WEB-INF\classes\confluence-init.properties] uses forward slashes (windows), and does NOT point to the /Confluence/confluence/data directory, instead create a data directory in the root:

    confluence.home=C:/IRC/Java/Confluence/data

    This was not easy to track down.

    From our testing, Confluence works using SQL Server 2005, there should be no changes to SQL server 2005 necessary (but do ensure that tcp/ip is enabled, and if communicating web->db with the same box use port 1433, else if communicating web->db to another box, use port 1434)

    1. Dec 21, 2005

      David Loeng says:

      We have added a note/warning about the slashes to confluence-init.properties to ...

      We have added a note/warning about the slashes to confluence-init.properties to make this clearer.

  2. Jan 24, 2006

    Dev says:

    Using confluence with sql server 2000/2005 with named instances (eg: INSTANCE01)...

    Using confluence with sql server 2000/2005 with named instances (eg: INSTANCE01):

    http://jtds.sourceforge.net/faq.html#instanceName http://www.eggheadcafe.com/ng/microsoft.public.sqlserver.jdbcdriver/post21039344.asp

    In summary, use the instance port number in place of 1433/1434.

    eg. 

    jdbc:jtds:sqlserver://localhost:1100/<database>

    You can find out which port your named instance is using by going to
    Enterprise Manager > Select <database>\<instance name> > Properties > General > Network Configuration > TCP/IP > Properties > (should be 1100+)

    (If you're trying to bind to an existing confluence installation, delete your [\Confluence\data\confluence.cfg.xml] file, and restart setup.)

  3. Nov 21, 2006

    Scott Smith says:

    I was only able to make this work with a SQL Server user; I could never get a Wi...

    I was only able to make this work with a SQL Server user; I could never get a Windows user to work.  Perhaps this is implied by requiring mixed-mode, but maybe my observation will save someone else some time.

  4. Apr 22, 2007

    Garnet R. Chaney says:

    The standalone download of Confluence has a common/lib off of the ${confluenceho...

    The standalone download of Confluence has a common/lib off of the ${confluencehome} directory. But the EAR/WAR version doesn't. So where to put the jtds-<version>.jar in the EAR/WAR version?
    Answer: If you installed the Tomcat 5.5 server, the jtds-<version>.jar goes in the Tomcat installdir/common/lib. Under Windows this might be C:\Program Files\Apache Software Foundation\apache-tomcat-5.5.23\common\lib

    1. May 14, 2008

      Anonymous says:

      one thing is not clear to me is how jdbc provider & data source are configur...

      one thing is not clear to me is how jdbc provider & data source are configured for jtds driver under IBM WebSphere 6.1.x rel.? Has anyone try to make it work under User-Defined...?

  5. Jun 27, 2007

    Francis Lau says:

    In step #3 above "Create Database Table", it should be "Create Database". The JI...

    In step #3 above "Create Database Table", it should be "Create Database". The JIRA instructions for connecting JIRA to SQL Server is clearer. See http://www.atlassian.com/software/jira/docs/latest/databases/sqlserver.html. I adapted the 3 steps as they apply to Confluence too. These 3 steps covers the steps 3 and 4 documented above.

    1. Create a database user which Confluence will connect as (e.g. confluenceuser). Note that confluenceuser should not be the database owner, but should be in the db_owner role. (See SQL Startup Errors for details.)
    2. Create a database for confluence to store issues in (e.g. confluencedb).
    3. Ensure that the user has permission to connect to the database, and create and populate tables.

  6. Feb 15, 2008

    Maarten Sikkema says:

    Setting it up with the Microsoft JDBC driver and Integrated Security (We did th...

    Setting it up with the Microsoft JDBC driver and Integrated Security

    (We did this after an initial setup using the default jTDS driver and standard auth)

    • (for weasels) Backup database and configuration
    • Download Microsoft SQL Server JDBC driver from MSDN and extract it to a temp directory
    • Copy sqljdbc.jar to <confluence-install-dir>\common\lib
    • Create or add to CLASSPATH system environment variable ".;<confluence-install-dir>\common\lib\sqljdbc.jar"
    • Copy x86\sqljdbc_auth.dll to <confluence-install-dir>\bin
    • Create service account in the Windows domain as a Domain Guest
    • Add this service account to the local Administrators group on the Confluence application server (i'm lazy)
    • Make the service account member of the db_owner role for the Confluence database on the SQL Server
    • Configure the Apache Tomcat Confluence service to run under the service account using the Services admin tool. (use account@domain notation, nor DOMAIN\account)
    • Update <confluence-home>\confluence.cfg.xml to use Microsoft JDBC driver instead of sourceforge jtds driver. Replace CONFSERVER:1433 to point to the existing SQL Server instance (see extract below) 
      <properties>
    <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
    <property name="hibernate.connection.password"></property>
    <property name="hibernate.connection.url">jdbc:sqlserver://CONFSERVER:1433;databaseName=Confluence;integratedSecurity=true;</property>
    <property name="hibernate.connection.username">sa</property>
</properties>

    Note that the username and password in the config file are actually ignored. The driver will use the credentials of the Service process that was set before using the Services admin tool.

    This setup seems to work without problems and makes the SQL Server dba happy as he can use the safer "Integrated Mode" setting on SQL Server and the database password is no longer exposed in the config file.

    1. Jun 23

      Anonymous says:

      Make the service account member of the db_owner role for the Confluence databa...
      • Make the service account member of the db_owner role for the Confluence database on the SQL ServerHow do I do this? I can´t find my newly created service account. Im using the SQL Server 2005 managment console (GUI)

  7. Sep 10

    Anonymous says:

    This page indicates that Confluence must be able to create its own schema. ...

    This page indicates that Confluence must be able to create its own schema.   However, in the instructions for any database setup , the documentation says that I should create the schema myself. Which is it?

    1. Sep 19

      Arie Murdianto says:

      Hi, You do not need to create your own schema. Confluence will create a default...

      Hi,

      You do not need to create your own schema. Confluence will create a default schema in SQL server which is dbo. What you only need to do is creating database which is accessible by Confluence to fill in the data inside the database. Please ensure that Confluence has enough privileges to modify your database and please ensure that you use the correct collation (case insensitive).

      Cheers,

Add Comment