# COPY command in Microsoft Fabric

Let's say you want to import data from an ADLS2 or any other external storage location to a Fabric Data Warehouse on the fly instead of the usual data pipeline approach. Something you would want to do through SQL that provides lightspeed data ingestion and provide the flexibility of making quick setting changes.

One option through which it can be achieved is by using the `COPY` command.

Yes you heard it right...the [COPY](https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest) command from Synapse Analytics can be used to move data into Microsoft Fabric warehouse but it works only for data warehouse and not for database or data lake in Fabric.

Lets see how it can be done.

### SetUp

The given ADLS2 location is a secured location and has three csv files with each file having five records. In total fifteen records.

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733431357706/8fb9c125-2b41-4f62-a587-d3caf774cbe1.png align="left")

Select one of the existing service principal. We will use `Fabric OAUTH2` service principal that exists in my Entra account.

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733431429470/bd87703d-0116-4c61-b958-c50dea30adf1.png align="left")

We require `ClientId`, `TenantId` and `Secret` of the Service Principal.

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733088976995/9e3e6943-e5d0-4a4f-81c8-9a7b9f5e82fc.png?auto=compress,format&format=webp align="left")

Ensure that the Service Principal has atleast `Storage Blob Data Contributor` access to the ADLS2 location.

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733091721540/5882aca0-dd74-4ccc-be79-acb1f2c40e9c.png?auto=compress,format&format=webp align="left")

Create the destination table that matches the structure of the source data.

```sql
   CREATE TABLE Customers
   ( [Customerid] [int] ,
	[Name] [varchar](40) ,
	[Email] [varchar](40) ,
	[city] [varchar](40) 
 )
```

and then use the following `COPY` statement to move the data across.

```sql
COPY INTO  Customers  FROM 'https://storageaccount.blob.core.windows.net/container/Customers' 
WITH ( FILE_TYPE='CSV', FIRSTROW =2,
CREDENTIAL=(IDENTITY= 'ClientId@https://login.microsoftonline.com/tenantId/oauth2/v2.0/token', 
SECRET='secret')
```

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733426764085/051e5abd-ffe5-4fd4-b05f-628c308ce093.png align="left")

Note that I have use `https` protocol with `blob` endpoints. You can use `abfss` protocols with `dfs` endpoint. Similar to use of `COPY` command on Synapse dedicated pool, there are no limitations wrt use of endpoints and protocols in Fabric for use of `COPY` command.

You can refer to this [section](https://www.azureguru.net/access-blob-adls2-data-in-azure-synapse-dedicated-pool#heading-access-blob-with-managed-identity-and-https-protocol) of one of my article on Synapse dedicated pool for further details on `COPY` command in Synapse Analytics.

You can even run this command in TSQL Notebooks

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733435468986/0e57e4c1-0617-400d-8064-45a7db78a570.png align="center")

### Does COPY command work in Fabric Database ?

Unfortunately it doesn’t. When I execute the statement in SSMS it fails with this error message

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733428207611/fcfce662-3f57-4bcc-a56a-2955bec0ceec.png align="center")

and running the same on Fabric UI fails with a different error message.

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733428345504/5853c756-b96c-427e-9d0e-b064574d7e36.png align="center")

I came across this documentation of use of `COPY` command

[https://learn.microsoft.com/en-us/fabric/data-warehouse/ingest-data-copy](https://learn.microsoft.com/en-us/fabric/data-warehouse/ingest-data-copy)

But the article uses data that is publicly available and it does not mention how to use it with secured data sources.

### Database scoped credentials and External data source

But then is it possible to use the other option [database scoped credentials](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver16) with [external data source](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver16) that works in Azure Synapse Analytics ?

I tried creating them. Creation of Database Scoped Credentials and External Data Source do succeed.

*Database Scoped Credentials*

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733428885469/2b0cc653-bbd7-4e1d-acd7-26ccfa0c4fa4.png align="center")

*External Data Source*

```sql
CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    TYPE=BLOB_STORAGE,
    location = 'abfss://adlsfilesystem@sachinadls.dfs.core.windows.net/',
    CREDENTIAL = MyCredential
);
```

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733428993415/026269b8-140e-43cd-9395-19416a0b1bfe.png align="left")

and when I use `OPENROWSET` to query the data that External Data Source points to, the query errors out

```sql
SELECT 
	CustomerID,Name,Email,City
FROM 
	OPENROWSET(BULK 'Customers',format='csv',
    firstrow = 2) 
	WITH (
    [Customerid] [int] ,
	[Name] [varchar](40) ,
	[Email] [varchar](40) ,
	[city] [varchar](40) 
) AS [r]
go
```

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733432948993/50f2b8a5-98b0-4de4-8b9d-e22922ea55a8.png align="center")

I checked the feature comparison between Azure SQL Database and Fabric SQL Database

[Features comparison: Azure SQL Database and SQL database (preview) - Microsoft Fabric | Microsoft Learn](https://learn.microsoft.com/en-us/fabric/database/sql/feature-comparison-sql-database-fabric)

and I found out that `Database Scoped Credentials` are indeed supported

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733429348371/5a8538df-0e56-40a9-af85-6771bfd89177.png align="left")

but `OPENROWSET` isn’t..similar to Synapse dedicated pool

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733429388428/03ac2b5c-ee54-40b4-8bdc-c212a716b9b1.png align="left")

and there is no mention if `External Data Source` is supported or not.

So I am quite unsure the purpose `Database Scoped Credentials` and `External Data Source` serve in Fabric Database.

Just out of curiosity I tried using Managed Identity in the `COPY` command knowing that Managed Identity is now replaced with Workspace Identity in Fabric

```sql
COPY INTO  Customers  FROM 'abfss://adlsfilesystem@sachinadls.dfs.core.windows.net/' 
WITH ( FILE_TYPE='CSV', FIRSTROW =2,
CREDENTIAL=(IDENTITY= 'Managed Identity')
```

and as expected it errored out with a never seen error message which was a first one for me.

![COPY command In Fabric](https://cdn.hashnode.com/res/hashnode/image/upload/v1733432287940/4e7bc546-56dd-4792-85b1-94f1f2c306ec.png align="center")

`Msg 15858, Level 16, State 1, Line 1 Cannot obtain AAD token to access storage. Error message: 'Server identity is not configured. Please follow the steps in "Assign an Azure AD identity to your server and add Directory Reader permission to your identity" (`[`https://aka.ms/sqlaadsetup`](https://aka.ms/sqlaadsetup)`)'.`

### Risks with Use of COPY command

A major issue with the `COPY` command approach is that using it in your production environment is far from ideal. You definitely wouldn't want to hardcode the secret values in your code or share it with anyone in first place.

And since there is nothing equivalent to [Azure Key Vault](https://learn.microsoft.com/en-us/azure/key-vault/general/overview) in Fabric there is no way to encrypt your secrets or securely store them.

The feature is on the roadmap to be released in Q1 2025.

https://learn.microsoft.com/en-us/fabric/release-plan/data-factory#data-source-identity-management-azure-key-vault

But then this would mean you have to store credentials on Azure Key vault and access them from Fabric and key vault wont be inherently a Fabric thing.Though it is possible to access Azure Key vault in a Fabric Notebook using the `MSSPARKUTILS` package.

As it's not possible to access key vaults from SQL. So if you don't want to risk exposing the secrets then it's prudent not to use `COPY` command in prod environment.

### Conclusion

The COPY command is great command if you want to quickly move data across without going through the overhead of creating data pipeline and works well with all protocols and endpoints.

But given the limitations that comes with its use limited only to Data warehouse I would definitely like that it should be supported in Fabric Database as well.

Thanks for reading !!!