Forum Discussion
Power Participanthow to configure credentials in copy statement?
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='[email protected]', SECRET='xxx'),
FIRSTROW = 2
);
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
Hey Jeanxyz, as per the docs: https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=fabric&preserve-view=true
`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...
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.
4 Replies
- justinjbird
Resolver I
Hey Jeanxyz, as per the docs: https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=fabric&preserve-view=true
`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
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.
- Shahid12523
Community Champion
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!
