Skip to content

Commit 72aeff9

Browse files
prmerger-automator[bot]prmerger-automator[bot]
authored
Merge pull request #26315 from radshah-ms/patch-9
Update import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-s…
2 parents 654faff + 3414840 commit 72aeff9

2 files changed

Lines changed: 63 additions & 2 deletions

File tree

‎docs/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md‎

Lines changed: 33 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -97,7 +97,12 @@ BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
9797
9898
## Bulk importing from Azure Blob storage
9999

100-
When importing from Azure Blob storage and the data is not public (anonymous access), create a [DATABASE SCOPED CREDENTIAL](../../t-sql/statements/create-database-scoped-credential-transact-sql.md) based on a SAS key which is encrypted with a [MASTER KEY](../../t-sql/statements/create-master-key-transact-sql.md), and then create an [external database source](../../t-sql/statements/create-external-data-source-transact-sql.md) for use in your BULK INSERT command.
100+
When importing from Azure Blob storage and the data is not public (anonymous access), create a [DATABASE SCOPED CREDENTIAL](../../t-sql/statements/create-database-scoped-credential-transact-sql.md) based on a SAS key which is encrypted with a [MASTER KEY](../../t-sql/statements/create-master-key-transact-sql.md), and then create an [external database source](../../t-sql/statements/create-external-data-source-transact-sql.md) for use in your BULK INSERT command.
101+
102+
Alternatively, create a [DATABASE SCOPED CREDENTIAL](../../t-sql/statements/create-database-scoped-credential-transact-sql.md) based on `MANAGED IDENTITY` to authorize requests for data access in non-public storage accounts. When using `MANAGED IDENTITY`, Azure storage must grant permissions to the managed identity of the instance by adding the **Storage Blob Data Contributor** built-in Azure role-based access control (RBAC) role that provides read/write access to the managed identity for the necessary Azure Blob Storage containers. Azure SQL Managed Instance have a system assigned managed identity, and can also have one or more user-assigned managed identities. You can use either system-assigned managed identities or user-assigned managed identities to authorize the requests. For authorization, the `default` identity of the managed instance would be used (that is primary user-assigned managed identity, or system-assigned managed identity if user-assigned managed identity is not specified).
103+
104+
> [!IMPORTANT]
105+
> Managed Identity is applicable only to Azure SQL. SQL Server does not support Managed Identity.
101106
102107
> [!NOTE]
103108
> Do not use explicit transaction, or you receive a 4861 error.
@@ -130,6 +135,33 @@ FROM 'inv-2017-12-08.csv'
130135
WITH (DATA_SOURCE = 'MyAzureBlobStorage');
131136
```
132137

138+
The following example shows how to use the BULK INSERT command to load data from a csv file in an Azure Blob storage location using Managed Identity. The Azure Blob storage location is configured as an external data source.
139+
140+
```sql
141+
--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
142+
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
143+
GO
144+
145+
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
146+
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
147+
WITH IDENTITY = 'Managed Identity';
148+
149+
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
150+
151+
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
152+
WITH ( TYPE = BLOB_STORAGE,
153+
LOCATION = 'https://****************.blob.core.windows.net/invoices'
154+
, CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
155+
);
156+
157+
BULK INSERT Sales.Invoices
158+
FROM 'inv-2017-12-08.csv'
159+
WITH (DATA_SOURCE = 'MyAzureBlobStorage');
160+
```
161+
162+
> [!IMPORTANT]
163+
> Managed Identity is applicable only to Azure SQL. SQL Server does not support Managed Identity.
164+
133165
> [!IMPORTANT]
134166
> Azure SQL Database does not support reading from Windows files.
135167

‎docs/t-sql/statements/bulk-insert-transact-sql.md‎

Lines changed: 30 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -377,7 +377,13 @@ To resolve this error, use [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.
377377

378378
For more information about this and other security considerations for using BULK INSERT, see [Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)](../../relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md).
379379

380-
When importing from Azure Blob Storage and the data isn't public (anonymous access), create a [DATABASE SCOPED CREDENTIAL](../../t-sql/statements/create-database-scoped-credential-transact-sql.md) based on a SAS key encrypted with a [MASTER KEY](create-master-key-transact-sql.md), and then create an [external database source](../../t-sql/statements/create-external-data-source-transact-sql.md) for use in your BULK INSERT command. For an example, see [Import data from a file in Azure Blob Storage](#f-import-data-from-a-file-in-azure-blob-storage).
380+
When importing from Azure Blob Storage and the data isn't public (anonymous access), create a [DATABASE SCOPED CREDENTIAL](../../t-sql/statements/create-database-scoped-credential-transact-sql.md) based on a SAS key encrypted with a [MASTER KEY](create-master-key-transact-sql.md), and then create an [external database source](../../t-sql/statements/create-external-data-source-transact-sql.md) for use in your BULK INSERT command.
381+
382+
Alternatively, create a [DATABASE SCOPED CREDENTIAL](../../t-sql/statements/create-database-scoped-credential-transact-sql.md) based on `MANAGED IDENTITY` to authorize requests for data access in non-public storage accounts. When using `MANAGED IDENTITY`, Azure storage must grant permissions to the managed identity of the instance by adding the **Storage Blob Data Contributor** built-in Azure role-based access control (RBAC) role that provides read/write access to the managed identity for the necessary Azure Blob Storage containers. Azure SQL Managed Instance have a system assigned managed identity, and can also have one or more user-assigned managed identities. You can use either system-assigned managed identities or user-assigned managed identities to authorize the requests. For authorization, the `default` identity of the managed instance would be used (that is primary user-assigned managed identity, or system-assigned managed identity if user-assigned managed identity is not specified). For an example, see [Import data from a file in Azure Blob Storage](#f-import-data-from-a-file-in-azure-blob-storage).
383+
384+
385+
> [!IMPORTANT]
386+
> Managed Identity is applicable only to Azure SQL. SQL Server does not support Managed Identity.
381387
382388
### Permissions
383389

@@ -526,6 +532,29 @@ FROM 'inv-2017-12-08.csv'
526532
WITH (DATA_SOURCE = 'MyAzureBlobStorage');
527533
```
528534

535+
The following example shows how to use the BULK INSERT command to load data from a csv file in an Azure Blob storage location using Managed Identity. The Azure Blob storage location is configured as an external data source.
536+
537+
```sql
538+
--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
539+
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
540+
GO
541+
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
542+
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
543+
WITH IDENTITY = 'Managed Identity';
544+
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
545+
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
546+
WITH ( TYPE = BLOB_STORAGE,
547+
LOCATION = 'https://****************.blob.core.windows.net/invoices'
548+
, CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
549+
);
550+
BULK INSERT Sales.Invoices
551+
FROM 'inv-2017-12-08.csv'
552+
WITH (DATA_SOURCE = 'MyAzureBlobStorage');
553+
```
554+
555+
> [!IMPORTANT]
556+
> Managed Identity is applicable only to Azure SQL. SQL Server does not support Managed Identity.
557+
529558
> [!IMPORTANT]
530559
> Azure SQL only supports reading from Azure Blob Storage.
531560

0 commit comments

Comments
 (0)