0

In a SQL Server Managed Instance I have 2 databases (for security reasons both databases have different logins). I need the possibility to allow one database to look into the other one. In a local SQL Server I was able to create a Linked Server to realize this. But this seems not to work using the Managed Instance. Can someone give some hints how to achieve this?

Steffen Böttcher
  • 1
  • 1

1 Answers1

0

Managed Instance supports linked servers (unless if they use MSDTC for some distributed writes). Make sure that you add logins for remote server:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PEER',@useself=N'False',@locallogin=NULL,
@rmtuser=N'$(linkedServerUsername)', @rmtpassword='$(linkedServerPassword)';

If it still doesn't work put the exact error message. This might be Network security Group blocking the port, VNets that are not peered, etc.

np_6
  • 514
  • 1
  • 6
  • 19
Jovan MSFT
  • 13,232
  • 4
  • 40
  • 55
  • Thanks a lot, now I got it. But additionally I had to add the name of the target user in the `sp_addlinkedserver` procedure:`EXEC master.dbo.sp_addlinkedserver @server = N'', @srvproduct=N'', @provider=N'SQLNCLI11', @provstr=N'Server=;Initial Catalog=master;Persist Security Info=False;MultipleActiveResultSets=FalseEncrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;User ID='` – Steffen Böttcher Apr 24 '19 at 12:14