Confluence MS SQL Server connection with Microsoft Authentication Mode

[Andreas Essigkrug]

Dear all,

we want to use MS SQL for a Confluence installation.

Unfortunately the company policy requires Microsoft Authentication Mode to authenticate at the Database.

This is possible for Jira, but I didn't find a solution for Confluence.

The "known Issues" for Confluence just say "Switch to Mixed mode". But this is no option.

https://confluence.atlassian.com/confkb/known-issues-for-sql-server-119669.html

Does anybody know a solution, or is this really impossible?

[AnnWorley · Accepted solution]

Hello Andreas,

If you are performing a new installation of the latest, Confluence 6.6 then you should be able to use Windows based authentication, using the integratedSecurity=true parameter. Please see Support for Microsoft SQL Server JDBC Driver

If you are using a version of Confluence prior to 6.4 then you may follow this article: Cannot connect Confluence to MS SQL Server using a domain account

I look forward to hearing whether either of these works for you.

Thanks,

Ann

[Andreas Essigkrug]

Hello Ann,

many thanks for your fast answer. It seems exactly what I needed.

As soon as we tried it out, I let you know the result (fear this will be next year).

regards,

Andreas

[Andreas Essigkrug]

Hello Ann,

we tried now to install it with Confluence 6.5.2, using the connection url at db configuration time.

We added the parameter integratedSecurity=true to the url, typed in the db user and tested the connection.

The test ended with an error message like "jdbc driver is not configured for integrated authentication" - sorry I have the exact message not at hand.

Searching for hints in the internet, I found out, that I either have to copy the sqljdbc_auth.dll to a system folder, where the driver finds it (but which for confluence)? Or give an additional parameter for kerberos (but I don't think that is the right one).

Do you have a more detailed description for this configuration?

[AnnWorley]

Yes, sqljdbc_auth.dll is required as you pointed out. Please see: Connecting with Integrated Authentication On Windows

You may either make sure the Windows system path includes a directory with the .dll or else pass a java system property (-Djava.library.path) to Confluence as described in: Configuring System Properties

Thank you for keeping me in the loop, I look forward to your next update.

[Andreas Essigkrug]

Hi Ann,

many thanks for the fast answer. As we struggle with any possible directory (\windows, windows\system32 ...), what exactly is meant by Windows system path. Do you have a running environment and an exact description?

We can try the hint with the system properties, I let you know.

[Andreas Essigkrug]

Hi Ann,

because of the efforts it took, we changed to your second solution with the jdt driver:
Cannot connect Confluence to MS SQL Server using a domain account
and it worked!

Nevertheless it would be good to have an exact descripton what to do for the Microsoft jdbc driver (e.g. where to exactly copy the dll and if e need the 32bit or 63bit dll). It would save others from trail and error.

[AnnWorley]

Happy to hear you were able to log into the database with Microsoft Authentication Mode at last.

The windows system path is a Windows OS environment variable, it is not part of Confluence. This article has great details for several versions of Windows: How do I set or change the PATH system variable?

Whether you need the 32 bit or 64 bit .dll depends on your Java version:

Connecting with Integrated Authentication On Windows

If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc_auth.dll file in the x86 folder, even if the operating system is the x64 version. If you are running a 64-bit JVM on a x64 processor, use the sqljdbc_auth.dll file in the x64 folder.

The JRE bundled with Confluence 6.6.x is 64-bit; different versions of Confluence can run with different Java versions. Most Windows instances are installed with the .exe installer, which comes with the bundled JRE, but there are also Windows instances that use the system Java (installed using .zip archive file).