Copy Data from sql Server to Synapse

Question

Copy Data from sql Server to Synapse

Wajih Arfaoui 70

I am trying to copy data from Azure SQL Server to Synapse via Data Factory, during the process, Azure Blob will be used as a staging layer. The problem is that the client is asking for the roles to assign to my user account and he is against giving admin access to the whole Storage account. Is there a way to bypass this ? Thank you

Wajih ARFAOUI 0 Reputation points

2024-01-09T08:55:25.9333333+00:00

Hello,

Thank you for your answer !

to sum up, in order to use ADP to ingest data from Azure SQL and save it in Azure Synapse without having admin access on the user account level, i need to assign contributor roles for both Azure Storage account and Azure SQL Server to the managed identity of the ADP workspace and connect to them when creating the pipeline using managed identity authentification ? that's it ?
Bhargava-MSFT 31,216 Reputation points Microsoft Employee

2024-01-09T22:14:36.65+00:00

Hello Wajih Arfaoui,

Yes, your understanding is correct. To ingest data from Azure SQL and save it in Azure Synapse without having admin access on the user account level, you would need to assign "storage blob data contributor" or "storage blob data owner" roles to the managed identity of the ADF workspace in your staging Azure Blob Storage.

You also need to assign correct permissions to the managed identity of the ADF workspace in your Azure SQL Server.

and then when creating the pipeline in ADF, you can use the managed identity authentication to connect to both the Azure SQL Server and the staging Azure Blob Storage. This allow you to bypass the need for admin access on the user account level.

I hope this answers your question.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.
Wajih Arfaoui 70 Reputation points

2024-01-10T16:28:36.1933333+00:00

Thank you so much ! Another question in the same context, with Admin role in Synapse analytics, i created a database and a schema but then when trying to create a Table using the Create Table query i kept getting an error message saying this is not supported.
Bhargava-MSFT 31,216 Reputation points Microsoft Employee

2024-01-10T19:43:22.0633333+00:00
Hello Wajih Arfaoui, it seems like you are using serverless pool. If yes, the Serverless SQL pool has no local storage. Only metadata objects are stored in databases. Serverless SQL only supports external tables. You can follow the below document to create an external table. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#example-create-external-table

CREATE EXTERNAL TABLE census_external_table ( decennialTime varchar(20), stateName varchar(100), countyName varchar(100), population int, race varchar(50), sex varchar(10), minAge int, maxAge int ) WITH ( LOCATION = '/parquet/', DATA_SOURCE = population_ds, FILE_FORMAT = census_file_format ) GO SELECT TOP 1 * FROM census_external_table

I hope this helps

Accepted answer

1 additional answer

Your answer

Wajih ARFAOUI 0 Reputation points

2024-01-09T08:55:25.9333333+00:00

Hello,

Thank you for your answer !

to sum up, in order to use ADP to ingest data from Azure SQL and save it in Azure Synapse without having admin access on the user account level, i need to assign contributor roles for both Azure Storage account and Azure SQL Server to the managed identity of the ADP workspace and connect to them when creating the pipeline using managed identity authentification ? that's it ?
Bhargava-MSFT 31,216 Reputation points Microsoft Employee

2024-01-09T22:14:36.65+00:00

Hello Wajih Arfaoui,

Yes, your understanding is correct. To ingest data from Azure SQL and save it in Azure Synapse without having admin access on the user account level, you would need to assign "storage blob data contributor" or "storage blob data owner" roles to the managed identity of the ADF workspace in your staging Azure Blob Storage.

You also need to assign correct permissions to the managed identity of the ADF workspace in your Azure SQL Server.

and then when creating the pipeline in ADF, you can use the managed identity authentication to connect to both the Azure SQL Server and the staging Azure Blob Storage. This allow you to bypass the need for admin access on the user account level.

I hope this answers your question.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.
Wajih Arfaoui 70 Reputation points

2024-01-10T16:28:36.1933333+00:00

Thank you so much ! Another question in the same context, with Admin role in Synapse analytics, i created a database and a schema but then when trying to create a Table using the Create Table query i kept getting an error message saying this is not supported.
Bhargava-MSFT 31,216 Reputation points Microsoft Employee

2024-01-10T19:43:22.0633333+00:00

Hello Wajih Arfaoui, it seems like you are using serverless pool. If yes, the Serverless SQL pool has no local storage. Only metadata objects are stored in databases. Serverless SQL only supports external tables. You can follow the below document to create an external table. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop#example-create-external-table

CREATE EXTERNAL TABLE census_external_table ( decennialTime varchar(20), stateName varchar(100), countyName varchar(100), population int, race varchar(50), sex varchar(10), minAge int, maxAge int ) WITH ( LOCATION = '/parquet/', DATA_SOURCE = population_ds, FILE_FORMAT = census_file_format ) GO SELECT TOP 1 * FROM census_external_table

I hope this helps

Answer 1

Hello Wajih Arfaoui,

Welcome to the Microsoft Q&A forum.

You don't need to have admin access on the Azure blob(staging layer).

If your linked servie(staging linked service) is using managed identity(which is similar to using your own service principal) authentication then you need to grant "storage blob data contributor" permissions to your synapse/adf workspace managed identity in your staging Azure Blob Storage or Azure Data Lake Storage Gen2 account.

Please follow this document to grant perissions to workspace managed identity.

How staged copy works: https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-performance-features#how-staged-copy-works

Per the document, you need to grant delete permission to your Azure Data Factory in your staging storage, so that the temporary data can be cleaned after the copy activity runs.

"Storage blob data contributor" or "storage blob data owner" roles have delete permissions.

Note:

If your staging Azure Storage is configured with VNet service endpoint, you must use managed identity authentication with "allow trusted Microsoft service" enabled on storage account, refer to Impact of using VNet Service Endpoints with Azure storage.

If your staging Azure Storage is configured with Managed Private Endpoint and has the storage firewall enabled, you must use managed identity authentication and grant Storage Blob Data Reader permissions to the Synapse SQL Server to ensure it can access the staged files during the PolyBase load.

I hope this answers your question. Please let me know if you have any further questions.

Answer 2

following on that process, I am trying to copy a table from SQL Server to Synapse and i keep getting this error: ErrorCode=PolybaseOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse. Operation: 'Polybase operation'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Conversion failed when converting from a character string to uniqueidentifier.,Source=.Net SqlClient Data Provider,SqlErrorNumber=8169,Class=16,ErrorCode=-2146232060,State=2,Errors=[{Class=16,Number=8169,State=2,Message=Conversion failed when converting from a character string to uniqueidentifier.,},],' while i made sure to create a table in Synapse with the same columns type as the original table.

Share via

Copy Data from sql Server to Synapse

1 additional answer

Your answer