In OPENROWSET with DATA_SOURCE authentication mechanism is defined in database scoped credential assigned to the referenced data source.You would need to create server-scoped credential to allow access to storage files. SQL logins can also use OPENROWSET without DATA_SOURCE to access publicly available files, files protected using SAS token, or Managed Identity of Synapse workspace.Azure AD logins can access protected files using their own Azure AD identity if Azure storage allows the Azure AD user to access underlying files (for example, if the caller has Storage Reader permission on Azure storage).Any user can use OPENROWSET without DATA_SOURCE to read publicly available files on Azure storage.
#Show headers for a given table sqlite 3 how to
OPENROWSET use the following rules to determine how to authenticate to storage: Learn more about storage access control in this article. The storage administrator must also enable a user to access the files by providing valid SAS token or enabling Azure AD principal to access storage files. If you need more powerful authentication options, use DATA_SOURCE option and define credential that you want to use to access storage.Ī database user must have ADMINISTER BULK OPERATIONS permission to use the OPENROWSET function. As an example, Azure AD principals can access files only using their Azure AD identity or publicly available files. OPENROWSET without DATA_SOURCE provides quick and easy way to access the storage files but offers limited authentication options.
This option enables you to configure location of the storage account in the data source and specify the authentication method that should be used to access storage. OPENROWSET with DATA_SOURCE can be used to access files on specified storage account: SELECT *įROM OPENROWSET(BULK '/folder/*.parquet',ĭATA_SOURCE='storage', -> Root URL is in LOCATION of DATA SOURCE This option enables you to use the basic authentication option to access the storage (Azure AD passthrough for Azure AD logins and SAS token for SQL logins). This is a quick and easy way to read the content of the files without pre-configuration. OPENROWSET without DATA_SOURCE can be used to directly read the contents of the files from the URL location specified as BULK option: SELECT *įROM OPENROWSET(BULK ' FORMAT = 'PARQUET') AS The OPENROWSET function can optionally contain a DATA_SOURCE parameter to specify the data source that contains files. The data source is an Azure storage account and it can be explicitly referenced in the OPENROWSET function or can be dynamically inferred from URL of the files that you want to read. OPENROWSET function in Synapse SQL reads the content of the file(s) from a data source. The OPENROWSET function is not supported in dedicated SQL pool.
0 kommentar(er)
