# Access Blob & ADLS2 data in Azure Synapse Serverless pool

I have worked on Azure Synapse Analytics quite extensively. Synapse Analytics should be your goto platform if you want a combination of Big data and Warehousing capabilities under a single roof. There are two major components in Synapse Analytics **Serverless** and **Dedicated.**

In **Serveless** SQL pool as its name implies there is no need to provision or manage any infrastructure and it is primarily used for data analysis across the semi/ unstructured data stored on Blob and ADLS2 storages of various file formats.

On the other hand **Dedicated** SQL pool is fully provisioned environment that provides large scale data warehousing capabilities that runs on **Massively Parallel Processing** (MPP) architecture.

The difference between the two is quite extensive, detailed [here](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/on-demand-workspace-overview) and [here](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-what-is).

In this article I will try my best to highlight different data access options available through Managed Identity and Service Principal on serverless pool. There are some significant caveats between the two.

If you are not aware of service principals and managed identities in Azure you can refer to my article on service principal [here](https://www.azureguru.net/service-principal-for-azure) and my article on managed identity [here](https://www.azureguru.net/managed-identities-in-microsoft-azure).

### SetUp

To start, lets create three csv files that store customer information :

`Customer_1.csv`

```javascript
CustomerID,Name,Email,City
1,Aarav Joshi,aarav.joshi@example.com,Pune
2,Isha Kulkarni,isha.kulkarni@example.com,Mumbai
3,Vikram Singh,vikram.singh@example.com,Bangalore
4,Neha Patil,neha.patil@example.com,Nashik
5,Rohan Deshmukh,rohan.deshmukh@example.com,Sangli
```

`Customer_2.csv`

```javascript
CustomerID,Name,Email,City
6,Priya Chavan,priya.chavan@example.com,Pune
7,Aditya Jadhav,aditya.jadhav@example.com,Solapur
8,Sneha Nair,sneha.nair@example.com,Ahmedabad
9,Arjun Shinde,arjun.shinde@example.com,Aurangabad
10,Kavya Chawla,kavya.chawla@example.com,Lucknow
```

`Customer_3.csv`

```javascript
CustomerID,Name,Email,City
11,Manoj Khanna,manoj.khanna@example.com,Surat
12,Ananya Bansal,ananya.bansal@example.com,Kanpur
13,Karan Malhotra,karan.malhotra@example.com,Visakhapatnam
14,Tanvi Pawar,tanvi.pawar@example.com,Nagpur
15,Rajesh Gaikwad,rajesh.gaikwad@example.com,Thane
```

We will upload these files to a blob storage under a directory `Customers`.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733076566898/53276a88-013c-4cf1-b617-593d15bac7e7.png align="center")

and to the ADSL2 storage

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733076602853/10683b6c-b6ff-4b62-843e-f861226b97d5.png align="center")

Once done we would test different read options through `Managed Identity` and `Service Principal`.

We then create a service principal on Microsoft Entra. If you are not aware of steps to create Service Principal you can refer to one of my article [here](https://www.azureguru.net/service-principal-for-azure).

There are a few pre existing service principals on my Entra. I will use the `Fabric OAUTH2` service principal.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733076929637/b95673bd-46c0-4257-8190-f7272dd9cafd.png align="center")

I had an existing User Managed Identity under my Azure resource. I will use that one.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733082344756/e1b1541e-f569-4999-aee6-6a5f90dc9c4a.png align="left")

Next, create a database in the Server less pool. I created one under name `SQLDB`.

Then create a Encryption key in the master database :

```sql
Use master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='Any Strong Password'
GO
```

Lets access and query the data through different options.

### Access data on Blob Storage with Managed Identity

Grant `Storage Blob Data Contributor` role to the Managed Identity on the blob storage. In our case it is `Fabric_Identity`.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733082859038/f935f7f1-992b-4b80-8d03-a6fc3afceeec.png align="center")

Next create Database scope credentials. You can read more about database scoped credentials [here](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver16).

```sql
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'MANAGED IDENTITY'; -- MANAGED IDENTITY IS THE KEYWORD
```

Then create an External data source. You can read about external data source [here](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver16).

```sql
CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    -- location should be pointing to the container on the blob storage
  	location = 'https://storageaccount.blob.core.windows.net/container',
    CREDENTIAL = MyCredential
);
```

We will use `OPENROWSET` to query the data source. `OPENROWSET` returns the data from the source in a table format.

Running the queries using `PARSER_VERSION = '1.0'` and `PARSER_VERSION = '2.0'`.

Notice the syntax difference using `PARSER_VERSION = '1.0'` and `PARSER_VERSION = '2.0'`. In `PARSER_VERSION = '2.0'` you dont have to specify the `WITH` clause to predefine the structure of the underlying source.

```sql
-- Using PARSER_VERSION = '1.0'
SELECT  
       customerid,
       name,
       email,
       city
FROM   OPENROWSET(BULK 'Customers', -- This is the directory where source files exist
                  DATA_SOURCE = 'SampleSource',
                  FORMAT='csv',
                  PARSER_VERSION = '1.0', 
                  FIRSTROW = 2) 
WITH (
    [Customerid] [int] ,
	[Name] [varchar](40) ,
	[Email] [varchar](40) ,
	[city] [varchar](40) 
) AS [tbl]
GO

-- Using PARSER_VERSION = '2.0'
SELECT 
	customerid,
       name,
       email,
       city
FROM 
	OPENROWSET(BULK 'Customers', -- This is the directory where source files exist
	           DATA_SOURCE = 'SampleSource',
			   FORMAT='csv', 
			   PARSER_VERSION = '2.0',
               FIELDTERMINATOR = ',' ,
               HEADER_ROW = TRUE
		) AS tbl
GO
```

Running either of the two queries should return the underlying data.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733085062960/100568d2-9b43-4219-bef2-33c0e16c8469.png align="left")

### Access data on ADLS2 storage through Managed Identity.

Next we will try to access data on ADLS2 storage through Managed Identity.

Drop the existing data source and scoped credentials.

```sql
DROP EXTERNAL DATA SOURCE SampleSource
DROP DATABASE SCOPED CREDENTIAL MyCredential
```

Grant `Storage Blob Data Contributor` role to the ADLS2 storage.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733086924284/05b85612-861b-443d-a8ee-572bbff1d8c8.png align="center")

Next create scoped credentials and data source. We will use `abfss` protocol.

```sql
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'MANAGED IDENTITY'; -- MANAGED IDENTITY IS THE KEYWORD
GO

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    -- location should be pointing to the adls2 storage container
  	location = 'abfss://container@storageaccount.blob.core.windows.net/',
    CREDENTIAL = MyCredential
);
GO
```

Running the queries using `PARSER_VERSION = '1.0'` and `PARSER_VERSION = '2.0'`.

Notice the syntax difference using `PARSER_VERSION = '1.0'` and `PARSER_VERSION = '2.0'`. In `PARSER_VERSION = '2.0'` you dont have to specify the `WITH` clause to predefine the structure of the underlying source.

```sql
-- Using PARSER_VERSION = '1.0'
SELECT  
       customerid,
       name,
       email,
       city
FROM   OPENROWSET(BULK 'Customers', -- This is the directory where source files exist
                  DATA_SOURCE = 'SampleSource',
                  FORMAT='csv',
                  PARSER_VERSION = '1.0', 
                  FIRSTROW = 2) 
WITH (
    [Customerid] [int] ,
	[Name] [varchar](40) ,
	[Email] [varchar](40) ,
	[city] [varchar](40) 
) AS [tbl]
GO

-- Using PARSER_VERSION = '2.0'
SELECT 
	customerid,
       name,
       email,
       city
FROM 
	OPENROWSET(BULK 'Customers', -- This is the directory where source files exist
	           DATA_SOURCE = 'SampleSource',
			   FORMAT='csv', 
			   PARSER_VERSION = '2.0',
               FIELDTERMINATOR = ',' ,
               HEADER_ROW = TRUE
		) AS tbl
GO
```

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733087362441/642e5826-0707-4e3f-b628-2c2289ebaa20.png align="left")

### Access data on Blob storage through Service Principal

Drop the existing data source and scoped credentials.

```sql
DROP EXTERNAL DATA SOURCE SampleSource
DROP DATABASE SCOPED CREDENTIAL MyCredential
```

As mentioned earlier, we will use the existing service principal called `Fabric OAUTH2`.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733088976995/9e3e6943-e5d0-4a4f-81c8-9a7b9f5e82fc.png align="center")

```sql
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY='ClientId@https://login.microsoftonline.com/tenantId/oauth2/v2.0/token' , 
SECRET='secret'
```

Next create a data source that points to the blob storage

```sql
CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
   	location = 'https://storageaccount.blob.core.windows.net/container',
    CREDENTIAL = MyCredential
);
```

Execute the queries

```sql
-- Using PARSER_VERSION = '1.0'
SELECT  
       customerid,
       name,
       email,
       city
FROM   OPENROWSET(BULK 'Customers', -- This is the directory where source files exist
                  DATA_SOURCE = 'SampleSource',
                  FORMAT='csv',
                  PARSER_VERSION = '1.0', 
                  FIRSTROW = 2) 
WITH (
    [Customerid] [int] ,
	[Name] [varchar](40) ,
	[Email] [varchar](40) ,
	[city] [varchar](40) 
) AS [tbl]
GO

-- Using PARSER_VERSION = '2.0'
SELECT 
	customerid,
       name,
       email,
       city
FROM 
	OPENROWSET(BULK 'Customers', -- This is the directory where source files exist
	           DATA_SOURCE = 'SampleSource',
			   FORMAT='csv', 
			   PARSER_VERSION = '2.0',
               FIELDTERMINATOR = ',' ,
               HEADER_ROW = TRUE
		) AS tbl
GO
```

The queries error out

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733089369491/99d491ee-84d7-4084-bc46-fcf4a73b6a56.png align="center")

This is because the Service Principal has no access to the storage account. Let’s grant the `Storage Blob Data Contributor` role to the Service Principal `Fabric OAUTH2`.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733089628649/91dee830-bb52-4896-8049-bece7b317295.png align="center")

After granting the access, give couple of minutes for changes to take effect.

Re executing the query should now return the data.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733089691806/7b42672c-021e-478a-bc69-64432d7d59d0.png align="left")

### Access data on ADLS2 storage through Service Principal

Drop the data source and credentials and re create them just the way we did in previous step.

```sql
DROP EXTERNAL DATA SOURCE SampleSource
DROP DATABASE SCOPED CREDENTIAL MyCredential
```

Note that below I have used `blob` endpoints. You could also use `dfs` endpoints if you wish.

```sql
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY='ClientId@https://login.microsoftonline.com/tenantId/oauth2/v2.0/token' , 
SECRET='secret'
GO
CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    -- location should be pointing to the adls2 storage container
  	location = 'abfss://container@storageaccount.blob.core.windows.net/',
    CREDENTIAL = MyCredential
);
GO
```

The query would fail due to lack of underlying access to the source.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733091686153/d192fc5a-6ba8-4f65-8dd7-d70adddd3104.png align="left")

Grant `Storage Blob Data Contributor` role to the Service Principal `Fabric OAUTH2` to the ADLS2 storage.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733091721540/5882aca0-dd74-4ccc-be79-acb1f2c40e9c.png align="left")

Give couple of minutes for changes to take effect. Re execute the queries and the query should return the data.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733091867365/e4c6186a-8394-4202-922d-95582e416aa6.png align="left")

### External Tables

Incase if you wish to store the output of the query in a table you cant just create a table and do an Insert into through the Select query.

The only option available is to create an External Table and pre define an External file format.

You can read about External tables [here](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16&tabs=dedicated) and External file format [here](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver16).

Create an External File Format that defines the external source structure.

```sql
CREATE EXTERNAL FILE FORMAT [CsvFormatWithHeader] WITH (
    FORMAT_TYPE = DELIMITEDTEXT, 
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',', 
        FIRST_ROW  = 2,
        STRING_DELIMITER = '"',
        USE_TYPE_DEFAULT = False
        )
)
```

and then create an External Table.

```sql
CREATE EXTERNAL TABLE dbo.Customers
( [Customerid] [int] ,
	[Name] [varchar](40) ,
	[Email] [varchar](40) ,
	[city] [varchar](40)
	)
    WITH (
            LOCATION = '/Customers',
            DATA_SOURCE = SampleSource,
			FILE_FORMAT = [CsvFormatWithHeader]
            ) 
SELECT * FROM [Customers]
```

The created External Table is available under the External table folder in SSMS.

![Synapse SQL Serverless pool](https://cdn.hashnode.com/res/hashnode/image/upload/v1733165374808/88d3d0af-0337-486e-ac7e-4a1dd75e84fa.png align="left")

To drop the External table just use the `DROP` statement.

```sql
DROP EXTERNAL TABLE [dbo].[Customers]
```

### Conclusion

In this article we delved into different options Azure Synapse serverless SQL pools provide to query data directly from Blob and Azure Data Lake Storage without requiring data movement. Using `External Tables` and `OPENROWSET`, one can query unstructured and semi-structured data formats like Parquet, CSV, and JSON seamlessly. This approach reduces storage duplication and operational overhead.

Ensuring proper authentication with Managed identities, Service Principals and database-scoped credentials enhances reduces the overall risks and overheads of maintaining user credentials separately.

In [next](https://www.azureguru.net/access-blob-adls2-data-in-azure-synapse-dedicated-pool) article we will see how we can leverage different options available in Synapse Analytics Dedicated pools to query external data sources on Blob and ADLS2 storages.

Thanks for reading !!!