1

I have two databases on the same Azure SQL server. I wanted to create an external table in database A to access one table from database B. To do it I followed this this article and everything works fine. Then to increase security I wanted to disable Azure Services access to the SQL Server but to preserve this functionality I added it to a virtual network that was allowed inside firewall settings. It didn't work properly so I created a Private endpoint using documentation. But when I try to do the select the connection is refused because it's not done through the virtual network and the firewall stops it - I see the server public IP.

If I enable azure services access, the external table works properly. Changing the external data source location to the privatelink URL breaks with the SSL certificate error regarding invalid principal.

Is there some other steps I should take to allow SQL Server to connect to itself when Azure Services access is disabled?

Dale K
  • 25,246
  • 15
  • 42
  • 71
rakiop
  • 41
  • 4
  • We also discovered that external db access doesn't work while private endpoint is on. Are you absolutely certain you need this data to be in different databases? It often makes more sense to put the data into the same database in different schemas. – Nick.Mc May 25 '21 at 12:16
  • I would prefer it that way. Using different schema could be a work-around though. @Nick.McDermaid, did you try to use only virtual network to accomplish this task? – rakiop May 25 '21 at 15:01
  • 1
    I'm always telling people: you might need to rethink your "seperate databases" approach in Azure SQL. If you have many small databases that run cross database queries it makes a lot more sense to just put them into schemas into one database. If you are getting an invalid principal error, make sure you are connecting with the fully qualified name, not the IP address. – Nick.Mc May 25 '21 at 22:58

1 Answers1

0

It is not possible due to outbound traffic is not using endpoint, see https://www.drware.com/lesson-learned-177-is-possible-to-use-private-endpoint-with-azure-sql-external-tables/

Stefan Persson
  • 1