Requirement
-> Create a linked server to Azure SQL Database jbsqldbserver in Azure SQL Managed Instance jbmi-sql01.
-> Azure SQL Managed instance and Azure SQL Database are on different Resource group.
-> Azure SQL Database will have “Deny public network access” set to Yes.
-> Before creating a linked server in Azure SQL Managed Instance. I tried connecting to Azure SQL database using SQL Server management studio and got below error,
===================================
Cannot connect to jbmi-sqldb.database.windows.net.
===================================
Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database). (.Net SqlClient Data Provider)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=47073&LinkId=20476
Server Name: jbmi-sqldb.database.windows.net
Error Number: 47073
Severity: 14
State: 1
Line Number: 65536
-> It seems like the private endpoint is not configured correctly. I followed below procedure to get it created properly,
-> Opened the required Azure SQL Database from Azure portal and clicked on “Firewalls and virtual networks”, Clicked on “Create Private Endpoint”.
-> Complete the “Basics” tab as below,
-> Complete the “Resource” tab,
-> Complete the “Configuration” tab. Select the Vnet related to Azure SQL Managed instance,
-> It is to be noted that you cannot select the subnet used by Azure SQL managed instance, you will receive below error when you do that.
The selected subnet ‘MI’ has a delegation and cannot be used with a private endpoint.
-> Select a different subnet for MI and proceed further,
-> Complete the “Tags” tab and create the private endpoint,
-> Now I am able to connect to the Azure SQL Database,
-> Try creating Linked server now,
-> Right click created linked server and click on “test connection” and it connects just fine.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.