Azure MI SQL Agent cannot use Linked Server

Question

BACKGROUND

• I have created a Linked Server on an Azure Managed Instance and secured it to specific logins only.
• I have a SQL Agent job that wants to use the Linked Server.

QUESTION How do I grant the Azure MI SQL Agent execution account the permission to use the Linked Server?

RESULTS In on premises SQL Server I would add the service account I assigned to SQL Agent as a linked server login using proc sp_addlinkedsrvlogin. However, my SQL Agent service account appears to be [User Manager\ContainerAdministrator] (REF 1). If I try adding that login using the above proc I get the error 'User Manager\ContainerAdministrator' is not a valid login or you do not have permission.'.

When my SQL Agent jobs tries to use the linked server I get this error as expected : Executed as user: User Manager\ContainerAdministrator. Access to the remote server is denied because no login-mapping exists.

WORK AROUNDS

1. Remove security on the Linked Server and let every login use it. This is unacceptable from a security stand point.
2. Move my SQL Agent job off the Managed Instance onto an regular installation of SQL Server where I can grant the SQL Agent execution account permission to use the linked server. Refactor the job to write the results back to the Managed Instance using a second linked server. This is what I think I need to do but it is disappointing as our research on Azure Managed Instance indicated that SQL Agent and Linked servers were supported, just not at the same time apparently.

REF 1 : https://johnmccormack.it/2020/09/how-do-i-find-the-agent-service-account-for-azure-sql-database-managed-instance/

Answer 1

Have you considered configuring the SQL Agent Job step in question to run as SQL login for which you already configured appropriate permissions on the Linked Server?

Answer 2

Had a similiar issue but not using "Linked Servers". On two out of three of our SQL Managed Instances the SQL Jobs randomly started executing the SQL Jobs as: Executed as user: User Manager\ContainerAdministrator. I have SQL jobs that when they execute EXEC msdb.dbo.sp_send_dbmail it would fail with the following error: Message Executed as user: User Manager\ContainerAdministrator. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed. No clue again why it is executing as this year (not even anythign setup for it). If I add this line of code and create a Login for it, it works:
execute as login = 'SQLAgentUser'