Up until now, Managed Instance supported only SQL authentication for Linked Servers, and if you wanted to use modern authentication methods that don’t require handling secrets, that was not possible. With this release you can now use two Azure AD authentication methods: Pass-through and Managed Identity. Both methods enable you to create Linked Servers without the need to manage secrets in Managed Instance.
Pass-through authentication
If AAD principal is mapped to a SQL login on two Managed Instances, local and remote one, Pass-though authentication method will allow the principal authenticated on the local server to use linked server to access remote Managed Instance. There will be no need to authenticate again, as the security context obtained with the initial authentication on the local instance will be transferred to the remote instance. With this, number of logins your users need to perform is reduced, while security remains at the highest level.
Requirement for Pass-through authentication is that all instances belong to the Server trust group. Group can be created on Azure Portal or with Azure PowerShell or CLI. In this blog you can find examples of how New-AzSqlServerTrustGroup and Set-AzSqlServerTrustGroup can be used to manage Server trust group.
Finally, you can create Linked Server that uses AAD Pass-through authentication with following T-SQL code (more details available in the documentation).
EXEC master.dbo.sp_addlinkedserver
@server = N'linkedServerAADPassThroughAuth',
@srvproduct = N'',
@provider = N'MSOLEDBSQL',
@datasrc=N'sqlminame.8b522d9b1fc0.database.windows.net,1433'
Managed Identity authentication
Another available AAD authentication method for Managed Instance Linked Servers is Managed Identity authentication. Managed Instance supports service or user assigned managed identities. To use this authentication method, Managed Identity (or in case there are multiple managed identities, then Primary identity) of your local Managed Instance should be added as login on the remote Managed Instance. With the T-SQL you can create Linked Server object and mark its @provstr parameter with “Authentication=ActiveDirectoryMSI” and then add linked server logins which should be able to use the Linked Server. Here is an example of this is done.
EXEC master.dbo.sp_addlinkedserver
@server = N'MyLinkedServer',
@srvproduct = N'',
@provider = N'MSOLEDBSQL',
@provstr = N'Server=sqlminame.8b522d9b1fc0.database.windows.net,1433;Authentication=ActiveDirectoryMSI;'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MyLinkedServer',
@useself = N'False',
@locallogin = N'user1@domain1.com'; -- Use NULL to allow all logins.
You can learn more about the syntax in the documentation.
Going forward
This is just one of the improvements we have released recently to enable you to make your Managed Instance passwordless and more secure. More is coming, so track our latest updates on our AzureSQL TechComunity Blog!
