How to query Synapse Analytics from Azure Functions

📅2026/01/06👤mark241⏱️5 min read

This article summarizes how to invoke queries from Azure Functions using Azure Synapse Analytics.

Steps

Transform and copy data with Data Factory

First, prepare the data to analyze. In this example, we will analyze logs stored in storage. Transform the raw logs into an analytics-friendly format and store them in Azure Data Lake Storage Gen2.

Here we use a service called Data Factory. Use Data Factory to copy data. Since you can choose the output format during the copy, we will convert it to Parquet.

Below is a Bicep sample. This sample uses wildcards and expressions in the path settings, but adjust those parts as needed for your requirements. Creating the pipeline from the UI in Data Factory Studio is usually easier to understand. You can also export pipelines created in Studio as Bicep files.

bicep

1param factoryName string
2param tags object
3@secure()
4param subscriptionId string
5param sourceResourceGroupName string
6param inputStorageAccountName string
7param outputStorageAccountName string
8 
9param triggerStartTime string = utcNow('yyyy-MM-ddTHH:mm:ssZ')
10 
11resource factory 'Microsoft.DataFactory/factories@2018-06-01' = {
12  identity: {
13    type: 'SystemAssigned'
14  }
15  location: resourceGroup().location
16  name: factoryName
17  properties: {
18    publicNetworkAccess: 'Enabled'
19  }
20  tags: tags
21}
22 
23resource pipelines 'Microsoft.DataFactory/factories/pipelines@2018-06-01' = {
24  parent: factory
25  name: 'clicklog-pipeline'
26  dependsOn: [
27    inputDataSet
28    outputDataSet
29  ]
30  properties: {
31    activities: [
32      {
33        dependsOn: [
34          {
35            activity: 'LookupBlob'
36            dependencyConditions: [
37              'Succeeded'
38            ]
39          }
40        ]
41        policy: {
42          timeout: '0.12:00:00'
43          retry: 0
44          retryIntervalInSeconds: 30
45          secureOutput: false
46          secureInput: false
47        }
48        name: 'CopyAccessLog'
49        userProperties: [
50          {
51            name: 'Source'
52            value: 'container-name/<your path>/'
53          }
54          {
55            name: 'Destination'
56            value: 'destination-container-name//'
57          }
58        ]
59        type: 'Copy'
60        inputs: [
61          {
62            referenceName: 'SourceDataset'
63            type: 'DatasetReference'
64            parameters: {}
65          }
66        ]
67        outputs: [
68          {
69            referenceName: 'DestinationDataset'
70            type: 'DatasetReference'
71            parameters: {}
72          }
73        ]
74        typeProperties: {
75          source: {
76            type: 'JsonSource'
77            storeSettings: {
78              type: 'AzureBlobStorageReadSettings'
79              recursive: true
80              wildcardFolderPath: {
81                value: 'your-path/@{concat(\'y=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'yyyy\'))}/@{concat(\'m=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'MM\'))}/@{concat(\'d=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'dd\'))}'
82                type: 'Expression'
83              }
84              wildcardFileName: '*'
85              enablePartitionDiscovery: false
86            }
87            formatSettings: {
88              type: 'JsonReadSettings'
89            }
90          }
91          sink: {
92            type: 'ParquetSink'
93            storeSettings: {
94              type: 'AzureBlobStorageWriteSettings'
95              copyBehavior: 'FlattenHierarchy'
96            }
97            formatSettings: {
98              type: 'ParquetWriteSettings'
99            }
100          }
101          enableStaging: false
102          validateDataConsistency: false
103          translator: {
104            type: 'TabularTranslator'
105            mappings: [ // Define mappings here
106              {
107                source: {
108                  path: '$[\'time\']'
109                }
110                sink: {
111                  name: 'time'
112                  type: 'DateTime'
113                }
114              }
115              // ...
116            ]
117          }
118        }
119      }
120    ]
121    policy: {
122      elapsedTimeMetric: {}
123    }
124  }
125}
126 
127resource inputBlob 'Microsoft.DataFactory/factories/linkedservices@2018-06-01' = {
128  parent: factory
129  name: 'AzureBlobStorageInput'
130  properties: {
131    annotations: []
132    type: 'AzureBlobStorage'
133    typeProperties: {
134      serviceEndpoint: 'https://${inputStorageAccountName}.blob.${environment().suffixes.storage}/'
135      accountKind: 'StorageV2'
136    }
137    // For remaining properties, see LinkedService objects
138  }
139}
140 
141resource outputBlob 'Microsoft.DataFactory/factories/linkedservices@2018-06-01' = {
142  parent: factory
143  name: 'AzureBlobStorageOutput'
144  properties: {
145    annotations: []
146    type: 'AzureBlobStorage'
147    typeProperties: {
148      serviceEndpoint: 'https://${outputStorageAccountName}.blob.${environment().suffixes.storage}/'
149      accountKind: 'StorageV2'
150    }
151    // For remaining properties, see LinkedService objects
152  }
153}
154 
155resource outputDataSet 'Microsoft.DataFactory/factories/datasets@2018-06-01' = {
156  parent: factory
157  name: 'DestinationDataset'
158  dependsOn: [
159    outputBlob
160  ]
161  properties: {
162    annotations: []
163    description: 'string'
164    folder: {
165      name: 'string'
166    }
167    linkedServiceName: {
168      referenceName: 'AzureBlobStorageOutput'
169      type: 'LinkedServiceReference'
170    }
171    typeProperties: {
172      location: {
173        type: 'AzureBlobStorageLocation'
174        folderPath: {
175          value: '@{concat(\'y=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'yyyy\'))}/@{concat(\'m=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'MM\'))}/@{concat(\'d=\', formatDateTime(addHours(pipeline().TriggerTime, -2), \'dd\'))}'
176          type: 'Expression'
177        }
178        container: 'container-name'
179      }
180      compressionCodec: 'snappy'
181    }
182    type: 'Parquet'
183    // For remaining properties, see Dataset objects
184  }
185}
186 
187resource inputDataSet 'Microsoft.DataFactory/factories/datasets@2018-06-01' = {
188  parent: factory
189  name: 'SourceDataset'
190  dependsOn: [
191    inputBlob
192  ]
193  properties: {
194    annotations: []
195    description: 'string'
196    folder: {
197      name: 'string'
198    }
199    linkedServiceName: {
200      referenceName: 'AzureBlobStorageInput'
201      type: 'LinkedServiceReference'
202    }
203    typeProperties: {
204      location: {
205        type: 'AzureBlobStorageLocation'
206        folderPath: 'your-path'
207        container: 'container-name'
208      }
209    }
210    type: 'Json'
211    schema: {
212      type: 'object'
213      properties: {
214        time: {
215          type: 'string'
216        }
217        ...
218      }
219    }
220    // For remaining properties, see Dataset objects
221  }
222}
223 
224resource DailyTrigger 'Microsoft.DataFactory/factories/triggers@2018-06-01' = {
225  parent: factory
226  name: 'DailyTrigger'
227  dependsOn: [
228    pipelines
229  ]
230  properties: {
231    annotations: []
232    // runtimeState: 'Started'
233    pipelines: [
234      {
235        pipelineReference: {
236          referenceName: 'your-pipeline-name'
237          type: 'PipelineReference'
238        }
239        parameters: {}
240      }
241    ]
242    type: 'ScheduleTrigger'
243    typeProperties: {
244      recurrence: {
245        frequency: 'Day'
246        interval: 1
247        startTime: triggerStartTime
248        timeZone: 'UTC'
249        schedule: {
250          minutes: [
251            0
252          ]
253          hours: [
254            0
255          ]
256        }
257      }
258    }
259    // For remaining properties, see Trigger objects
260  }
261}
262 
263output managedIdentityPrincipalId string = factory.identity.principalId

The resources used here are as follows.

Resource	Description
Microsoft.DataFactory/factories	The Data Factory instance
Microsoft.DataFactory/factories/pipelines	Pipeline definition. Used for data copy and other activities.
Microsoft.DataFactory/factories/linkedservices	Linked services that connect the input and output storage
Microsoft.DataFactory/factories/datasets	Input and output datasets
Microsoft.DataFactory/factories/triggers	Trigger configuration. Not required if you do not need automatic triggering.

Create Synapse

Next, deploy Synapse Analytics. Since a built-in serverless SQL pool is available by default, we will create a database there.

Create a database

Create a database in the built-in serverless SQL pool.

sql

1CREATE DATABASE [myDB];
2GO
3 
4USE [myDB];
5GO

Add an external data source

Create an external data source and connect it to the data lake. At this time, create WorkspaceIdentity and configure access to this external data source to use the permissions of WorkspaceIdentity.

sql

1CREATE MASTER KEY
2ENCRYPTION BY PASSWORD = 'StrongPassword_ChangeThis!';
3 
4CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
5WITH IDENTITY = 'Managed Identity';
6GO
7 
8CREATE EXTERNAL DATA SOURCE MyDataLake
9WITH (
10    LOCATION = 'https://<your storage name>.blob.core.windows.net/<container name>',
11    CREDENTIAL = WorkspaceIdentity
12);
13GO

Create an external table

Create an external table and set the DATA_SOURCE to the external data source created earlier. For LOCATION, specify the blob path. Wildcards are supported.

sql

1-- Create schema
2CREATE SCHEMA ext AUTHORIZATION dbo;
3GO
4 
5CREATE SCHEMA func AUTHORIZATION dbo;
6GO
7 
8-- Create file format
9CREATE EXTERNAL FILE FORMAT [ParquetFormat]
10WITH (FORMAT_TYPE = PARQUET);
11GO
12 
13-- Create external table
14CREATE EXTERNAL TABLE ext.myTable
15(
16  -- Column definitions
17    time           datetime2      NULL,
18    propertyA     nvarchar(512)  NULL,
19    propertyB     nvarchar(512)  NULL,
20    ...
21)
22WITH
23(
24    LOCATION    = 'y=*/**',
25    DATA_SOURCE = [MyDataLake],
26    FILE_FORMAT = [ParquetFormat]
27);
28GO

Create a view

Create a view and narrow down the properties that will be accessed from Functions.

sql

1CREATE VIEW func.viewOfMyTable
2AS
3SELECT
4  [time],
5  [propertyA],
6  [propertyB],
7  ...
8FROM ext.myTable
9WHERE <add a condition here if needed>;
10GO

Grant permissions to the service principal

This is a setup to add database permissions from a pipeline in CI/CD. If you do not plan to do that, you can skip this section.

Using the name of the service principal (SPN) used by the pipeline, create an external user and grant permissions.

sql

1CREATE USER [pipeline-sp] FROM EXTERNAL PROVIDER;
2GO
3 
4ALTER ROLE db_accessadmin ADD MEMBER [pipeline-sp];
5GO
6 
7ALTER ROLE db_owner ADD MEMBER [pipeline-sp];
8GO

Grant the Directory Readers role in Entra ID to the Synapse workspace managed identity

If you want to grant permissions to Functions dynamically, you can specify the Function name and the workspace will automatically look up the managed identity objectId for that Function name and grant permissions. To do this, the workspace itself needs to have the Directory Readers role.

Add the role from Entra ID > Roles and administrators > Directory Readers > Add assignments to the workspace identity. You can confirm the objectId of the workspace managed identity in Synapse Studio under the admin menu Credentials.

Grant permissions to Functions from the pipeline

When you deploy a new Function, grant it access to the database. To run this on Azure Pipelines, I used the following steps.

Prepare a SQL script
Dynamically replace the Function name in the script at pipeline runtime
Execute the SQL using SqlAzureDacpacDeployment@1

Let’s go through them in order.

Prepare SQL

Prepare SQL like the following. The FunctionAppMiName part will later be replaced with the target Function name.

sql

1USE [myDB];
2 
3IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'FunctionAppMiName')
4BEGIN
5    PRINT 'Creating user [FunctionAppMiName] FROM EXTERNAL PROVIDER...';
6    CREATE USER [FunctionAppMiName] FROM EXTERNAL PROVIDER;
7END
8ELSE
9BEGIN
10    PRINT 'User [FunctionAppMiName] already exists.';
11END;
12 
13IF NOT EXISTS (
14    SELECT 1
15    FROM sys.database_role_members rm
16    JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
17    JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
18    WHERE r.name = N'db_datareader'
19      AND m.name = N'FunctionAppMiName'
20)
21BEGIN
22    PRINT 'Adding [FunctionAppMiName] to db_datareader...';
23    ALTER ROLE db_datareader ADD MEMBER [FunctionAppMiName];
24    GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO [FunctionAppMiName];
25    GRANT SELECT ON OBJECT::func.viewOfMyTable TO [FunctionAppMiName];
26    DENY SELECT ON SCHEMA::ext TO [FunctionAppMiName];
27END
28ELSE
29BEGIN
30    PRINT 'Already in db_datareader.';
31END;

A brief explanation of the permissions:

db_datareader is required to read data. I also granted reference permissions to WorkspaceIdentity because the external data source could not be accessed without it. Finally, I granted access to the view and denied direct querying of external tables by denying access to the schema.

Insert the Function name

I rewrote the SQL file using a PowerShell@2 task as follows, but any method is fine as long as you can replace the Function name in the file.

bash

1- task: PowerShell@2
2    displayName: 'Generate grant-function-mi.sql from template'
3    inputs:
4        targetType: 'inline'
5        pwsh: true
6        script: |
7        $templatePath = "$(System.DefaultWorkingDirectory)/drop/sql/grant-function-mi.sql.template"
8        $outputPath   = "$(System.DefaultWorkingDirectory)/drop/sql/grant-function-mi.sql"
9 
10        if (-not (Test-Path $templatePath)) {
11            throw "Template not found: $templatePath"
12        }
13 
14        # Read template
15        $content = Get-Content $templatePath -Raw
16 
17        # Replace (inject values using pipeline variables)
18        $content = $content -replace 'FunctionAppMiName', '$(deploy_resources.functionName)'
19 
20        # Create output directory if it does not exist
21        $dir = Split-Path $outputPath
22        if (-not (Test-Path $dir)) {
23            New-Item -Path $dir -ItemType Directory -Force | Out-Null
24        }
25 
26        # Write SQL
27        $content | Set-Content $outputPath -Encoding UTF8
28 
29        Write-Host "Generated SQL file:"
30        Get-Content $outputPath

Execute SQL

Execute the SQL rewritten above. Run it with a task like the following.

bash

1- task: SqlAzureDacpacDeployment@1
2    displayName: 'Grant db_datareader to Function MI (Synapse serverless)'
3    inputs:
4      # Azure connection (service connection name)
5      azureSubscription: '${{parameters.azureServiceConnection}}'
6 
7      # Synapse serverless SQL endpoint
8      ServerName: '${{parameters.synapseWorkspaceName}}.sql.azuresynapse.net'
9      DatabaseName: 'myDB'
10 
11      # Authentication (using the service connection's SPN)
12      SqlUserName: ''   # Can be left empty
13      SqlPassword: ''   # Can be left empty
14      AuthenticationType: 'servicePrincipal'
15 
16      # Run mode: SQL script (not DACPAC)
17      deployType: 'SqlTask'
18      SqlFile: '$(System.DefaultWorkingDirectory)/drop/sql/grant-function-mi.sql'  # Path to the SQL file generated above
19 
20      # Options (as needed)
21      IpDetectionMethod: 'AutoDetect'

In my case, I used SqlAzureDacpacDeployment@1, but this task cannot accept the Function name as a parameter. Therefore, the workflow is to rewrite the SQL in advance to embed the Function name, and then run the task.

The important point is granting permissions to the Function as shown in the SQL file, so you do not have to follow this exact procedure.

Query from Functions

From Functions, send a query to the view like the following. In .NET, I use the Microsoft.Data.SqlClient package.

csharp

1var queryString = @"
2SELECT TOP (@maxItemCount)
3  [time],
4  [requestUri]
5  -- ...
6FROM func.viewOfMyTable
7WHERE
8  [time] >= @startDate
9  AND [time] <= @endDate
10ORDER BY
11  [time] DESC;
12";

Summary

To query from Functions, you need to grant permissions to the Function. Also, if you want to add permissions from a CI/CD pipeline, you need additional permission setup for the SPN, which can get a bit tricky. Since there are two layers—managed identity permissions and database access permissions—be careful not to mix them up.

In this article, we created an external data source and a view. You could also access the data directly from Functions using OPENROWSET, but I did not choose that approach because it would require granting broader permissions to the Function.