November 19, 2024 at 5:01 pm
Azure elastic agent jobs: I’m getting this error “The server principal “ac1971e9-381b-449b-9e1a-9cc276fc2985@b5313c9d-fb0d-47af-bc87-7c050bffbdc3” is not able to access the database” when an elastic agent job tries to run against a new database that has been added to an elastic pool.
The elastic pool is configured as a target group member. For all other databases in the pool (that have existed for some time), the job executes successfully.
The job is authenticating against the databases within the elastic pool using a database level user which is mapped to the same (Azure SQL database) server level login. Each database user has been assigned ‘db_owner’ so has sufficient permissions. I have also checked the SID for the login and users match (which they do). Note, these ARE NOT database scoped credentials as they are mapped to Azure SQL databases level login. I can’t understand why the jobs execute for all the other databases in the same target member (elastic pool), with the same credentials, but for the database that has been newly added to the pool, for some reason the job is trying to authenticate with the server principle (ac1971e9-381b-449b-9e1a-9cc276fc2985@b5313c9d-fb0d-47af-bc87-7c050bffbdc3).
I’ve checked through this thoroughly querying the following views/tables etc…
sys.database_principals
sys.sql_logins
sys.database_scoped_credentials (non-exist as expected)
Everything matches in the databases where job jobs run versus the new database (in the same elastic pool) where the job errors as it is trying to authenticate with a server principle.
I have also tried re-mapping the database level users to the logical instance level login but this doesn’t make a difference.
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, suggesting that the permissions assigned the to the login/users have ben configured correctly.
Also, please note that this relates to Azure SQL databases and Azure elastic jobs (not SQL Server and SQL Agent).
November 20, 2024 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply