Question posted in 
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 following authentication error.
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.
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?
I should also mention that I have tested logging in with the elastic job user (mapped from login) to the databases where the jobs are failing (using SQL Authentication) and can do so successfully, so it seems more like an issue with the agent server logging in to the target database.
I have checked that the SID’s for the logical instance level login and mapped target database user are the same and they are.
I have checked the database level permissions of the user. It has sufficient privileges (db_owner).
I have tried re-mapping the login and user.
The target group for the job is an elastic pool, so the new database (where the jobs are failing) has been added as a target group member as a result of adding it to the elastic pool which is associated with the target group (not added individually). This is how we onboard all new databases in to the target scope of elastic jobs.
2
Answers
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 you are not using Database scoped credential authentication option, then there will be only one option except it, that is Authentication via user-assigned managed identity (UMI). According to the MS document the user-assigned managed identity(UMI) was associated with the elastic job agent, Ensure that UMI is having access to the newly created database. Otherwise, you will get above error. If you want to use connect to the newly created database to run jobs with UMI authentication, create user of UMI in the database using below query:
CREATE USER [UMI] FROM EXTERNAL PROVIDER;Otherwise, un-associate the user-assigned managed identity from the elastic agent. It will resolve the error, and the job will run successfully. For more information you can refer to the similar post in MS Q&A portal.