Permission error, having trouble loading parquet files from AWS S3 to SQL Server 2022

xa9qqrwz  于 2023-10-15  发布在  SQL Server
关注(0)|答案(1)|浏览(131)

Basically every time I try to connect to AWS bucket to grab a parquet file in SQL Server 2022, I am getting a permission error
Cannot find the CREDENTIAL 's3_dc', because it does not exist or you do not have permission.

Code:

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;  
RECONFIGURE;

exec sp_configure @configname = 'polybase enabled';

CREATE DATABASE SCOPED CREDENTIAL s3_dc
WITH IDENTITY = 'IDENTITY',
     SECRET = 'SECRET' ;

CREATE EXTERNAL DATA SOURCE s3_ds 
    WITH (LOCATION = 's3://s3.amazonaws.com',    
          CREDENTIAL = s3_dc ); 
GO

SELECT *  
FROM OPENROWSET(BULK 'buckett/haz/data/data.parquet',
                FORMAT = 'PARQUET', 
                data_source = 's3_ds') AS [cc];

That's the error message:

Cannot find the CREDENTIAL 's3_dc', because it does not exist or you do not have permission.

I tried disabling firewall, and tried logging in as SQL Server user and as a windows user, still getting same error.

ws51t4hk

ws51t4hk1#

With below command check if your scoped credentials are exist or not

SELECT * FROM sys.database_scoped_credentials;

If they exist still facing the issue you need to give permission to access the DATABASE SCOPED to your user, use below command to grant permisson:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[{credential_name}] TO [{user}];

For more information refer this Document

相关问题