0

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/

0 Answers0