Use Integrated Authentication with SQL Server in Jira Server

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

 (info) Windows authentication is the default and is often referred to as integrated security as per mentioned in Microsoft Document

You can use Integrated Authentication with Atlassian JIRA with MsSQL; however, Integrated Authentication is outside the scope of Atlassian Support and Atlassian can not guarantee any support for it.

  • If you need further help with configuring Integrated Authentication, please create a question on the Atlassian Community

目的

Jira does not support Microsoft Integrated Authentication by default. If you need to use Integrated Authentication for SQL Server with Jira, you will need to perform some extra steps. The below documentation is for the Microsoft drivers that ship with Jira.

Microsoft Driver for SQL Server for JIRA 7.5+

Microsoft JDBC driver only supports Integrated Windows Authentication from a Windows application server. The following steps don't apply if you're running Jira on Linux.


Microsoft SQL Server driver will pick up domain credentials from the user that starts the Atlassian JIRA Windows service. While the actual user credentials do not need to be present in the dbconfig.xml file, the username and password tags still need to be present and populated. You may use "dummy" values in these fields.

  • 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, and make sure they are populated with dummy values
    <url>jdbc:sqlserver://[servername\[InstanceName:][portNumber];databaseName=[database];integratedsecurity=true;</url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</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

    In some cases, Full Control access is required for the SQL Domain User for $JIRA_Home and $JIRA_Install folders to avoid issues with startup.

Required step: Including the Microsoft SQL Server driver on the JVM

As the official Microsoft's documentation states, the mssql-jdbc_auth-<version>-<arch>.dll file is required for the integrated authentication to work.

(warning) However, this file is not present in Jira by default.

If this file is not present in the java.library.path, you will see the following error in the startup logs:

2021-07-28 19:32:17,172-0400 JIRA-Bootstrap ERROR [c.a.config.bootstrap.DefaultAtlassianBootstrapManager] Could not successfully test your database: 
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3206)
	at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<init>(AuthenticationJNI.java:72)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4015)
...
Caused by: java.lang.UnsatisfiedLinkError: no mssql-jdbc_auth-x.x.x.x64 in java.library.path

Steps to add and use the required DLL:

  1. Download the full SQL Server driver that matches the version in the Jira Supported Platforms documentation for your version of Jira.
  2. Install or unpack the downloaded file to pull the mssql-jdbc_auth.dll or mssql-jdbc_auth-x.x.x.x64.dll (depending on the version) file out of the auth folder: 

    .\sqljdbc_<version>\enu\auth\x64\mssql-jdbc_auth-x.x.x.x64.dll
  3. Stop your Jira
  4. Add it to a folder in java.library.path.
  5. Edit the <Jira-Install>/bin/setenv.bat file and add this parameter to the recommended arguments, removing any comment symbol:

    JVM_SUPPORT_RECOMMENDED_ARGS="-Djava.library.path=C:\sqljdbc_<version>\enu\auth\x64\"

(info) Please check our documentation on Setting properties and options on startup on how to set the JVM argument if your Jira is running as a Service.

Notejava.library.path is defined in the startup logs. If you are using the JRE that is bundled with Jira, you can include the file in the <Jira-install>\jre\bin folder.

(info) Missing this DLL is known to cause this error: Getting a "This driver is not configured for integrated authentication" error with database import configuration

(info) Restart your Jira and you should be able to connect using the Integrated Authentication.



JTDS driver for JIRA 7.4.X and below 


ここをクリックして展開...

When using Integrated Authentication for any JIRA 7.4.X and below you will need the following configured in your dbconfig.xml file located in $JIRA_Home -

    <url>jdbc:jtds:sqlserver://[servername:[port]]/[database];[instance=][instance_name];integratedsecurity=true;domain=[domain_name];</url>
    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
    <username>[dsql]</username>
    <password>[dpassword1]</password>

JTDS driver picks up the following -

    • integratedSecurity=true
    • domain=mycompany
    • <username>
    • <password>

When credentials in dbconfig.xml are incorrect

JIRA will not be able to connect to the MSSQL database and will not start up.

Errors in atlassian-jira.log will show -

2017-10-13 22:57:33,795 JIRA-Bootstrap INFO      [c.a.j.config.database.SystemDatabaseConfigurationLoader] Reading database configuration from C:\Program Files\Atlassian\Application Data\JIRA\dbconfig.xml
2017-10-13 22:57:34,264 JIRA-Bootstrap ERROR      [c.a.config.bootstrap.DefaultAtlassianBootstrapManager] Could not successfully test your database: 
java.sql.SQLException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
	...
	...

ソリューション

ON JIRA SIDE
  1. If you get the above errors, check the domain name entity, username and password values in dbconfig.xml are correct.
  2. Then restart Jira.
ON SQL SERVER SIDE
  1. Check that the Domain Credentials specified in dbconfig.xml:
    • Has not been blocked by the domain controller
    • Can log into the SQL Server
    • Is a db_owner of the jira database



最終更新日 2022 年 6 月 24 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.