%3CLINGO-SUB%20id%3D%22lingo-sub-369168%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2363%3A%20It%20is%20possible%20to%20create%20Linked%20Server%20in%20Azure%20SQL%20Managed%20Instance%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369168%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2019%2C%202019%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20The%20answer%20is%20Yes!!!%20and%20to%20create%20it%20you%26nbsp%3Bonly%20need%20to%20follow%20up%20the%20instructions%20provided%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Flinked-servers%2Fcreate-linked-servers-sql-server-database-engine%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20URL%20%3C%2FA%3E%20Unfortunately%2C%20nowadays%2C%26nbsp%3Byou%26nbsp%3Bonly%20are%20able%20to%20use%20the%20SQL%20provider%26nbsp%3Bto%26nbsp%3Bconnect%20to%20Azure%20SQL%20Database%2C%20SQL%20Server%26nbsp%3Bor%20Azure%20SQL%20Managed%20Instance.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20My%26nbsp%3Blessons%20learned%20in%20this%20situation%20is%20you%20need%20to%20pay%20attention%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20For%20Azure%20SQL%20Database%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EPlease%20review%20this%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-develop-direct-route-ports-adonet-v12%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20URL%20%3C%2FA%3E%20to%20open%20the%20outbound%20ports%201433%2C11000-11999%20and%26nbsp%3B14000-14999%26nbsp%3Bto%20connect%20to%20Azure%20SQL%20Database%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ERemember%20to%20use%20in%20the%20user%20name%40server%20in%20the%20user%20name%20format.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EIf%20you%20want%20to%20reduce%20the%20surface%20security%20impact%20and%20only%20open%20the%20port%201433%20as%20outbound%2C%20please%2C%20review%20this%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connectivity-architecture%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20URL%20%3C%2FA%3E%20to%20disable%20the%20re-direction%20process.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EFinally%2C%20remember%20that%20the%20IP%20source%26nbsp%3Bof%20the%20Azure%20SQL%20Managed%20Instance%20will%20be%20the%20IP%20of%26nbsp%3Bthe%20external%20load%20balancer%26nbsp%3Band%26nbsp%3Byou%20need%20to%20open%20in%20your%20Azure%20SQL%20Database%20firewall%20-%20Review%20this%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-managed-instance-connectivity-architecture%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20URL.%20%3C%2FA%3E%20Also%2C%20there%20is%20not%20possible%20nowadays%20to%20add%20the%20service%20endpoint%20of%20our%20Azure%20SQL%20Database.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EIf%20you%20are%20connecting%20to%20%3CSTRONG%3E%20SQL%20Server%26nbsp%3Bor%20to%20another%26nbsp%3BAzure%20SQL%20Managed%20Instance%20%3C%2FSTRONG%3E%20you%20need%20the%20port%201433.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20Enjoy!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369168%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2019%2C%202019%20The%20answer%20is%20Yes!!!%20and%20to%20create%20it%20you%26nbsp%3Bonly%20need%20to%20follow%20up%20the%20instructions%20provided%20in%20this%20URL%26nbsp%3B%26nbsp%3B%20Unfortunately%2C%20nowadays%2C%26nbsp%3Byou%26nbsp%3Bonly%20are%20able%20to%20use%20the%20SQL%20provider%26nbsp%3Bto%26nbsp%3Bconnect%20to%20Azure%20SQL%20Database%2C%20SQL%20Server%26nbsp%3Bor%20Azure%20SQL%20Managed%20Instance.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369168%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELinked%20Server%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Emanaged%20instance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esql%20server%20on%20premise%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1126332%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2363%3A%20It%20is%20possible%20to%20create%20Linked%20Server%20in%20Azure%20SQL%20Managed%20Instance%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1126332%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20been%20searching%20for%20some%20instructions%20regarding%20creation%20of%20linked%20server%20on%20managed%20instance%20on%20Azure%20to%20SQL%20server%20on%20premise.%20For%20some%20reason%20there%20are%20plenty%20of%20examples%20on%20creating%20linked%20server%20on-prem%20to%20Managed%20Instance.%20I%20tried%20different%20providers%20and%20get%20the%20error%20that%20server%20can%20not%20be%20found.%20I%20wonder%20if%20it%20has%20something%20to%20do%20with%20security.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1468855%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2363%3A%20It%20is%20possible%20to%20create%20Linked%20Server%20in%20Azure%20SQL%20Managed%20Instance%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1468855%22%20slang%3D%22en-US%22%3E%3CP%3ELinked%20Servers%20on%20Microsoft%20platforms%20utilize%20the%20Tabular%20Data%20Stream%20(TDS)%20protocol%2C%20which%20is%20commonly%20blocked%20by%20hosted%2Fmanaged%20providers.%20We%20had%20this%20initial%20issue%20as%20well%20on%20the%20Amazon%20AWS%20SQL%20RDS%20platform%20that%20was%20affecting%20several%20of%20our%20in-house%20applications%20from%20migrating%20over%20to%20AWS.%20So%20after%20a%20few%20months%20of%20research%20and%20several%20proof%20of%20concepts%20later%2C%20I%20was%20successful%20on%20implementation%20of%20a%20solution%20using%20a%203rd%20party%20app%20(along%20with%20a%20unique%20configuration%20I%20designed).%20Then%20after%20finding%20posts%20like%20this%2C%20I%20found%20that%20my%20solution%20can%20be%20applied%20to%20Azure%20managed%20instances%20as%20well%20to%20connect%20to%20on-premise%20hosts.%20I%20would%20test%20it%20on%20Google%20Cloud%20as%20well%2C%20but%20found%20they%20don't%20support%20SQL%20Server...%20yet%2C%20but%20I%20am%20betting%20the%20solution%20will%20work%20there%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E
First published on MSDN on Jan 19, 2019
The answer is Yes!!! and to create it you only need to follow up the instructions provided in this URL Unfortunately, nowadays, you only are able to use the SQL provider to connect to Azure SQL Database, SQL Server or Azure SQL Managed Instance.

My lessons learned in this situation is you need to pay attention

  • For Azure SQL Database:

    • Please review this URL to open the outbound ports 1433,11000-11999 and 14000-14999 to connect to Azure SQL Database

    • Remember to use in the user name@server in the user name format.

    • If you want to reduce the surface security impact and only open the port 1433 as outbound, please, review this URL to disable the re-direction process.

    • Finally, remember that the IP source of the Azure SQL Managed Instance will be the IP of the external load balancer and you need to open in your Azure SQL Database firewall - Review this URL. Also, there is not possible nowadays to add the service endpoint of our Azure SQL Database.



  • If you are connecting to SQL Server or to another Azure SQL Managed Instance you need the port 1433.


Enjoy!
2 Comments
Occasional Visitor

Hi, I have been searching for some instructions regarding creation of linked server on managed instance on Azure to SQL server on premise. For some reason there are plenty of examples on creating linked server on-prem to Managed Instance. I tried different providers and get the error that server can not be found. I wonder if it has something to do with security.

Occasional Visitor

Linked Servers on Microsoft platforms utilize the Tabular Data Stream (TDS) protocol, which is commonly blocked by hosted/managed providers. We had this initial issue as well on the Amazon AWS SQL RDS platform that was affecting several of our in-house applications from migrating over to AWS. So after a few months of research and several proof of concepts later, I was successful on implementation of a solution using a 3rd party app (along with a unique configuration I designed). Then after finding posts like this, I found that my solution can be applied to Azure managed instances as well to connect to on-premise hosts. I would test it on Google Cloud as well, but found they don't support SQL Server... yet, but I am betting the solution will work there as well.