Lesson Learned #92: Connecting to SQL Server - IaaS from Azure SQL Managed Instance

Published 06-24-2019 10:54 AM 784 Views

Today, I worked on a service request with the the following scenario:

 

  • SQL Server running on a Virtual Machine on Azure without public IP, just only, is accesible from the Virtual Network.
  • Azure SQL Managed Instance needs to connect using Linked Server to this SQL Server. It is possible to connect using the IP but not the name.

 

And I found during my troubleshooting process:

 

  • I created a virtual machine in the virtual network where is running Azure SQL Managed Instance and I didn’t specify public IP.
  • Running Ipconfig /All I saw that the DNS suffix is: When you are using Azure-provided name resolution, Azure Dynamic Host Configuration Protocol (DHCP) provides an internal DNS suffix (.internal.cloudapp.net) to each VM. This suffix enables host name resolution because the host name records are in the internal.cloudapp.net zone
  • For this reason, trying to connect for any machine of this virtual network was totally possible just only defining the name because the DNS is internal.cloudapp.net.
  • However, the DNS registration of Azure SQL Managed Instance is different. You could see the information here.
  • In this situation, using a virtual machine of this virtual network, I installed and configured a DNS , I added the internal IP of this DNS and also the IP:168.63.129.16 of Azure DNS, as you could see in this URL.
  • After restarting all the virtual machines and scale up and down the Azure SQL Managed Instance, I was able to connect using the name of the server and the domain of DNS. In fact, sometimes we need to create an internal ticket to our Azure Product Team to restart the physical machine to register the new DNS. Or perhaps, it is a good idea have the DNS configured before creating a new Azure SQL Managed Instance.

 

Enjoy!

 

%3CLINGO-SUB%20id%3D%22lingo-sub-717821%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2392%3A%20Connecting%20to%20SQL%20Server%20-%20IaaS%20from%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-717821%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20with%26nbsp%3Bthe%26nbsp%3Bthe%20following%20scenario%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESQL%20Server%20running%20on%20a%20Virtual%20Machine%20on%20Azure%20without%20public%20IP%2C%20just%20only%2C%20is%20accesible%20from%20the%20Virtual%20Network.%3C%2FLI%3E%0A%3CLI%3EAzure%20SQL%20Managed%20Instance%20needs%20to%20connect%20using%20Linked%20Server%20to%20this%20SQL%20Server.%20It%20is%20possible%20to%20connect%20using%20the%20IP%20but%20not%20the%20name.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20I%20found%20during%20my%20troubleshooting%20process%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EI%20created%20a%20virtual%20machine%20in%20the%20virtual%20network%20where%20is%20running%20Azure%20SQL%20Managed%20Instance%20and%20I%20didn%E2%80%99t%20specify%20public%20IP.%3C%2FLI%3E%0A%3CLI%3ERunning%20Ipconfig%20%2FAll%20I%20saw%20that%20the%20DNS%20suffix%20is%3A%20When%20you%20are%20using%20Azure-provided%20name%20resolution%2C%20Azure%20Dynamic%20Host%20Configuration%20Protocol%20(DHCP)%20provides%20an%20internal%20DNS%20suffix%20(.internal.cloudapp.net)%20to%20each%20VM.%20This%20suffix%20enables%20host%20name%20resolution%20because%20the%20host%20name%20records%20are%20in%20the%20internal.cloudapp.net%20zone%3C%2FLI%3E%0A%3CLI%3EFor%20this%20reason%2C%20trying%20to%20connect%20for%20any%20machine%20of%20this%20virtual%20network%20was%20totally%20possible%20just%20only%20defining%20the%20name%20because%20the%20DNS%20is%20internal.cloudapp.net.%3C%2FLI%3E%0A%3CLI%3EHowever%2C%20the%20DNS%20registration%20of%20Azure%20SQL%20Managed%20Instance%20is%20different.%20You%20could%20see%20the%20information%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fvirtual-network%2Fvirtual-networks-name-resolution-for-vms-and-role-instances%2520%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%3EIn%20this%20situation%2C%20using%20a%20virtual%20machine%20of%20this%20virtual%20network%2C%20I%20installed%20and%20configured%20a%20DNS%20%2C%20I%20added%20the%20internal%20IP%20of%20this%20DNS%20and%20also%20the%20IP%3A168.63.129.16%20of%20Azure%20DNS%2C%20as%20you%20could%20see%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-managed-instance-custom-dns%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EURL.%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3EAfter%20restarting%20all%20the%20virtual%20machines%20and%20scale%20up%20and%20down%20the%20Azure%20SQL%20Managed%20Instance%2C%20I%20was%20able%20to%20connect%20using%20the%20name%20of%20the%20server%20and%20the%20domain%20of%20DNS.%20In%20fact%2C%20sometimes%20we%20need%20to%20create%20an%20internal%20ticket%20to%20our%20Azure%20Product%20Team%20to%20restart%20the%20physical%20machine%20to%20register%20the%20new%20DNS.%20Or%20perhaps%2C%20it%20is%20a%20good%20idea%20have%20the%20DNS%20configured%20before%20creating%20a%20new%20Azure%20SQL%20Managed%20Instance.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-717821%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20this%20article%20I%20would%20like%20to%20share%20with%20you%2C%20why%20is%20important%20to%20use%20a%20Custom%20DNS%20if%20you%20want%20to%20connect%20using%20Linked%20Server%26nbsp%3Bto%20any%20SQL%20Server%20running%20on%20a%20Virtual%20Machine%20and%20same%20Virtual%20Network%20that%20Azure%20SQL%20Managed%20Instance%20is%20running%2C%20specially%2C%20in%20this%20case%2C%20when%20the%20this%26nbsp%3Bvirtual%20machine%20has%20not%26nbsp%3Ba%20public%20IP.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Jun 24 2019 10:54 AM
Updated by: