sqlazure-synapseazure-data-lake
Not able to access data lake file in Azure synapse dedicated pool
I created Azure synapse and data lake inside that
1.I uploaded one parquet file in data lake which is connected to synapse
2.When i access that file in serverless pool using openrowset it works
3.but when i access that file in dedicated sql pool it is not authorized. Please check screenshot and same issue arrise when i call this file spark pool
Solution
There is an authorization issue: the default credential works fine in the OPENROWSET serverless pool, but it does not work in the dedicated pool.
I have tried all available credentials mentioned in this documentation.
| CSV | Parquet | ORC | |
|---|---|---|---|
| Azure Blob Storage | SAS/MSI/SERVICE PRINCIPAL/KEY/AAD | SAS/KEY | SAS/KEY |
| Azure Data Lake Gen2 | SAS/MSI/SERVICE PRINCIPAL/KEY/AAD | SAS (blob 1 )/MSI (dfs 2 )/SERVICE PRINCIPAL/KEY/AAD | SAS (blob 1 )/MSI (dfs 2 )/SERVICE PRINCIPAL/KEY/AAD |
For ADLS Gen2, you need to provide any one of the above-mentioned credentials.
I recommend using MSI. Use the code below and ensure you have the minimum RBAC roles required:
- Minimum RBAC roles required: Storage Blob Data Contributor or Storage Blob Data Owner for the Azure AD registered SQL Database server
- How can i insert/ select into a table and delete copied records from original table?
- Synapse/ ADF - How to Truncate table if dynamic config column is True in pre-copy script
- Fastest way to determine if record exists
- Select only distinct values from two tables on pgadmin without getting all combinations
- Unrecognized column name that I define
- How to merge 2 or more rows and SUM a column in an UPDATE without a primary key?
- SEDE Data Query to find posts that have hidden comments added by me
- What is the most efficient way to count rows in a table in SQLite?
- Renaming foreign-key columns in MySQL
- Fetch dates within the next 5 days from today, from a column of dates
- Preg split multi SQL by semicolon in PHP
- Mysql Pivot View
- Is it good practice to have a column for each user and a row for each user in SQL table?
- How to filter Select option to ALL
- error column cannot be null, trying to upload file into sql
- Function vs. Stored Procedure in SQL Server
- Left side of LIKE expression must evaluate to a varchar (actual: varbinary). What's an alternative to converting from varbinary to varchar?
- How to show the same field value from another entity attributes
- Big Query - Get continuous 7 characters from a long string in Big Query
- How to correctly join two tables that each have Start Date and Stop Date columns?
- PHP XAMPP Config
- SQL select distinct with condition
- To get the previous record which is less than the given date and time
- Create OBJECT with column values as Key in Snowflake
- How to load multiline csv feature into MySQL
- SQL copying data with new foreign keys
- An arithmetic expression with a DATETIME value is invalid while using PreparedStatement with Arithmetic calculation in sql later
- space is not allowed after parameter prefix ':' in SpEL support in Spring Data JPA @Query definitions
- postgres- plsql function to get rows counts for specific columns with null data
- SQLAlchemy - Get query results in same order as IN clause