Use jTDS NTLM Authentication in Confluence with SQL Server

Comments

I run Atlassian’s Confluence in several organizations. It’s a great product and runs on many platforms. Being a Windows admin I tend to stick to the existing Microsoft stack where possible, so I typically run Confluence on Windows against Microsoft SQL Server. The jTDS driver that comes with Confluence wants to use a SQL server user and can’t use NTLM/Windows authentication out of the box. With a few simple changes, we can have Confluence using NTLM auth to connect to SQL Server.

Doesn’t Microsoft Provide a JDBC driver?

They sure do! I used this driver up until very recently. The Microsoft driver supports NTLM/Windows auth and failover partner (for database mirroring).

Unfortunately, it’s not supported by Atlassian. It was working fine for a while, but after a recent upgrade, I noticed that the automatic backups stopped working. Then page comments were failing.

After opening tickets with Atlassian, the determination was that there were bugs in Confluence that caused it not to work with Microsoft’s JDBC driver, but they insisted that they could not offer support for it.

So back to jTDS I went. I still long for the failover partner functionality, but at least I got NTLM working again.

The jTDS Way

When I said that NTLM is not supported out of the box, let me clarify. The driver supports it just fine, but the copy that comes with Confluence doesn’t include the Windows DLL that is needed. We need to get the DLL, put it in place, change the connection string, and change the user that the Confluence service runs as.

Create a Windows/AD User

Confluence will need to be running as a specific user that can authenticate against SQL server and access the Confluence database.

If your SQL server is not on the same machine as Confluence, then both machines should be joined to the same Active Directory domain, and the user you create should be a domain user.

When the user is created, make sure that:

  • The user is not prevented from logging into the server which runs Confluence (logon restrictions, etc.).

  • The user can log into SQL server.

  • The user has access to the Confluence database on SQL server.

Download jTDS for ntlmauth.dll

  1. Head over to the jTDS download page and grab the ZIP file.

  2. Extract the ZIP and look at the directory structure.

  3. There is an x86 and an x64 directory; choose the one that corresponds to your Confluence installation (if you’re running x86 Confluence on x64 Windows, choose x86).

  4. Inside, there is an SSO directory, which contains ntlmauth.dll.

  5. Copy this file into your Confluence installation’s bin directory.

Change the Configuration

  1. Browse to your Confluence data directory.

  2. Edit confluence.cfg.xml.

  3. Find the connection URL, which will look something like this:
    <property name="hibernate.connection.url">jdbc:jtds:sqlserver://SQLSERVER:1433/ConfluenceDB</property>
  4. Change that line so that it looks like this:
    <property name="hibernate.connection.url">jdbc:jtds:sqlserver://SQLSERVER:1433/ConfluenceDB;useNTLMv2=true;domain=CONTOSO</property>

  5. The useNTLMv2 property is optional, but I am under the impression that it provides better security.

  6. You’ll also find username and password fields:
    <property name="hibernate.connection.username">theusername</property>
<property name="hibernate.connection.password">thepassword</property>
  7. You can remove those properties entirely, or comment them out.

Changing the Service Parameters

To actually get Confluence running as the user you set up earlier, you need to tell the Windows service to run as the user. You can do this any way you like, such as through services.msc or the command line.

I tend to run Confluence on Server Core when possible so I like to use the command line utility sc.exe:

sc config "Confluence" obj= "CONTOSO\ConfUser" password= "thewinpass"

The spaces between the = and the parameter are important! Or, with PowerShell:

$svc = Get-WmiObject Win32_Service -Filter "name='Confluence'"
$svc.Change($null, $null, $null, $null, $null, $null, "CONTOSO\ConfUser", "thewinpass")

Testing it Out

At this point you can just restart the service and see if it works. If you would rather see some output and not have to dig through logs, you might consider testing this by running confluence manually.

Just remember that you have to run it as the user you’re setting the service to, not as your administrative user, so don’t just go double clicking startup.bat to try it out.

You can run a command prompt as your new Windows user to help with this:

runas /user:CONTOSO\ConfUser cmd

This will prompt you for the password of the new user and then open a command prompt as that user. From there, you can run the startup command for Confluence and see what’s going on.

Other Considerations

Since the data directory is kept intact during an upgrade, you shouldn’t have to worry about the config change during an upgrade, but ntlmauth.dll will be overwritten and need to be replaced. Refer to my post about Confluence and JIRA upgrades for an easy remedy.

To my knowledge there is no way to get that database mirroring failover functionality with jTDS. If you know of a way, I’d love to hear from you!