Lesson Learned #176: Using Polybase to connect from SQL OnPremises to Azure Managed Instance/SQLDB

This article is contributed. See the original author and article here.

Today, I got a very interesting question about if could be possible to connect from external tables to Azure SQL Managed Instance, SQL Database or Synase. In this article, I would like to explain it. 

Besides the option that we have with Linked Server, my first option was to use SQL SERVER 2019 and Polybase, after installing Polybase and using the following TSQL statement I was able to connect to Managed Instance, SQL Database and Synapse from my OnPremises or Azure Virtual Machine. 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
CREATE DATABASE SCOPED CREDENTIAL AzureSQLExternalTableCredentials  WITH IDENTITY = 'UserName', Secret = 'Password';
CREATE EXTERNAL DATA SOURCE AzureSQLExternalTableDataSource WITH (LOCATION = 'sqlserver://servername.database.windows.net', PUSHDOWN = ON, CREDENTIAL = AzureSQLExternalTableCredentials);
CREATE EXTERNAL TABLE [dbo].[AzureSQLExternalTable_MyTable] ([id] [int] NOT NULL)         WITH (DATA_SOURCE = AzureSQLExternalTableDataSource ,location='databasename.schemaname.TableName')
    

Running a query Select * from AzureSQLExternaTable_MyTable I was able to obtain the data. 

Unfortunately, it is not possible to insert data to the table AzureSQLExternalTable_MyTable because external tables in AzureSQL, Synapse and SQL Server OnPrem there is not supported run DML commands.

Enjoy!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.