Purpose

Implement Windows Integrated Authentication for database connection between Confluence and Microsoft SQL Server using Microsoft SQL Driver

Solution

1.  Download the Microsoft JDBC Driver
   
   1. Database JDBC Drivers
2.  Extract the file
3.  Select the .dll file that you will find under the /sqljdbc_X.X/enu/auth directory
   
   1. there will be x64 or x86 options, choose according to your operating system
4.  Copy the .dll file into <confluence-install>/confluence/WEB-INF/lib
   1. Rename the .dll to sqljdbc_auth.dll in both file locations <confluence-install>/confluence/WEB-INF/lib and /sqljdbc_X.X/enu/auth/x64-or-x32
      

      Note for Microsoft JDBC Driver 8.2 and later

      Starting with Microsoft JDBC Driver 8.2 for SQL Server, the driver relies on mssql-jdbc_auth-<version>-<arch>.dll instead of sqljdbc_auth.dll to use Azure Active Directory Authentication feature. In that case you should use the default name of the DLL file that you have downloaded. (ex: mssql-jdbc_auth-12.8.1.x64.dll) 
      
      Please refer to the below Microsoft Document for more details: 
      
      sqljdbc_auth.dll change
      

      
      

   2. Alternatively, if this path doesn't work, some installations work with <confluence-install>/confluence/jre/bin

      Note for Collaborative Editing users

      If you use Collaborative Editing you have to copy the .dll file to the <confluence-install>/confluence/jre/bin directory so Synchrony will work properly.

5. Select the appropriate mssql-jdbc .jar driver file, under the /sqljdbc.X.X/enu directory
   1. Choose the corresponding .jar driver file for the Java version Confluence is using (choose jre11 jar for anything java 11 and above)
   2. To find out which Java version is bundled with Confluence: Bundled Tomcat and Java Versions
6. Move the .jar driver file, to the <confluence-install>/confluence/WEB-INF/lib directory
   1. Delete any other pre-existing mssql-jdbc .jar files on that directory, besides the driver file you moved

7.  Edit the <confluence-home>/synchrony-args.properties file (or create it, if it doesn't exist) and insert this parameter, in order to specify the path to the .dll file, inside the folder that was extracted earlier, such as:

   java.library.path=C:\\Microsoft JDBC Driver 12.8.1 for SQL Server\\sqljdbc_12.8.1\\enu\\auth\\x64

   1. Alternatively, you can specify the path where you copied the .dll file to, which would be <confluence-install>/confluence/WEB-INF/lib

8. If you start Confluence manually, open the setenv.bat file and insert the following parameter:

   set CATALINA_OPTS=-Djava.library.path=C:\\Same\\path\\specified\\earlier\\ %CATALINA_OPTS%

   1. You need to specify the same path under the synchrony-args.properties file

9. If you start Confluence as a Windows service, add the following parameter under the Java Options tab of the service's properties:

   -Djava.library.path=C:\Same\path\specified\earlier\

   1. This needs to be the same path listed under the synchrony-args.properties file
   2. To open the Confluence service properties, please see: How to set system properties for Confluence running as a service on Windows

10. If setting this up during the installation, during the database setup step, insert the parameter integratedsecurity=true to the database connection string

    1. If Confluence is already setup, add the integratedsecurity=true parameter to the database connection URL, inside the <confluence-home>/confluence.cfg.xml file instead
    2.  Leave username and password tags in confluence.cfg.xml.

Domain User

The Microsoft SQL Server driver will pick up domain credentials from the user that starts the Confluence Windows service, so it will be necessary to change the service owner to the SQL Domain User.

During Confluence startup and upgrades, if the Windows Service is configured to run using an account different from the one valid for the SQL Server, Confluence will try to access the database using that account and you may see the following error:

2020-07-29 14:22:08,001 ERROR [Catalina-utility-1] [confluence.impl.health.DefaultHealthCheckRunner] logEvent Confluence failed to establish a connection to your database.
This could be because: 
	- Your database isn't running
	- The configuration of your confluence.cfg.xml file is incorrect (user, password, or database URL etc.)
	- There is a network issue between Confluence and your database (e.g. firewall, database doesn't allow remote access etc.)

There are several other solutions you can try, review our documentation and see what works for you.

2020-07-29 14:22:08,038 WARN [Catalina-utility-1] [atlassian.confluence.setup.DefaultBootstrapManager] getDatabaseBuildNumber Unable to determine build number from database. If you are upgrading from a Confluence version prior to 2.3, this is expected: Login failed for user 'MYDOMAIN\TEST-CONFLUENCE$'. ClientConnectionId:cd2010e0-ae0b-41df-ac97-3e47c76cf2ef

You will need to change the Confluence Windows service owner to the SQL Domain User.  If the Windows service owner is changed, please ensure that the following directories can be read and written to by this user account:

• The sub-directories of the Confluence Installation Directory:
  • • logs
    • temp
    • work
• The entire Confluence Home directory.

Refer to Creating a Dedicated User Account on the Operating System to Run Confluence.