How to create linked servers from SQL managed instance using Entra Authentication (part 1)

luisaranda 

Thank you - I believe I have enabled "system managed Identity"

I now understand to use "name of my managed instance" - my step #3

I still have issue - test connection fails

My steps

#1.  turned on System assigned managed identity

#2.  verify my SQL MI has a configured - Entra Admin >> it does

#3. on SQL Azure - create user with same name as SQL server managed instance

Done - user shows in SSMS under Azure database>security>Users

CREATE USER [mxxx-xxx-xxx] FROM EXTERNAL PROVIDER;

#4. Create Linked server on local SQL server - pointing to my server and database, using Authentication=ActiveDirectoryMSI

EXEC master.dbo.sp_addlinkedserver
@server = N'AzureDev_MSILinkedServer',
@srvproduct = N'',
@Provider = N'MSOLEDBSQL',
@provstr = N'Server=mxxx-xxx-xxx.dataxxxx.winxxxx.nxx,1433;Database=xxx_xxxx;Authentication=ActiveDirectoryMSI;';

#5. create login to linked server

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'S2',
@useself = N'False',
@locallogin = NULL;

#6. Test - Get error below - why is the user blank / '' ?

Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryMSI').
Error code 0xD; state 9