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

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

1 answer

1 vote

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

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.

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. 

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

561 views 1 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you