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
- Remove security on the Linked Server and let every login use it. This is unacceptable from a security stand point.
- 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.