Permission error when configuring Polybase to access external data

Question

I followed instructions at https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-s3-compatible?view=sql-server-ver16 to configure PolyBase to access external data in on-prem S3-compatible object storage.

I built my test case on the SQL 2022 instance (developer edition) running on my workstation and my account is the sysadmin.

Below are background info

select @@VERSION
-- Microsoft SQL Server 2022 (RTM-GDR) (KB5035432) - 16.0.1115.1 (X64)  	Jul 30 2024 01:13:46  	Copyright (C) 2022 Microsoft Corporation 	Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19045: ) (Hypervisor) 

GO

-- polybase is enabled
EXEC sp_configure @configname = 'polybase enabled';

-- name	minimum	maximum	config_value	run_value
-- polybase enabled	0	1	1	1

GO 

I successfully created a database-scoped credential and an external data source using this credential, per the instructions.

CREATE DATABASE SCOPED CREDENTIAL s3
 WITH IDENTITY = 'S3',
 SECRET = '****:****' ;
END
GO 

CREATE EXTERNAL DATA SOURCE ds_s3
WITH
(   LOCATION = 's3://s3server.test.com:9000/'
,   CREDENTIAL = s3
);
GO

When I tried to create a view using this external data source, I got the error - Cannot find the CREDENTIAL 's3', because it does not exist or you do not have permission.

CREATE VIEW dbo.vw_test
AS SELECT *
FROM
    OPENROWSET(
        BULK 'testaccount/test1/*.parquet',
        DATA_SOURCE = 'ds_s3',
        FORMAT='PARQUET'
    ) AS src
GO

The existence of both s3 credential and ds_s3 external data source is confirmed by the query below.

SELECT * FROM sys.database_scoped_credentials SELECT * FROM sys.external_data_sources;

Thank you.

Best regards,

Answer 1

I checked the documentation for CREATE EXTERNAL DATA SOURCE, and it seems that the IDENTITY for CREATE DATABASE SCOPED CREDENTIAL must be S3 Access Key. You have it as S3 only.