Azure elastic agent jobs that work for existing databses are failing for newly created one's with "The server principal "ac1971e9-381b-449b-9e1a-9cc276fc2985@b5313c9d-fb0d-47af-bc87-7c050bffbdc3" is not able to access the database "

Question

Azure elastic agent jobs that work for existing databases are failing for newly created one's with "The server principal "ac1971e9-381b-449b-9e1a-9cc276fc2985@b5313c9d-fb0d-47af-bc87-7c050bffbdc3" is not able to access the database"

We have a number of elastic agent servers with jobs that target elastic pools. The jobs are still completing successfully for all databases that have existed for some time, but for new databases that are setup and added to the pool, we get the above authentication error.

The jobs are configured to connect to the databases with database level user accounts that are mapped from instance level logins. I've checked the assigned permissions on the users associated with the effected databases, and they are all configured correctly (completely in line with the other database's where the job are completing successfully). Could this be backend issue with Azure that is (for some reason) only effecting newly created databases that are added to target groups for elastic jobs?

Answer 1

Hi @Andrew Betts,

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Issue:

Azure elastic agent jobs that work for existing databases are failing for newly created one's with "The server principal "ac1971e9-381b-449b-9e1a-9cc276fc2985@b5313c9d-fb0d-47af-bc87-7c050bffbdc3" is not able to access the database"

We have a number of elastic agent servers with jobs that target elastic pools. The jobs are still completing successfully for all databases that have existed for some time, but for new databases that are setup and added to the pool, we get the above authentication error.

The jobs are configured to connect to the databases with database level user accounts that are mapped from instance level logins. I've checked the assigned permissions on the users associated with the effected databases, and they are all configured correctly (completely in line with the other database's where the job are completing successfully). Could this be backend issue with Azure that is (for some reason) only effecting newly created databases that are added to target groups for elastic jobs?

Solution:

I worked the issue out now. The user-assigned managed identity was associated with the elastic job agent, although no jobs had been setup to use it yet (all jobs for the target server/DB’s where still using DB users mapped from logins). All databases that existed prior to associating the user-assigned managed identity to the elastic agent server where unaffected, but jobs for databases created after this point would fail with the “The server principal "ac1971e9-381b-449b-9e1a-9cc276fc2985@b5313c9d-fb0d-47af-bc87-7c050bffbdc3" is not able to access the database”. Un-associating the user-assigned managed identity from the elastic agent server resolved the problem.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Make sure you have created on the new database the DATABASE SCOPED CREDENTIALs, EXTERNAL DATA SOURCEs, and CREATE EXTERNAL TABLEs needed by your elastic jobs.

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
 WITH IDENTITY = 'Username',
 SECRET = 'Password';  
 CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = RDBMS,
    LOCATION = '[servername].database.windows.net',
    DATABASE_NAME = 'Mydatabase',
    CREDENTIAL = ElasticDBQueryCred,
) ;
-- Make sure the location is correct please
CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NOT NULL,
  [Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)