Issue running job using Elastic Job Agent in dedicated SQL Pool (User Managed Identity)

Kshitij Gurung 0

I have an Azure SQL Server with a dedicated SQL Pool (probably prior to 2020) and a reporting database. I am trying to run elastic job agent using User Managed Identities (UMI) on all database in this Azure SQL Server. I have set up all the logins, users, permission on both the job database and target server/databases (Azure SQL Server/dedicated SQL Pool/reporting database) as per Microsoft documentation. When I run the elastic job, it runs just fine in reporting database, but it fails on the dedicated SQL Pool due saying, " Failed to connect to the target database: Login failed for user '<token-identified principal>;.error <token-identify-problem). I can't seem to figure out why. is UMI even compatible Elastic Job dedicates SQL Pool?

Mahesh Kurva 5 Reputation points Microsoft Vendor

2024-09-12T16:53:13.2833333+00:00
Hi @Kshitij Gurung,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Yes, User Managed Identities (UMI) are compatible with Elastic Job Agent in Azure SQL Database, including dedicated SQL pools. However, it is possible that there is an issue with the configuration of the UMI or the permissions on the dedicated SQL pool.

To troubleshoot the issue, you can try the following steps:

Check that the UMI has been correctly configured and has the necessary permissions to access the dedicated SQL pool.

Verify that the connection string used by the Elastic Job Agent to connect to the dedicated SQL pool is correct and includes the UMI token.

Make sure that the UMI token has not expired.

Check the firewall settings for the dedicated SQL pool and ensure that the Elastic Job Agent IP address is allowed to access the dedicated SQL pool.

Check the error logs for the dedicated SQL pool to see if there are any login failures or other issues that could be causing the problem.

For more information, please refer the document: Create, configure, and manage elastic jobs.

I hope this information helps. Please do let us know if you have any further queries.
Kshitij Gurung 0 Reputation points

2024-09-12T18:32:19.3333333+00:00
Azure SQL Servers has two database (1 - Reporting, 2 - dedicated SQL Pool: standalone). I am using job-agent-UMI login in the Azure SQL Server, which is then used to create agentjobuser database users (GRANT Connect, db_reader/write perms) in both Reporting DB and SQL Pool. job-agent-UMI has contributor access to the Azure SQL Server and the resource group*. job-agent-UMI* is used to create the Elastic Job agent, elastic jobs, and private endpoint. When running the elastic job, it works just fine for the Reporting DB but throws ,"Failed to connect to the target database: Login failed for user '<token-identified principal>', although both are in the same Azure SQL Server, which I don't understand. Job is simply reading some metrics such as database size and it already has db_reader_writer permission.

If I am using the same UMI for both databases and SQL Server, I believe the UMI should contain the token and is not expired since it's working for Reporting DB?

I have set up the private to the SQL Server, and the Allow Azure services is checked too.

All the error Iogs I can find is the Login failed for user '<token-identified principal> in the job_executions table of job database. I can't find logs in dedicated SQL Pool.

What specific permission do I need to grant the UMI besides the one I have mentioned?
Kshitij Gurung 0 Reputation points

2024-09-12T18:34:39.1366667+00:00

comment testing
Mahesh Kurva 5 Reputation points Microsoft Vendor

2024-09-13T05:28:48.5433333+00:00
Hi @Kshitij Gurung,

Check if the firewall rules for the dedicated SQL Pool are configured correctly. Make sure that the IP address of the machine running the elastic job is added to the firewall rules of the dedicated SQL Pool.

Check if the private endpoint for the dedicated SQL Pool is configured correctly. Make sure that the private endpoint is connected to the correct subnet and virtual network, and that the DNS resolution is working correctly.

For more information, please refer the document: Login failed for user '<token-identified principal>'

I hope this information helps. Please do let us know if you have any further queries.
Mahesh Kurva 5 Reputation points Microsoft Vendor

2024-09-16T10:47:03.87+00:00

Hi @Kshitij Gurung,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Kshitij Gurung 0 Reputation points

2024-09-16T21:20:42.5233333+00:00
dedicated SQL Pool has no firewall rules but the Azure SQL Server it is ties to has firewall rules. I have configured Private access to the Elastic Job Agent. The elastic job works just fine for the Reporting DB, which using the Azure SQL Server, same private access, logins, users (UMI) but not for the dedicated SQL Pool.

I checked the SID and Object ID in both reporting DB and dedicated SQL Pool, and they were exact match. permission is also exact replica.
Oury Ba-MSFT 18,696 Reputation points Microsoft Employee

2024-09-18T19:53:47.2433333+00:00

@Kshitij Gurung You mentioned that dedicated sql pool has no firewall rule. Could you please check if the firewall rule will show under public tab.

Regards,

Oury
Kshitij Gurung 0 Reputation points

2024-09-19T04:17:14.4133333+00:00

@Oury Ba-MSFT , The networking screenshot above (private access) is for the Azure SQL Server , which has one Azure SQL DB (Reporting DB) and one dedicated SQL Pool. There are two firewall rules under public tab, which is intentionally locked down. If your follow up comment is going to be a suggestion to add the IP of the Elastic Agent job then I do not know how to get that IP for the Elastic Agent. I followed the Microsoft documentation (linked below) to create the private end point to link the Azure SQL and Elastic jobs. It is running smoothly for the Azure SQL DB (Reporting DB) but getting the 'Failed to connect to the target database: Login failed for user '<token-identified principal>' for dedicated SQL POOL, which I don't understand. Both are linked to the same Azure SQL Server, where I set up the private endpoint shown in the Networking screenshot above.

'Configure Azure SQL elastic jobs private endpoint: Creating an elastic jobs private endpoint establishes a private link between the elastic job and the target server. Once configured, all communication between the elastic job agent and the target server will occur through the private endpoint

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tutorial?view=azuresql.'

This is the screenshot of the Dedicated SQL Pool, which is probably using the Networking setup of the Azure SQL Server as I don't see Networking tab.

Share via

Issue running job using Elastic Job Agent in dedicated SQL Pool (User Managed Identity)

Your answer