Setting up JIRA using windows authentication

[Adam Moussa]

When configuring JIRA through the web application on initial setup, the account I'm using to access Microsoft SQL 2012 DB is a service account, but I am getting an error in SQL that I cant connect using SQL Authentication, Server is configured for Windows Authentication only. Which that is the case on how SQL is configured due to STIGS, but I am using a AD Service account and not a SQL account.  

[Andy Heinzer]

Hi Adam,

I understand you are looking to use windows authentication when Jira connects to your MS SQL database.   I am afraid that the KB article Alexey linked to was accurate for Jira at least up until the 7.5.0 release.

In the 7.5 version, Jira changed what database driver it is using in order to connect to MS SQL databases.   In Jira 7.4.x and before versions, Jira is using the open source JTDS driver.  However starting with 7.5 and higher versions, Jira is using the Microsoft driver.

As a result the syntax used to connect to a database for each of these drivers is slightly different.   We have a KB to explain what steps were necessary when Updating JDBC URL for Microsoft SQL Server.  Note that KB is designed for existing installations, and not as much for new installs like yours.   But this detail I think is probably playing a role in why this documented work-around does not work for your fresh Jira 7.6 version.   So we certainly need to update the work-around KB to reflect the changes in 7.6.  I will look to update that once I have a clear solution here.

In the meantime, I can see a few different approaches you might be able to take in order to get around this:

1. Install a Jira 7.4.x version and follow that existing KB steps as a means to complete this install
2. We can try to adjust the syntax for the new Microsoft Driver Jira 7.5+ is using.   The KB I linked to in this post notes that the syntax is as follows:
   

   jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

   as such, we can try to apply the same parameters just in the format of this new driver to see if that works.  I think the url tag of the dbconfig.xml would look something like this:

   jdbc:sqlserver://serverName\instanceName:portNumber;autoCommit=false;useNTLMv2=true;domain=domainName

   I am not certain this will work as the previous users were not using this specific driver to connect Jira to MS SQL.
3. It might be possible to keep Jira on the 7.6 version, but then install the previous JTDS driver for Jira and use that instead.  This way you could still follow the existing KBs steps.  The problem here is that Jira 7.5 and higher does not ship with this old JTDS database driver natively anymore. You can download that driver separately from https://sourceforge.net/projects/jtds/files/jtds/1.3.1/ and then extract the .jar file into the $JIRAINSTALL/lib/ folder, from there you just need to set the $JIRAHOME/dbconfig.xml file to use the correct url and driver-class parameters
   

   <url>jdbc:jtds:sqlserver://serverName:port/databaseName;autoCommit=false;useNTLMv2=true;domain=domainName</url>
   <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>

   And then save this file and restart Jira for these changes to take effect.

I think that any one of these three options could provide you a means to connect Jira with this specific, restricted SQL environment.  Please let me know if you have any questions or concerns.   I am interested to find out if you have success with any of these options.

Regards,
Andy

[Andy Heinzer]

After searching a bit more I came across another existing KB that explains this better and provides the next steps to follow: Using Integrated Security with MsSQL for Jira.  This article explains in even more detail how to make this work with your version of Jira and older versions of Jira as well.

Andy

[Adam Moussa]

Andy,

I installed Jira 7.4, so I shouldn't have to download the driver correct?

[Adam Moussa]

Andy,

Also I tried the below config with specific information to the string in "". I left whats not enclosed in "" as it reads below. Is that correct?

<url>jdbc:jtds:sqlserver://"serverName:port"/"databaseName";autoCommit=false;useNTLMv2=true;domain="domainName"</url>
<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>

[Andy Heinzer]

Adam,
That's right, in 7.4 the JTDS driver is already included with Jira.  With 7.4 you will want the dbconfig.xml file to have these kind of example entries:

    <url>jdbc:jtds:sqlserver://andysqlserver123:1433/jiradb;instance=MSSQLSERVER;autoCommit=false;integratedsecurity=true;domain=example.com</url>
    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
    <username>username</username>
    <password>password</password>

I wasn't sure about your example with quotes, so I just wanted to provide an example where the sql server name is andysqlserver123, the SQL instance is using the default port of 1433, the database name is jiradb, (the instance name is usually optional, it might be required if you have more than one SQL service running on that hostname/address), and in this case the domain name is example.com.


The older KB suggests using the useNTLMv2=true parameter.  However the newer KB instead suggests using the integratedsecurity=true value instead.   I would tend to lean towards the newer parameter as a means to connect Jira with this database in this case.

Please let me know the results.
Andy

[Adam Moussa]

<url>jdbc:jtds:sqlserver://OURSERVERNAME:1433/JIRA;autoCommit=false;integratedsecurity=true;domain=FQDN</url>
    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
    <username>username</username>
    <password>password</password>

So I reconfigured the dbconfig file with the above settings. I tried both NTLM and intergartedsecurity and got this error: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [ CLIENT: IP ]. I assume username and password string should be blank since it would be bad to have a pw in plain text, and the Atlassian JIRA service is running with the service account that has dbo permissions assigned to the DB I created.

[Andy Heinzer]

Actually the username and password values in the dbconfig need to be accurate in 7.4.   You will likely need to use a DOMAIN\username format for the useraccount.

If you were using the newer Microsoft driver in Jira 7.5+ then those fields still have to exist in the dbconfig, but they don't necessarily need be the correct values in this version of Jira.

[Adam Moussa]

Andrew,

After making the username/pw change I'm still getting the same error in SQL.

[Gary Tingley]

Andrew, do you have a recommendation?

[Adam Moussa]

Andrew,

Can you also verify which services should be running for JIRA, and confirm it should be running with the domain service account I created that also has dbo set for the JIRA DB.

[Andy Heinzer]

Hi Adam and Gary,

Sorry for the delay in response here.

The guide we have in https://confluence.atlassian.com/jirakb/using-integrated-security-with-mssql-for-jira-939494383.html has some slightly different configurations steps between 7.4 versions of Jira and later 7.5 and above.

In reviewing these I am noticing that the 7.4 and before versions do not explicitly mention using the windows/domain account to start the Jira Service.  However the 7.5 instructions, that use the newer MS driver do clearly indicate to do this in this kind of configuration.   So if we still are struggling to get 7.4 setup this way, perhaps you can try with a 7.5 or 7.6 version of Jira and follow the steps we have there.   It looks like this version requires the use of an additional dll in order to get Jira to be able to authenticate in that setup.

Also I wanted to check with you in regards to the OS that Jira is running on top of; is this a Windows Server?  Or is this perhaps a Linux/Unix based OS?   The reason I ask is that there have been documented instances of Linux/Unix operating systems that when trying to use Jira 7.5 or higher can still connect to the database, but are unable to use this kind of authentication.   I suspect it might relate back to the use of this dll in this version and that there might not be a corresponding library available to non-windows platforms that would be needed to authenticate correctly.

Forgive me for copy/pasting nearly that whole article, but if I understand your network restrictions you might not be able to view the KB on our documentation site.   As such

Microsoft Driver for SQL Server for JIRA 7.5+

Microsoft SQL Server driver will pick up domain credentials from the user that starts the Atlassian JIRA Windows service, credentials appear not to be needed in the dbconfig.xml -

• <username>
• <password>

However, tags needs to still be present in the dbconfig.xml file as JIRA still looks for it on startup.

Please convert the dbconfig.xml to the newer format from JTDS - Updating JDBC URL for Microsoft SQL Server

• Ensure that integratedsecurity=true is present
• You can remove domain=<mydomain> from the <url> string as it is not used by the Microsoft Driver
• Leave the <username> and <password> tags in as JIRA still checks for the presence of these 2 entities.

    <url>jdbc:jtds:sqlserver://[servername\[InstanceName:][portNumber];databaseName=[database];integratedsecurity=true;</url>
    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
    <username>[dummyuser]</username>
    <password>[dummypassword]</password>

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

• If the Windows Service is still running as the "Local System Account" then JIRA will try to access the SQL Server as that account and you may see an error that Login failed for user "Local System Account"

2017-10-15 02:34:35,809 JIRA-Bootstrap ERROR      [c.a.config.bootstrap.DefaultAtlassianBootstrapManager] Could not successfully test your database: 
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'MYDOMAIN\WIN-1236CNIQFHD$'. ClientConnectionId:cd2010e0-ae0b-41df-ac97-3e47c76cf2ef

• You will need to change the Atlassian JIRA Windows service owner to the SQL Domain User
• Make sure that the SQL Domain User has write access to (especially if service was previously starting as Local System account) -

  • $JIRA_Home 

  • $JIRA_Install/work

  • $JIRA_Instal/temp

  • $JIRA_Install/logs

Further configuration required with Microsoft SQL Server driver -

There will need to be some configuration done based on Microsoft's documentation and would require sqljdbc_auth.dll.

Add the following into the JVM argument and restart JIRA in order for it to use sqljdbc_auth.dll -

-Djava.library.path=C:\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_<version>\enu\auth\x64

Please check our documentation on Setting properties and options on startup on how to set the JVM argument.

I am interested to see if perhaps trying these steps with a Jira 7.5+ might yield some better results.

Please let me know

Andy

[Adam Moussa]

Andy,

I downloaded 7.6 and installed it, also configured the domain service account and created the JIRA DB with dbo permissions for the account. Attempting to configure the dbconfig.xml, but the file is nowhere to be found under the Atlassian directory. Tried to configure the connection through the web interface and received the following error as well.

Error connecting to database

Login failed for user 'N025-JIRA-SVC'.

[Andy Heinzer]

Hi Adam,

The dbconfig.xml will need to exist in the $JIRAHOME/ root directory.   This home directory will have other subdirectories in it with names such as 'data', 'import', 'export', 'plugins', etc.  However there typically are not very many other files in this same folder itself.

This home directory might not exist on the first startup of Jira.   But you can go into the $JIRAINSTALL/bin/ directory and run the config.bat file.  This utility allows you to setup the home directory as well as try to configure the database, and what port the webserver is hosting on. (If you get an error that this utility can't find the correct version of Java, then I would recommend installing your own Oracle Java JRE or Java JDK that is an x64 version, and then setting up the JAVA_HOME variable, and restarting the OS.   These steps might be necessary before you can use the Jira config tool I recommend here.   Steps on how to do this are in the Installing Java document on our site.)

I tend to suggest this as a method to use over the setup wizard gui because it will let you set certain parameters before Jira starts up.  Also it will create this dbconfig.xml file and the other directories needed when you save in this utility.   If we can get the SQL authentication setup correctly this way before starting Jira, the setup wizard will still launch, but it will just prompt you for the basic information it needs to create a new Jira database, and not information like SQL credentials.   In this case, with this kind of setup, we are not expecting the dbconfig.xml to actually have the correct username and password.  But those username and password tags have to exist in this file.   If we're doing this correctly, and starting Jira via a service, that service should be configured to start as this Windows/Domain user.

Since you're seeing this login failure, I think there is a specific section of the previously mentioned KB that explains there is the need for an additional dll file to be included so that this account and authenticate with SQL in this way:

Further configuration required with Microsoft SQL Server driver -

There will need to be some configuration done based on Microsoft's documentation and would require sqljdbc_auth.dll.

Add the following into the JVM argument and restart JIRA in order for it to use sqljdbc_auth.dll -

-Djava.library.path=C:\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_<version>\enu\auth\x64

Please check our documentation on Setting properties and options on startup on how to set the JVM argument.

If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc_auth.dll file in the x86 folder, even if the operating system is the x64 version. If you are running a 64-bit JVM on a x64 processor, use the sqljdbc_auth.dll file in the x64 folder.

Also I would expect in this kind of setup that the username is going to need to have a format of either DOMAIN\username or HOSTCOMPUTER\username in order to use Windows Authentication in this manner.   The fact that you only see this username as a login failure tends to indicate to me that we might not be specifying where that user account is coming from.    It might also help to check with the MS SQL server itself under the security/accounts section to make sure that the SQL user in question also is following that same naming convention there.

[Adam Moussa]

Andy,

I installed Java so I could run the config.bat file, then when through the configuration to create the xml below. (I removed the server name, username and password in this copy)

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>mssql</database-type>
  <schema-name>dbo</schema-name>
  <jdbc-datasource>
    <url>jdbc:sqlserver://SERVERNAME:1433;databaseName=JIRA;integratedSecurity=true</url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <username>DOMAIN\USERNAME</username>
    <password>PASSWORD</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 read through There will need to be some configuration done based on Microsoft's documentation and would require sqljdbc_auth.dll., but I was unable to locate that dll. Should that have been installed on the server when JIRA was installed.

New error in SQL - Login failed for user "" Attempting to use an NT account name with SQL Server Authentication.

[Andy Heinzer]

Hi Adam,

In this case, I don't believe that Jira is including this sqljdbc_auth.dll.  Sorry that part wasn't clear.

Instead this is something that you can download from Microsoft's site: https://www.microsoft.com/en-us/download/details.aspx?id=11774

That link offers you either an exe or a tar.gz download package.  I tested the tar.gz and I found that it did have the path to that specific dll in the /enu/auth/x64/ directory.

[Adam Moussa]

Do I just copy that tar.gz to a directory? Or do I have to do something else with it?

[Andy Heinzer]

That is an archive file.  You need to extract that archive in order to locate the sqljdbc_auth.dll file within it.   This archive type is not something Windows default compression can extract if I recall.  You might need to use a program like 7zip to open this archive.

Once you have extracted this file, you need to place it in a directory such as

C:\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_<version>\enu\auth\x64

Once you do this, then you need to edit the startup options in Jira to tell Jira where how to load this jar.  You can add this in with a JVM startup parameter of:

-Djava.library.path=C:\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_<version>\enu\auth\x64

 but you will need to follow the steps in Setting properties and options on startup to do this.   The steps on how to do this are different depending if you're starting Jira as a service as I think you would be here.

[Adam Moussa]

Andrew,

So I extracted sqljdbc_6.0.8112.100_enu.tar to C:\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64 dir. Looking at the catalina log I'm getting the below error.

03-Jan-2018 08:07:27.400 INFO [main] org.apache.catalina.core.AprLifecycleListener.lifecycleEvent The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64

[Adam Moussa]

I also followed the "Setting properties for Windows services via command line" from https://confluence.atlassian.com/adminjiraserver/setting-properties-and-options-on-startup-938847831.html

[Andy Heinzer]

That's an INFO message.  It is not indicative that there is actually any error or problem here.  I think we can actually safely ignore that particular message here. 

Are you seeing a problem with Jira actually starting up? 

[Adam Moussa]

Went I extracted the file using 7ZIP, it is a .tar file. Is that what it suppose to be, because you mentioned a dll and their wasn't one in the zip.

[Andy Heinzer]

That can be confusing.  TAR is one compression format, and GZ is another.   Some compression programs just understand this is a single archive, while others are far more strict in regards to the file extensions and in turn treat them as two separate archives.  If you opened this file with something like 7zip and still only see the tar, then you will likely need to extract that tar file as well, after you have extracted that from the gz archive.   When I extract this file, I do have a path of

/sqljdbc_6.0/enu/auth/x64/sqljdbc_auth.dll

For that dll file.   Since you seemed to have the corresponding path, I was hopeful that you also saw the dll file in there as well, but if you are not seeing this file when you extract the archive, then perhaps we should try to download the exe option from that Microsoft link instead: https://www.microsoft.com/en-us/download/details.aspx?id=11774 and in turn try to run that as a means to make sure that your system has this dll installed somewhere.  Once you can find this specific dll on the system, then you can update the startup option to refer to that specific path so that this dll can be used by Jira when it starts up.

[Adam Moussa]

Andrew,

I was able to extract the .tar and the dll showed up in the folder I created C:\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64. I also verified -Djava.library.path=C:\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64 was added to the Java Options.

Can you verify the dbconfig.xml below is correct? I took out the server name and password in this example, but all the rest is configured as is.

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>mssql</database-type>
  <schema-name>dbo</schema-name>
  <jdbc-datasource>
    <url>jdbc:jtds:sqlserver://SERVERNAME:1433;databaseName=JIRA;integratedsecurity=true;</url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <username>SOF\N025-JIRA-SVC</username>
    <password>PASSWORD</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>

[Andy Heinzer]

Hi Adam,

Thanks for posting back. I took a look at this dbconfig.xml, but I think I found a problem. I believe your URL tag needs to look like this instead:

<url>jdbc:sqlserver://SERVERNAME:1433;databaseName=JIRA;integratedsecurity=true;</url>

The one you posted still had the format for the 'jdbc:jtds:sqlserver....'  but we need to remove that old reference to the jtds in Jira 7.5.x and higher.  Everything else in your dbconfig.xml looked ok to me.

I also found that our KB on this was not updated to reflect this change, so I have also gone through to update that as well.  Sorry about that.

Once you have that set, I believe the next step is to make sure that the Windows Service that starts Jira is configured to be started by a user account that is a Windows user on your Domain.  

Let us know how it goes.

Andy

[Adam Moussa]

Andrew,

Looks like that worked, but is it normal to see 18 instances of the server account to be connected the SQL in the Activity Monitor? Also do you know if this application supports connection to a SQL cluster with multiple nodes?

[Andy Heinzer]

By default Jira has a connection pool value of 20, so that would seem to fit in with the 18 separate connections you are seeing.  You can try to Tune the connection pool settings, but just be aware that incorrectly adjusting this can negatively effect performance in Jira very easily.

As for the clustered SQL support, that's a great question.  So far I have not been able to find any official documentation that states Atlassian supports that particular database configuration.  There is an older feature request that I believe we need to have updated now in https://jira.atlassian.com/browse/JRASERVER-43873

The limitation there was the older jdbc database driver did not support that kind of connection.  But since you are now using the official Microsoft database driver, I believe this could work.   Since the Supported Platforms does not currently include this setup (nor does the Connecting Jira to MS SQL 2014 guide), I do not believe that Atlassian's standard support offerings would technically support that data setup just yet.

[Alexey Matveev]

Please read this article about setting up SQL Server:

https://confluence.atlassian.com/adminjiraserver071/connecting-jira-applications-to-sql-server-2012-802592195.html

I guess the user must be a database user. If you want to use Windows Authentication then you should setup a trusted SQL connection or you have to use Mixed Authentication Mode.

[Adam Moussa]

Due to our network I cant get to that link you suggested. Also the service account is a domain account and has DB Owner permissions to the SQL DB. Also have the service account running the Atlassian JIRA Service (tomcat8.exe)on the application server. I tried the other JIRA service and just get an OOPS page.

[Alexey Matveev]

As far as I know Jiira does not support domain accounts for sql server. You need to make a database user and setup a trusted SQL connection. 

You can also use a workaround but there is a disclaimer about using it.  Hopefully you have access to this page:

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

[Gary Tingley]

The "unsupported" workaround resulted in an error.