Windows-based" (Active Directory) authentication with SQL Server

[MrPy]

Setting up jira service desk 3.9.1 and it uses jira 7.6.1
The problem is that we need to authenticate with "Windows-based" (Active Directory) authentication with SQL Server".

Cant get it to work, this is my string im editing in dbconfig.xml>>

<jira-database-config>
<name>jira_service_desk</name>
<delegator-name>default</delegator-name>
<database-type>mssql</database-type>
<schema-name>dbo</schema-name>
<jdbc-datasource><url>jdbc:jtds:sqlserver://aaaabbbb.xxx.xx\instance;databaseName=jira; useNTLMv2=True;domain=domainName</url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

<username>jirauser</username>
<password>jirapasswd</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>20</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<validation-query>select 1</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
<pool-test-on-borrow>false</pool-test-on-borrow>
<pool-test-while-idle>true</pool-test-while-idle>
</jdbc-datasource>
</jira-database-config>

I have made different variations of the string but cant get it to log in, if I create a sql user it works without problem.

Links I have read
https://confluence.atlassian.com/jirakb/setup-windows-based-active-directory-authentication-with-sql-server-720407218.html

https://confluence.atlassian.com/jirakb/updating-jdbc-url-for-microsoft-sql-server-934718842.html

[Andy Heinzer]

Hi,

There is a problem with that documentation.  It was originally created for older versions of Jira and since that time Jira changed the database driver it is using in 7.5 and higher versions.  Previous to these Jira was using the opensource jtds driver, where you could use that 'useNTLMv2' parameter as a means to do this.

However since Jira changed to use the Microsoft driver, you can't use that syntax to do this.  Instead I would recommend checking out Using Integrated Security with MsSQL for Jira.  There are a number of other steps that have to happen to make this work in these more recent versions of Jira.

Even the Microsoft documentation on Building the Connection URL helps to explain how that driver is expecting the syntax of a jdbc url string to be.

Both the MS documentation and our own KB above mention that you need to also use the sqljdbc_auth.dll file in order to make sure that the application (Jira) can authenticate in this fashion.

The other major difference between these drivers is that the Microsoft one only support Windows environments if I recall correctly, whereas the jtds would work on linux environments.

I hope this helps, I will also look to update that KB to indicate this version difference.

Andy

[MrPy]

Thanks for the reply, I saw this information but because we are running our server on a RHEL 7 it will not work :( .
So I had to keep using the old drivers to be able to get it to work.

[Andy Heinzer]

Well, it still might work with these newer versions of Jira, but you would have to manually install the jTDS driver by copying that .jar file into the $JIRAINSTALL/lib/ folder and then update the dbconfig.xml to tell Jira to use that database driver instead of the MS one.

You can do that by changing the driver-class parameter of

<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>

After you install this .jar file, and make this change to the dbconfig.xml, both changes require you to restart Jira before they become effective. 

[Roystine Machado]

Hi Andy,

Our instance of JIRA is on windows server 2016 and the JIRA service is running by the "Local System account".

can I use the setup the DB connection "Using Integrated Security with MsSQL for Jira." as you mentioned using a new AD user created for the connectivity

Regards,

Roystine Machado.

[Andy Heinzer]

Hi Roystine,

The local system account won't have the permissions needed to be able to connect to the SQL server in that case.  Instead you would need to switch the account that starts the Jira service to be a domain user.  This is mentioned in that KB:

The Atlassian JIRA Windows service will need to start as the SQL Domain User that needs to connect to the SQL Server.

I hope that helps.

Andy