Confluence SQL Server JDBC Connection URL authentication issue

Question

Hi,

I am trying to connect to a newly set up SQL Server 2016 database (separate server from app server) from Confluence 6.13 and have some issues with user/password authentication.

Previously we used the JDBC JTDS driver and used "domain=XYZ" in the database URL to specify the domain the user, connecting to the database, is part of.  However, that do not work any longer, I assume because of the new database driver. Using Integrated Windows Authentication by the way.

I successfully connected to the database if I log on to the server with the same user that has access to the database and set integratedSecurity=true but I don't want to do that.

Database URL that works:
jdbc:sqlserver://serverXYZ:1433;instanceName=HOTELL2016V;databaseName=XYZCONFLUENCE_VER;integratedSecurity=true

If I remove integratedSecurity=true and specify the user/pw I get the following error:

----
Incorrect username or password

SQLState - S0001
Error Code - 18456
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'ADMXTN031'. ClientConnectionId:cc0ed895-9303-4d5a-9f46-3e8187afeeeb
-----

Any ideas what is wrong? Tried authentication=ActiveDirectoryIntegrated but  then I get the following problem:

-----
Problem connecting to your database
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot use "Authentication=ActiveDirectoryIntegrated" with "User", "UserName" or "Password" connection string keywords.
-----

Any ideas what is wrong?

unknown · Answer

Hi Diego!

Thanks for the reply. The integrated authentication (setting integratedsecurity=true), I read somewhere that it uses the user that runs the Confluence Windows Service, if that is true, I think this method will work for us.

Best regards
Daniel

Diego_88dj6o · Answer

Hello there Daniel!

There could be some problem with the settings for the Authentication used for your database. Confluence requires that the authentication mode is set as Mixed:

SQL Server allows two types of authentication: SQL Server Authentication and Windows Authentication. To make sure Confluence will be able to connect to your database you'll need to set your SQL server to allow Mixed Authentication (both SQL Server and Windows modes). This setup is generally found under Properties > Security > Server Authentication.

reference:

Database Setup for SQL Server

Using the integrated authentication is an option in your case. However, you stated that this is not desired for your instance. Nonetheless, we have some documentation on how to set this kind of authentication:

How to use Windows integrated authentication for Microsoft SQL Server in Confluence 6.5 or newer

For short, if you shutdown both Confluence and database and after that change the authentication mode to mixed, the login process should work.

Daniel, please let us know your thoughts!