All,
I have to BULK Insert data into AzureSQL from a Azure Blob Storage Account. I know one way is to use SAS keys but are there more secure ways to load data from T-SQL?
For example, is there a way to use the users AAD account to connect to the Storage? Would Managed Identity work? I have not come across an example in the Internet that uses anything other than SAS Keys.
Gopi
azure data factory generally serves this purpose. You can build a pipeline that grabs data from blob and massages it / loads it into sql, kind of what it's designed for. However if you do not wish to use that, the recommended way is SAS because it can be temporary and revoked at any time. Why do you think SAS is less secure?
as per the documentation: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15#credential--credential_name if you were to create an external data source with blob_storage type the identity/credentials MUST be SAS, as it doesn't support any other authentication type. as such, that means you cannot use any other auth method to a blob storage using tsql.