Jira Align Enterprise Insights Self-Hosted ETL not working and no activity is logged
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
After installing or upgrading the EI Self-Hosted, the ETL service of the SQL Server is not running and logging no activity.
Environment
Jira Align Self-Hosted
Diagnosis
Going to the server of Enterprise Insights, we can get the logs of the Windows Event Viewer and ETL logs from the SQL Server.
On the Windows Event View, we will be able to see the following error:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The logon attempt failed [CLIENT: 10.200.6.50]
The user can check that the main issue is that the SQL Job is not working. We can go to SQL Server > SQL Server Agent > Jobs and confirm that the Job is not starting and it is triggering the error "The job failed. The job was invoked by user<user>. The last step to run was step1".
Expanding the error detail, we were able to see the error message: "Login failed. The login is from an untrusted domain and can't be used with Integrated authentication. (Microsoft SQL Server, Error: 18452)"
The full message of the errors:
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. (Microsoft SQL Server, Error: 18452)
The job failed. The Job was invoked by User sqladmin. The last step to run was step 1
Cause
Regarding the error "The login is from an untrusted domain and cannot be used with Integrated authentication. (Microsoft SQL Server, Error: 18452)". That would typically indicate an issue with the authentication mode used by MS SQL Server. Integrated authentication means Windows Authentication and the login attempt originates from a domain that is not trusted.
This message comes from the ETL job created, and it is worth mentioning that the user account that is indicated during the installation script is linked to the job.
So, this error is related to that user linked to the Job. Since the job was created and related to a user, the only way to change it is to run the EI script again to override the ETL job created.
During the EI installation, the user must obey the privileges of the user's account used in the EI installation script. The table that we have to follow is shown below:
Setting | Example | Purpose |
[DW_USERNAME] | SystemAdmin | EI Data Warehouse DB user account Note: The installation script (and upgrades) will require this account to be able to create and populate the Data Warehouse DB. Therefore the account will need to be assigned the dbcreator role initially. If the organization chooses to grant the least privilege after EI installation or upgrade is complete, this account can be granted db_datawriter access to the [DW_DB_NAME] database. |
[DW_PASSWORD] | JiraAlign! | EI Data Warehouse DB user account password |
[DW_SERVER_NAME] | <localhost> | EI Data Warehouse DB server FQDN |
[DW_DB_NAME] | datawarehouse | EI Data Warehouse DB name |
[ETL_JOB_NAME] | Align ETL Job | EI ETL job name |
[ETL_SERVER_NAME] | localhost | EI ETL database server FQDN |
[ETL_USERNAME] | SystemAdmin | EI ETL job Windows account user Note: This Windows user will require to Modify access to the BI ETL folder and will need to be assigned to the db_ssisadmin fixed database-level role, or, optionally, assigned sysadmin for the instance. |
[ETL_PASSWORD] | JiraAlign! | EI ETL job Windows account password |
[JA_SERVER_NAME] | <localhost> | Existing Jira Align DB server FQDN |
[JA_DB_NAME] | ja | Existing Jira Align DB name |
[JA_USERNAME] | SystemAdmin | Existing Jira Align DB user account Note: This SQL or Windows user account will require, at minimum db_datareader access to the existing [JA_DB_NAME] database. |
[JA_PASSWORD] | JiraAlign! | Existing Jira Align DB user account password |
That table is a page of the file downloaded with the EI Self-Hosted package called "Jira Align Self-Hosted Enterprise Intelligence Guide".
The user account that creates the EI ETL Job is [ETL_USERNAME], and it must be a SystemAdmin, and also this Windows user will be required to Modify access to the BI ETL folder and will need to be assigned to the "db_ssisadmin” fixed database-level role or optionally, assigned sysadmin for instance.
Solution
Before running the EI installation script, the user has to make sure that the user's account has the right privileges and access to the SQL Server and Windows services.
Regarding the error "18452 - The login is from an untrusted domain and cannot be used with Integrated authentication", the user has to go to SQL Server > Security > Logins and:
Check if the Windows user is added to log into the SQL Server and the instance of EI.
Suppose the server role is sysadmin. For that, the user can right-click on the login user and go to the "Server Roles” tab.
On the “User Mapping” tab, the user account has to be checked for the Jira Align tables, EI table, and SSISDB table.
Note that the table of user accounts explains which role each user has to be. For the EI table, it is db_owner.
After checking the user's account, we can follow the script, and when running the script, the user must be sure to use a Windows account inserted into the System Admin group. This Windows user must be in the same Domain as the SQL Server server.
To make sure of it:
Open the PowerShell as another user, and for this:
Press shift, Right-click on the PowerShell file, and click on “Run as a different user.”
Make sure that the Windows user is inserted in the same Domain of the servers, application, and database.
Depending on the structure of the Domain Group of the user, a Local Admin User of the database server can be used to run the installation script
Was this helpful?