Read data from Azure Synapse Serverless SQL Pools with Azure Data Factory

We have some data we can query using the serverless SQL pools in Azure Synapse Analytics. For this blog post, I’m querying data that is stored in Azure Cosmos DB. Read the blog post How to Store Normalized SQL Server Data into Azure Cosmos DB to learn more about how that data got there.

Suppose I now want to read the data using Azure Data Factory. You can read data from Cosmos DB directly, but let’s pretend I want to do some transformations first using my favorite language: SQL. How can we do this?

First we need to create a database and a view in the serverless SQL pool. In the Synapse workspace, we can create a new database in the Data tab.

Choose the serverless option and give the database a name.

Next, you create a view on top of the SELECT statement you want to run, using CREATE OR ALTER VIEW.

In Azure Data Factory (ADF), we create a linked service to Azure Synapse.

There’s one problem however: ADF expects dedicated pools and doesn’t display our newly created database in the list.

As a work around, you can type it in yourself:

To make sure ADF can actually connect to the database, we need to add it as a login and user to the serverless pool.

CREATE LOGIN [mssqltips-df]
	FROM EXTERNAL PROVIDER;

CREATE USER [mssqltips-df]
   FROM LOGIN [mssqltips-df];

ALTER ROLE db_datareader ADD MEMBER [mssqltips-df];

To access data, we need to create a dataset. Again, we choose Azure Synapse Analytics as the type.

In the dataset properties, specify a name, choose the linked service we just created and either enter the table name or select it from the dropdown:

Since we’re using a credential in the FROM OPENROWSET clause of the view (to connect to Cosmos DB), we need to give the managed identity permissions to use this credentials, otherwise we get the error “credential has not been found or you do not have permissions to access it” when trying to import the schema.

This can be done with the following SQL statement:

GRANT REFERENCES ON CREDENTIAL::[mssqltips-cosmoslink] TO [mssqltips-df];

Note: in this case I used a server credential, but it’s also possible to use database credentials (which changes the SQL statement slightly). Make sure you are connected to the correct database when executing the statement.

If everything is configured successfully, you can preview the data:

The dataset can now be used in a Copy data activity or in a data flow.