Could not connect to {database}. Authentication configuration needed. User managed identity is recommended for target and output database authentication.

Question

Could not connect to {database}. Authentication configuration needed. User managed identity is recommended for target and output database authentication.

Ayush Shrivastava 120

This is the error I am getting by

SELECT last_message

FROM jobs.job_executions

I am using SPN to authenticate the target database the SPN has all the appropriate access.

Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2024-09-11T17:02:31.58+00:00

@Ayush Shrivastava Thank you for reaching out.

Please see the public doc that explains how to configure UMI for elastic jobs: Elastic jobs overview - Azure SQL Database | Microsoft Learn

Please check the above doc to see if you are not missing a prerequisite.

DO let us know if it is helpful.

Regards,

Oury
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2024-09-16T20:58:38.8166667+00:00

@Ayush Shrivastava

Could you please share the status of the issue. Are you still facing issues?

Regards,

Oury

1 answer

Your answer

Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2024-09-11T17:02:31.58+00:00

@Ayush Shrivastava Thank you for reaching out.

Please see the public doc that explains how to configure UMI for elastic jobs: Elastic jobs overview - Azure SQL Database | Microsoft Learn

Please check the above doc to see if you are not missing a prerequisite.

DO let us know if it is helpful.

Regards,

Oury
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2024-09-16T20:58:38.8166667+00:00

@Ayush Shrivastava

Could you please share the status of the issue. Are you still facing issues?

Regards,

Oury

Answer 1

Darshida Das 15

Try using a User Managed Identity instead of the Service Principal Name (SPN) for authentication. Ensure it has the correct permissions and update your connection string accordingly

Ayush Shrivastava 120 Reputation points

2024-09-11T13:26:28.3433333+00:00
I don't have a connection string because I am using Elastic jobs.

Also when I replace it with managed identity it throws this error.

I created it using

CREATE WITH

Fail to obtain or decrypt secret for credential 'spn_creds'. (Msg 33047, Level 16, State 5, Line 1)
Ayush Shrivastava 120 Reputation points

2024-09-11T13:28:10.56+00:00

CREATE DATABASE SCOPED CREDENTIAL spn_creds

WITH IDENTITY = 'Managed Identity' ;
Darshida Das 15 Reputation points

2024-09-11T13:49:16.4+00:00
Here’s what you need to do to resolve the authentication issue when switching to Managed Identity in Elastic Jobs:

Enable Managed Identity:

Ensure Managed Identity is enabled for the Elastic Job agent or the service executing the job.

Grant Permissions to Managed Identity:

Assign the appropriate role to the Managed Identity for your target database.

Use this command to assign roles:

CREATE USER [<Managed Identity Name>] FROM EXTERNAL PROVIDER;

ALTER ROLE db_owner ADD MEMBER [<Managed Identity Name>];

Create a Credential Using Managed Identity:

Use the following command to create a credential for the Managed Identity in Elastic Jobs:

CREATE DATABASE SCOPED CREDENTIAL [ManagedIdentity]

WITH IDENTITY = 'Managed Identity';

4.Update your job to use this credential for database authentication
Ayush Shrivastava 120 Reputation points

2024-09-11T13:51:47.18+00:00

Will try this and let you know.
Ayush Shrivastava 120 Reputation points

2024-09-11T14:32:51.6733333+00:00

This doesn't work.

Fail to obtain or decrypt secret for credential 'ManagedIdentity'. (Msg 33047, Level 16, State 5, Line 1)
Darshida Das 15 Reputation points

2024-09-11T15:06:56.2533333+00:00
Check Managed Identity

Grant Permissions:

Update Configuration:

Share via

Could not connect to {database}. Authentication configuration needed. User managed identity is recommended for target and output database authentication.

1 answer

Your answer