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.
- Review Known Issues For SQL Server
- 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.
- 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.
- 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.
- Install Database Drivers
SQL Server users are strongly recommended to install the jTDS JDBC drivers.- If using direct JDBC connection, copy the driver file jtds-<version>.jar into your standalone's <confluence install>/confluence/WEB-INF/lib directory.
- If you are configuring a datasource to connect to your MS SQL server database, you will need to place the jar file in <confluence install>/lib (for Confluence 2.10 onwards) or <confluence install>/common/lib (for earlier versions). You may find this page helpful: http://jtds.sourceforge.net/faq.html
- 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.
- When prompted for a driver class name in the database setup step enter:
net.sourceforge.jtds.jdbc.Driver
- When prompted for the jdbc url, the format to use is:
jdbc:jtds:sqlserver://<server>:<port>/<database>
Comments (13)
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)
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.
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.
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.)
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.
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
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...?
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.
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)
<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.
Jun 23, 2008
Anonymous says:
Make the service account member of the db_owner role for the Confluence databa...Jul 17, 2009
Anonymous says:
Thanks, very useful post! We tested sqljdbc4.jar driver with SQL2005/SQL2008 and...Thanks, very useful post! We tested sqljdbc4.jar driver with SQL2005/SQL2008 and Confluence 2.8.x and 2.9.x.
All work perfectly.
Sep 10, 2008
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?
Sep 19, 2008
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,
Apr 23, 2009
Tony Atkins [Atlassian] says:
In addition to the steps above regarding named instances, you can also set up SQ...In addition to the steps above regarding named instances, you can also set up SQL Server to listen on the standard port as follows:
Add Comment