how to configure credentials in copy statement?

[Jeanxyz]

I'm trying to copy a csv file from lakehouse to warehouse across two workspaces, below is my T-SQL statement:

COPY INTO dbo.my_table
FROM 'https://onelake.dfs.fabric.microsoft.com/Stock_Fabric_test/stock_lakehouse.Lakehouse/Files/employee_100_data.csv'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL = (IDENTITY='aaabbb@wzying58gmail.onmicrosoft.com', SECRET='xxx'),
    FIRSTROW = 2
);​

 

I got an error as below: 

Incorrect syntax near 'aaabbb@wzying58gmail.onmicrosoft.com'.

I suspect the credentials I put in is not valid, however, that's the user name and password I used to log into Fabric. Maybe it's because I don't have an Microsoft Entra ID.  Could someone test on your side and let me know what's the correct format for credential input?

@Amit ch

[justinjbird · Accepted solution]

Hey @Jeanxyz, as per the docs: https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=fabric&preserve-v...

`The user's EntraID authentication is default, no credential needs to be specified. COPY INTO using OneLake as source only supports EntraID authentication.`

So CREDENTIAL is optional, if you already have permission to your warehouse you won't need to use this. Since you have inferred you're trying to connect as you thought I would point that out...

[Shahid12523]

You can’t use your Microsoft login/password in COPY INTO. First, create a database scoped credential using a SAS token or service principal, then reference that credential in your COPY INTO statement.

[Jeanxyz]

Could you please provide more details about creating SAS token or service principal? I'm not familiar with Azure system. I have only created an external free azure account in Azure portal.

Thanks!

[justinjbird · Accepted solution]

Hey @Jeanxyz, as per the docs: https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=fabric&preserve-v...

`The user's EntraID authentication is default, no credential needs to be specified. COPY INTO using OneLake as source only supports EntraID authentication.`

So CREDENTIAL is optional, if you already have permission to your warehouse you won't need to use this. Since you have inferred you're trying to connect as you thought I would point that out...

[Jeanxyz · Accepted solution]

Thanks. You might be right. I removed the credential part, this time the query returns: no matched file found at the location. I then read into MS documentation, it says: The COPY command feature in Warehouse in Microsoft Fabric uses a simple, flexible, and fast interface for high-throughput data ingestion for SQL workloads. In the current version, we support loading data from external storage accounts only. I then found my study notes  (stupid me:), it says: copy statement only works between Azure storage account and warehouse, e.g., Azure Blob Storage, Azure Data Lake Storage Gen2.

So it won't work because my file is located in Fabric Datalake, it's an internal storage account.