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

Kshitij Gurung 20

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 245 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 20 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 20 Reputation points

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

comment testing
Mahesh Kurva 245 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 245 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 20 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 19,346 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 20 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.
Mahesh Kurva 245 Reputation points Microsoft Vendor

2024-09-20T10:10:05.4766667+00:00
Hi @Kshitij Gurung,
Regarding the question:

Even though the dedicated SQL Pool doesn't have its own firewall rules, it's possible that there are additional access controls or security policies in place that are blocking the Elastic Job Agent's connection. Check the dedicated SQL Pool's settings to see if there are any specific access controls or security policies that might be causing the issue.

If a UMI is assigned to the Job Agent, SQL Authentication will not be used to connect to its targets. Job Agent will only use Microsoft Entra authentication with the UMI to connect to all of its target databases.
Mahesh Kurva 245 Reputation points Microsoft Vendor

2024-09-23T17:48:05.75+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 20 Reputation points

2024-09-25T15:11:17.2166667+00:00
@Anonymous

That was one of my questions. Do you know what are the MUST HAVE Access Controls and Security Policies that we need to make it work? I couldn't find it online. I feel like I might be missing a small permission somewhere. I have listed the permissions that I have granted above.

I am UMI, which is probably using Microsoft Entra authentication to connect to its target databases. I am certainly not using SQL Authentication. Then fact that it is able to successfully find target databases in Azure SQL Database (Reporting DB) and NOT is dedicated SQL Pool is throwing me off, if that makes sense.
Kshitij Gurung 20 Reputation points

2024-09-26T14:35:03.5566667+00:00
@Mahesh Kurva

That was one of my questions. Do you know what are the MUST HAVE Access Controls and Security Policies that we need to make it work? I couldn't find it online. I feel like I might be missing a small permission somewhere. I have listed the permissions that I have granted above.

I am UMI, which is probably using Microsoft Entra authentication to connect to its target databases. I am certainly not using SQL Authentication. Then fact that it is able to successfully find target databases in Azure SQL Database (Reporting DB) and NOT is dedicated SQL Pool is throwing me off, if that makes sense.

Accepted answer

Oury Ba-MSFT 19,346 Reputation points Microsoft Employee

2024-09-27T17:53:31.13+00:00

@Kshitij Gurung

Elastic job works with elastic pools and UMI also works for connecting to target server, enumerating list of DBs in a pool and executing jobs there. Make sure that you have created a user from UMI in master database and each of the target databases including databases in the pool with appropriate permissions to run the job there.

-- If target is a server/pool, create user from [EXTERNAL PROVIDER]

In the target server's master database

DROP USER IF EXISTS [jobuserUMI] GO

-- Create user from the User managed identity

CREATE USER [jobuserUMI] FROM EXTERNAL PROVIDER;

GO

Regards,

Oury
Please sign in to rate this answer.
Kshitij Gurung 20 Reputation points

2024-09-27T18:06:57.95+00:00

@Oury Ba-MSFT

As mentioned in my first post, I have already created the necessary user (with enough permission to run the job) in master db, dedicated pool and SQL Databases using the UMI login as documented in Microsoft documentation.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Oury Ba-MSFT 19,346 Reputation points Microsoft Employee

2024-10-02T21:42:02.0433333+00:00

@Kshitij Gurung

I was able to check this internally and seems like Elastic jobs are not supported on Azure Synapse. This was the reason why you are getting the error message with dedicated sql pool and not on Azure sql db.Regards,

Oury

Kshitij Gurung 20 Reputation points

2024-10-08T19:17:43.0466667+00:00

@Oury Ba-MSFT

That is what I was afraid of and it's a bummer that there aren't any official documentations mentioning the incompatibility. Would have been helpful to know it before than starting the work towards the project. Is it possible to send me the link to the documentations if you have it? What other work-around alternatives would you suggest apart from Azure Data Factory?

Oury Ba-MSFT 19,346 Reputation points Microsoft Employee

2024-10-09T19:37:36.0966667+00:00

@Kshitij Gurung

You can set up an Azure Function activity in ADF - but it is going to call the AzureFunction to cause the AzureFunction to execute.

You can't create a function in ADF but you can invoke it using Azure Function activity. You will need to create an Azure function using the portal and then you can call that function from the ADF.

Azure Function Activity - Azure Data Factory & Azure Synapse | Microsoft Learn

I have not tested that yet.

Regards,

Oury

Oury Ba-MSFT 19,346 Reputation points Microsoft Employee

2024-10-10T22:02:41.7066667+00:00

@Kshitij Gurung I wanted to check if the above workaround was helpful.

Regards,

Oury
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

0 additional answers

Your answer