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
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira

5 ways you can make the most of Jira Software and Bitbucket Cloud

As part of the Bitbucket product team I'm always interested in better understanding what kind of impact the use of our tools have on the way you work. In a recent study we conducted of software devel...

82 views 0 5
Read article

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