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