Regardless of the connectivity mechanism, there are requirements that must be met for the network traffic to flow between the environments.

In this task, you’ll open ports in Windows Firewall on the SQL VM, then configure ports on the Network Security Group (NSG) attached to the Managed Instance.

Open ports on the Windows Firewall

1. On the Azure SQL VM, SQLVM1, select the Windows start menu, then enter and select Windows Powershell.

2. Enter the following to open the inbound and outbound ports needed, by creating new firewall rules:

   ``` # Inbound rule: Allow TCP port 5022 (any source IP) New-NetFirewallRule -DisplayName “Allow TCP port 5022 inbound” -Direction Inbound -Profile Any -Action Allow -LocalPort 5022 -Protocol TCP

   # Outbound rule: Allow TCP port 5022 (any destination IP) New-NetFirewallRule -DisplayName “Allow TCP port 5022 outbound” -Direction Outbound -Profile Any -Action Allow -LocalPort 5022 -Protocol TCP

   # Outbound rule: Allow TCP port range 11000-11999 (any destination IP) New-NetFirewallRule -DisplayName “Allow TCP ports 11000-11999 outbound” -Direction Outbound -Profile Any -Action Allow -LocalPort 11000-11999 -Protocol TCP ```

   

3. Select the minimize button on the VM’s top control bar.

   

Open ports on the Managed Instance NSG

You’ll now set up the inbound and outbound security rules for the network security group attached to the Managed Instance.

1. On the tab for SQLVM1 in Azure, select the ResourceGroup1 breadcrumb link in the upper left.

   

2. Select the Managed Instance network security group, the NSG name attached to your MI (e.g., SQLMI-myName-NSG).

   

3. On the left service menu, select Settings, then select Inbound security rules.

   

4. Select Add at the top.

   

5. Use the following information on the Add inbound security rule pane:

   Item	Value
   Destination port ranges	5022,11000-11999
   Protocol	TCP
   Priority	200
   Name	AllowSqlLinkInbound

6. Select Add at the bottom of the pane.

   

7. Select Outbound security rules on the left service menu.

8. Select Add at the top.

9. Use the following information on the Add outbound security rule pane:

   Item	Value
   Destination port ranges	5022,11000-11999
   Protocol	TCP
   Priority	200
   Name	AllowSqlLinkOutbound

10. Select Add at the bottom of the pane.

    

Knowledge

• Ports need to be open in every firewall in the networking environment, including the host server, as well as any corporate firewalls or gateways on the network. In corporate environments, you might need to show your network administrator the information to help open additional ports in the networking layer.
• While you can choose to customize the endpoint on the SQL Server side, the port numbers for a SQL Managed Instance cannot be changed or customized.
• IP address ranges of subnets hosting Managed Instances and SQL Servers must not overlap.

Bidirectional network connectivity between SQL Server and SQL Managed Instance is necessary for the link to work. After you open ports on the SQL Server side and configure an NSG rule on the SQL Managed Instance side, you can test connectivity by using SQL Server Management Studio (SSMS).

When you use Network Checker in SSMS, this automatically creates a temporary SQL Agent job on both SQL Server and the Managed Instance to check the connection. This job is then deleted after the test is finished.

You can alternatively use Transact-SQL, but you’d need to manually delete the SQL Agent job upon completion.

Retrieve the host name of the Managed Instance from Azure

1. Switch to your other tab to check on the deployment of the Managed Instance.

   

2. If completed, select Go to resource group.

   

3. Select the the name of your SQL Managed Instance SQL managed instance.

   

4. Under the Essentials section, copy the value for Host and paste it in a Notepad for future use throughout this lab.

   

5. On the Windows task bar, reopen SQLVM1 to return to the Azure VM.

6. If needed, sign back in using your Azure password (used for SQL/MI authentication).

Test the connection

1. In SQL Server Management Studio’s Object Explorer pane on the left, expand Databases.

2. Right-click the Adatum database, select Azure SQL Managed Instance link, then select Test Connection…

   

3. On the Introduction page, select Next.

4. All Prerequisites should be met. Select Next.

5. Select Login, under Login to SQL Managed Instance.

6. Enter the following in the Connect to Server window:

   Item	Value
   Server name	the NSG name attached to your MI (e.g., SQLMI-myName-NSG)
   Authentication	SQL Server Authentication
   Login	MILab
   Password	your Azure password (used for SQL/MI authentication)

7. Select the checkboxes for Remember password and Trust server certificate, then select Connect.

   

   If you have issues connecting, the Server name value in the instructions is taken from the text box entry for the Host value retrieved from Azure. Correct the value in the text box in the earlier step, as it will continue to be referenced in the instructions.

8. Select Next.

   

9. Enter SQLMIEndpoint for the Endpoint name, then select Next.

   

10. On the Summary page, select Finish to run the test.

11. All tests should show Success. Select Close.

    

Congratulations! You’ve successfully completed this task.