Today, I got a very good question from a customer that they want to connect using Azure SQL DB to several tables of Azure Synapse using External Tables. Following I would like to share what was the lessons learned and how I was able to connect.
1) First of all, following I would like to share with you that script that I used to connect to Azure Synapse.
CREATE DATABASE scoped CREDENTIAL CredentialJM WITH IDENTITY ='UserNameToConnect'
, SECREt = 'Password'
CREATE EXTERNAL DATA SOURCE [RemoteData] WITH (TYPE = RDBMS, LOCATION = N'servername.database.windows.net', CREDENTIAL = [CredentialJM], DATABASE_NAME = N'databasename')
GO
CREATE EXTERNAL TABLE [dbo].[TableNameSynapse]
([id] [int] NOT NULL)
WITH (DATA_SOURCE = [RemoteData])
2) Finally, I was able to run and obtain data from the table TableNameSynapse without issues.
3) Just wanted to comment one thing that it is very important, even if you create a PrivateLink for Synapse server and you specified in the firewall Deny public network access, always the connection will be stablished from the Azure public IP of the machine that is running your Azure SQL Database never with the private IP. Remember that the Private Link is for incoming connection request not outbound connection request. Also, remember that instead opening the Deny public network access you could use Allow Azure services and resources to access this server.
Enjoy!
