SQL オンデマンド (プレビュー) のストレージ アカウント アクセスを制御するControl storage account access for SQL on-demand (preview)

SQL オンデマンドのクエリは、Azure Storage から直接ファイルを読み取ります。A SQL on-demand query reads files directly from Azure Storage. Azure Storage 上のファイルへのアクセス許可は次の 2 つのレベルで制御されます。Permissions to access the files on Azure storage are controlled at two levels:

  • ストレージ レベル - ユーザーには、基になるストレージ ファイルにアクセスするためのアクセス許可が必要です。Storage level - User should have permission to access underlying storage files. ストレージの管理者は、ファイルの読み取り/書き込み、またはストレージへのアクセスに使用される SAS キーの生成を Azure AD プリンシパルに許可する必要があります。Your storage administrator should allow Azure AD principal to read/write files, or generate SAS key that will be used to access storage.
  • SQL サービス レベル - ユーザーには、外部テーブルからデータを読み取るための SELECT 権限、または OPENROWSET を実行するための ADMINISTER BULK ADMIN 権限が必要です。また、ストレージへのアクセスに使用される資格情報を使用する権限も必要です。SQL service level - User should have SELECT permission to read data from external table or ADMINISTER BULK ADMIN permission to execute OPENROWSET and also permission to use credentials that will be used to access storage.

この記事では、使用できる資格情報の種類と、SQL および Azure AD のユーザーに対して資格情報の参照がどのように実行されるかについて説明します。This article describes the types of credentials you can use and how credential lookup is enacted for SQL and Azure AD users.

サポートされているストレージ承認の種類Supported storage authorization types

SQL オンデマンド リソースにログインしたユーザーには、Azure Storage 内のファイルにアクセスしてクエリを実行する権限が必要です (ファイルが一般公開されていない場合)。A user that has logged into a SQL on-demand resource must be authorized to access and query the files in Azure Storage if the files aren't publicly available. 3 種類の承認 (ユーザー IDShared access signatureマネージド ID) を使用して、非パブリック ストレージにアクセスできます。You can use three authorization types to access non-public storage - User Identity, Shared access signature, and Managed Identity.

注意

Azure AD パススルーは、ワークスペースを作成するときの既定の動作です。Azure AD pass-through is the default behavior when you create a workspace.

ユーザー ID ("Azure AD パススルー" とも呼ばれる) は、SQL オンデマンドにログインしている Azure AD ユーザーの ID がデータ アクセスの承認に使用される承認の種類です。User Identity, also known as "Azure AD pass-through", is an authorization type where the identity of the Azure AD user that logged into SQL on-demand is used to authorize data access. データにアクセスする前に、Azure Storage の管理者が Azure AD ユーザーにアクセス許可を付与する必要があります。Before accessing the data, the Azure Storage administrator must grant permissions to the Azure AD user. 下の表に示されているように、これは SQL ユーザーの種類ではサポートされていません。As indicated in the table below, it's not supported for the SQL user type.

重要

ID を使用してデータにアクセスするには、Storage Blob データの所有者/共同作成者/閲覧者のロールを持っている必要があります。You need to have a Storage Blob Data Owner/Contributor/Reader role to use your identity to access the data. ストレージ アカウントの所有者であっても、Storage Blob データのいずれかのロールに自分自身を追加する必要があります。Even if you are an Owner of a Storage Account, you still need to add yourself into one of the Storage Blob Data roles.

Azure Data Lake Store Gen2 でのアクセス制御の詳細については、「Azure Data Lake Storage Gen2 のアクセス制御」という記事をご覧ください。To learn more about access control in Azure Data Lake Store Gen2, review the Access control in Azure Data Lake Storage Gen2 article.

データベース ユーザーに対してサポートされている認可の種類Supported authorization types for databases users

次の表では、使用可能な認可の種類を確認できます。In the table below you can find the available authorization types:

承認の種類Authorization type SQL ユーザーSQL user Azure AD ユーザーAzure AD user
ユーザー IDUser Identity サポートされていませんNot supported サポートされていますSupported
SASSAS サポートされていますSupported サポートされていますSupported
Managed IdentityManaged Identity サポートされていませんNot supported サポートされていますSupported

サポートされているストレージと承認の種類Supported storages and authorization types

承認と Azure Storage の種類の次の組み合わせを使用できます。You can use the following combinations of authorization and Azure Storage types:

承認の種類Authorization type Blob StorageBlob Storage ADLS Gen1ADLS Gen1 ADLS Gen2ADLS Gen2
SASSAS サポートされています*Supported* サポートされていませんNot supported サポートされています*Supported*
Managed IdentityManaged Identity サポートされていますSupported サポートされていますSupported サポートされていますSupported
ユーザー IDUser Identity サポートされています*Supported* サポートされています*Supported* サポートされています*Supported*

* ファイアウォールで保護されていないストレージには、SAS トークンと Azure AD ID を使用してアクセスできます。* SAS token and Azure AD Identity can be used to access storage that is not protected with firewall.

重要

ファイアウォールで保護されているストレージにアクセスする場合に使用できるのは、マネージド ID のみです。When accessing storage that is protected with the firewall, only Managed Identity can be used. 信頼された Microsoft サービスを許可する設定を行い、そのリソース インスタンスのシステムによって割り当てられたマネージド ID に明示的に Azure ロールを割り当てる必要があります。You need to Allow trusted Microsoft services... setting and explicitly assign an Azure role to the system-assigned managed identity for that resource instance. この場合、インスタンスのアクセス範囲は、マネージド ID に割り当てられた Azure ロールに対応します。In this case, the scope of access for the instance corresponds to the Azure role assigned to the managed identity.

資格情報Credentials

Azure Storage にあるファイルに対してクエリを実行するには、SQL オンデマンド エンドポイントに、認証情報を含む資格情報が必要です。To query a file located in Azure Storage, your SQL on-demand end point needs a credential that contains the authentication information. 2 種類の資格情報が使用されます。Two types of credentials are used:

  • サーバーレベル資格情報は、OPENROWSET 関数を使用して実行されるアドホック クエリに使用されます。Server-level CREDENTIAL is used for ad-hoc queries executed using OPENROWSET function. 資格情報名はストレージ URL と一致する必要があります。Credential name must match the storage URL.
  • データベース スコープ資格情報は、外部テーブルに使用されます。DATABASE SCOPED CREDENTIAL is used for external tables. 外部テーブルは、ストレージへのアクセスに使用する必要がある資格情報を使用して DATA SOURCE を参照します。External table references DATA SOURCE with the credential that should be used to access storage.

管理者は、ユーザーが資格情報を作成または削除できるようにするために、ユーザーに対して ALTER ANY CREDENTIAL 権限を許可または拒否することができます。To allow a user to create or drop a credential, admin can GRANT/DENY ALTER ANY CREDENTIAL permission to a user:

GRANT ALTER ANY CREDENTIAL TO [user_name];

外部ストレージにアクセスするデータベース ユーザーは、資格情報を使用するアクセス許可を持つ必要があります。Database users who access external storage must have permission to use credentials.

資格情報を使用するためのアクセス許可を付与するGrant permissions to use credential

資格情報を使用するには、ユーザーは特定の資格情報に対する REFERENCES 権限を持っている必要があります。To use the credential, a user must have REFERENCES permission on a specific credential. specific_user の storage_credential に対する REFERENCES 権限を許可するには、次を実行します。To grant a REFERENCES permission ON a storage_credential for a specific_user, execute:

GRANT REFERENCES ON CREDENTIAL::[storage_credential] TO [specific_user];

スムーズな Azure AD パススルーの実行を確保するために、すべてのユーザーには、既定で UserIdentity 資格情報を使用する権限が与えられます。To ensure a smooth Azure AD pass-through experience, all users will, by default, have a right to use the UserIdentity credential.

サーバースコープ資格情報Server-scoped credential

サーバースコープ資格情報が使用されるのは、DATA_SOURCE が指定されない OPENROWSET 関数を SQL ログインが呼び出して、ストレージ アカウント上のファイルを読み取るときです。Server-scoped credentials are used when SQL login calls OPENROWSET function without DATA_SOURCE to read files on some storage account. サーバースコープ資格情報の名前は、Azure Storage の URL と一致する必要がありますThe name of server-scoped credential must match the URL of Azure storage. 資格情報を追加するには、CREATE CREDENTIAL を実行します。A credential is added by running CREATE CREDENTIAL. CREDENTIAL NAME 引数の指定が必要になります。You'll need to provide a CREDENTIAL NAME argument. それは、ストレージ内のデータへのパスの一部またはパス全体に一致している必要があります (下記参照)。It must match either part of the path or the whole path to data in Storage (see below).

注意

引数 FOR CRYPTOGRAPHIC PROVIDER はサポートされていません。The FOR CRYPTOGRAPHIC PROVIDER argument is not supported.

サーバーレベル資格情報の名前は、<prefix>://<storage_account_path>/<storage_path> という形式で、ストレージ アカウントの完全なパス (および必要に応じてコンテナー) と一致する必要があります。Server-level CREDENTIAL name must match the full path to the storage account (and optionally container) in the following format: <prefix>://<storage_account_path>/<storage_path>. ストレージ アカウント パスについては、次の表で説明します。Storage account paths are described in the following table:

外部データ ソースExternal Data Source PrefixPrefix ストレージ アカウント パスStorage account path
Azure Blob StorageAzure Blob Storage httpshttps <storage_account>.blob.core.windows.net<storage_account>.blob.core.windows.net
Azure Data Lake Storage Gen1Azure Data Lake Storage Gen1 httpshttps <storage_account>.azuredatalakestore.net/webhdfs/v1<storage_account>.azuredatalakestore.net/webhdfs/v1
Azure Data Lake Storage Gen2Azure Data Lake Storage Gen2 httpshttps <storage_account>.dfs.core.windows.net<storage_account>.dfs.core.windows.net

サーバースコープ資格情報は、次の認証の種類を使用して Azure Storage にアクセスできるようにします。Server-scoped credentials enable access to Azure storage using the following authentication types:

Azure AD ユーザーは、Storage Blob Data OwnerStorage Blob Data ContributorStorage Blob Data Reader のいずれかのロールがあれば、Azure Storage 上のあらゆるファイルにアクセスできます。Azure AD users can access any file on Azure storage if they have Storage Blob Data Owner, Storage Blob Data Contributor, or Storage Blob Data Reader role. ストレージにアクセスするために、Azure AD ユーザーの資格情報は必要ありません。Azure AD users don't need credentials to access storage.

SQL ユーザーが Azure AD 認証を使用してストレージにアクセスすることはできません。SQL users can't use Azure AD authentication to access storage.

データベーススコープ資格情報Database-scoped credential

データベーススコープ資格情報が使用されるのは、任意のプリンシパルが DATA_SOURCE を指定した OPENROWSET 関数を呼び出すとき、または公開ファイルにアクセスしない外部テーブルのデータを選択するときです。Database-scoped credentials are used when any principal calls OPENROWSET function with DATA_SOURCE or selects data from external table that don't access public files. データベーススコープ資格情報は、ストレージ アカウントの名前と一致する必要はありません。The database scoped credential doesn't need to match the name of storage account. これは、ストレージの場所を定義する DATA SOURCE で明示的に使用されます。It will be explicitly used in DATA SOURCE that defines the location of storage.

データベーススコープ資格情報は、次の認証の種類を使用して Azure Storage にアクセスできるようにします。Database-scoped credentials enable access to Azure storage using the following authentication types:

Azure AD ユーザーは、少なくとも Storage Blob Data OwnerStorage Blob Data ContributorStorage Blob Data Reader のいずれかのロールがあれば、Azure Storage 上のあらゆるファイルにアクセスできます。Azure AD users can access any file on Azure storage if they have at least Storage Blob Data Owner, Storage Blob Data Contributor, or Storage Blob Data Reader role. ストレージにアクセスするために、Azure AD ユーザーの資格情報は必要ありません。Azure AD users don't need credentials to access storage.

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>'
)

SQL ユーザーが Azure AD 認証を使用してストレージにアクセスすることはできません。SQL users can't use Azure AD authentication to access storage.

データベーススコープ資格情報は、外部データ ソースで使用され、そのストレージへのアクセスに使用される認証方法を指定します。Database scoped credentials are used in external data sources to specify what authentication method will be used to access this storage:

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>',
          CREDENTIAL = <name of database scoped credential> 
)

Examples

一般公開されているデータ ソースにアクセスするAccess a publicly available data source

次のスクリプトを使用して、一般公開されているデータ ソースにアクセスするテーブルを作成します。Use the following script to create a table that accesses publicly available data source.

CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat]
       WITH ( FORMAT_TYPE = PARQUET)
GO
CREATE EXTERNAL DATA SOURCE publicData
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<public_container>/<path>' )
GO

CREATE EXTERNAL TABLE dbo.userPublicData ( [id] int, [first_name] varchar(8000), [last_name] varchar(8000) )
WITH ( LOCATION = 'parquet/user-data/*.parquet',
       DATA_SOURCE = [publicData],
       FILE_FORMAT = [SynapseParquetFormat] )

データベース ユーザーは、データ ソースを参照する外部テーブルまたは OPENROWSET 関数を使用して、データ ソースからファイルの内容を読み取ることができます。Database user can read the content of the files from the data source using external table or OPENROWSET function that references the data source:

SELECT TOP 10 * FROM dbo.userPublicData;
GO
SELECT TOP 10 * FROM OPENROWSET(BULK 'parquet/user-data/*.parquet',
                                DATA_SOURCE = 'mysample',
                                FORMAT='PARQUET') as rows;
GO

資格情報を使用してデータ ソースにアクセスするAccess a data source using credentials

次のスクリプトを変更して、SAS トークン、ユーザーの Azure AD ID、またはワークスペースのマネージド ID を使用して Azure Storage にアクセスする外部テーブルを作成します。Modify the following script to create an external table that accesses Azure storage using SAS token, Azure AD identity of user, or managed identity of workspace.

-- Create master key in databases with some password (one-off per database)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Y*********0'
GO

-- Create databases scoped credential that use Managed Identity or SAS token. User needs to create only database-scoped credentials that should be used to access data source:

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity'
GO
CREATE DATABASE SCOPED CREDENTIAL SasCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-10-1********ZVsTOL0ltEGhf54N8KhDCRfLRI%3D'

-- Create data source that one of the credentials above, external file format, and external tables that reference this data source and file format:

CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] WITH ( FORMAT_TYPE = PARQUET)
GO

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>'
-- Uncomment one of these options depending on authentication method that you want to use to access data source:
--,CREDENTIAL = WorkspaceIdentity 
--,CREDENTIAL = SasCredential 
)

CREATE EXTERNAL TABLE dbo.userData ( [id] int, [first_name] varchar(8000), [last_name] varchar(8000) )
WITH ( LOCATION = 'parquet/user-data/*.parquet',
       DATA_SOURCE = [mysample],
       FILE_FORMAT = [SynapseParquetFormat] );

データベース ユーザーは、データ ソースを参照する外部テーブルまたは OPENROWSET 関数を使用して、データ ソースからファイルの内容を読み取ることができます。Database user can read the content of the files from the data source using external table or OPENROWSET function that references the data source:

SELECT TOP 10 * FROM dbo.userdata;
GO
SELECT TOP 10 * FROM OPENROWSET(BULK 'parquet/user-data/*.parquet', DATA_SOURCE = 'mysample', FORMAT='PARQUET') as rows;
GO

次のステップNext steps

次の記事では、さまざまなフォルダーの種類やファイルの種類に対してクエリを実行する方法、およびビューの作成と使用について説明します。The articles listed below will help you learn how query different folder types, file types, and create and use views: