CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

创建数据库凭据。Creates a database credential. 数据库凭据不会映射到服务器登录或数据库用户。A database credential is not mapped to a server login or database user. 只要数据库在执行需要访问权限的操作,数据库就可使用凭据访问外部位置。The credential is used by the database to access to the external location anytime the database is performing an operation that requires access.

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

CREATE DATABASE SCOPED CREDENTIAL credential_name
WITH IDENTITY = 'identity_name'
    [ , SECRET = 'secret' ]

参数Arguments

credential_name 指定正在创建的数据库范围凭据的名称 。credential_name Specifies the name of the database scoped credential being created. credential_name 不能以数字符号 (#) 开头 。credential_name cannot start with the number (#) sign. 系统凭据以 ## 开头。System credentials start with ##.

IDENTITY ='identityname' 指定从服务器外部进行连接时要使用的帐户名称 _IDENTITY ='identity_name' Specifies the name of the account to be used when connecting outside the server. 要使用共享密钥从 Azure Blob 存储导入文件,标识名称必须是 SHARED ACCESS SIGNATURETo import a file from Azure Blob storage using share key, the identity name must be SHARED ACCESS SIGNATURE. 若要将数据加载到 SQL DW,任何有效的值均可用于标识。To load data into SQL DW, any valid value can be used for identity. 有关共享访问签名的详细信息,请参阅使用共享访问签名 (SAS)For more information about shared access signatures, see Using Shared Access Signatures (SAS).

SECRET ='secret' 指定发送身份验证所需的机密内容 。SECRET ='secret' Specifies the secret required for outgoing authentication. 需要 SECRET 才可从 Azure Blob 存储导入文件。SECRET is required to import a file from Azure Blob storage. 若要从 Azure Blob 存储加载到 SQL DW 或并行数据仓库,Secret 必须是 Azure 存储密钥。To load from Azure Blob storage into SQL DW or Parallel Data Warehouse, the Secret must be the Azure Storage Key.

警告

SAS 密钥值可以“?”(问号)开头。The SAS key value might begin with a '?' (question mark). 使用 SAS 密钥时,必须删除前导“?”。When you use the SAS key, you must remove the leading '?'. 否则会阻止操作。Otherwise your efforts might be blocked.

RemarksRemarks

数据库范围凭据是一个记录,其中包含连接到 SQL ServerSQL Server 以外的资源所需的身份验证信息。A database scoped credential is a record that contains the authentication information that is required to connect to a resource outside SQL ServerSQL Server. 多数凭据包括一个 Windows 用户和一个密码。Most credentials include a Windows user and password.

创建数据库范围凭据之前,数据库必须具有主密钥用于保护凭据。Before creating a database scoped credential, the database must have a master key to protect the credential. 有关详细信息,请参阅 CREATE MASTER KEY (Transact-SQL)For more information, see CREATE MASTER KEY (Transact-SQL).

当 IDENTITY 为 Windows 用户时,机密内容可以是密码。When IDENTITY is a Windows user, the secret can be the password. 机密内容使用服务主密钥进行加密。The secret is encrypted using the service master key. 如果重新生成服务主密钥,则使用新的服务主密钥重新加密机密内容。If the service master key is regenerated, the secret is re-encrypted using the new service master key.

可在 sys.database_scoped_credentials 目录视图中查看有关数据库范围凭据的信息。Information about database scoped credentials is visible in the sys.database_scoped_credentials catalog view.

以下是数据库范围凭据的一些应用:Here are some applications of database scoped credentials:

权限Permissions

需要针对数据库的 CONTROL 权限 。Requires CONTROL permission on the database.

示例Examples

A.A. 为应用程序创建数据库范围凭据Creating a database scoped credential for your application

以下示例创建名为 AppCred 的数据库范围凭据。The following example creates the database scoped credential called AppCred. 数据库范围凭据包含 Windows 用户 Mary5 和一个密码。The database scoped credential contains the Windows user Mary5 and a password.

-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';

-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL AppCred WITH IDENTITY = 'Mary5',
    SECRET = '<EnterStrongPasswordHere>';

B.B. 为共享访问签名创建数据库范围凭据Creating a database scoped credential for a shared access signature

以下示例创建的数据库范围凭据可用于创建可执行批量操作(例如 BULK INSERTOPENROWSET)的外部数据源The following example creates a database scoped credential that can be used to create an external data source, which can do bulk operations, such as BULK INSERT and OPENROWSET. 共享访问签名不能与 SQL Server、APS 或 SQL DW 中的 PolyBase一起使用。Shared Access Signatures cannot be used with PolyBase in SQL Server, APS or SQL DW.

-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';

-- Create a database scoped credential.CREATE DATABASE SCOPED CREDENTIAL MyCredentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'QLYMgmSXMklt%2FI1U6DcVrQixnlU5Sgbtk1qDRakUBGs%3D';

C.C. 为到 Azure Data Lake Store 的 PolyBase 连接创建数据库范围凭据Creating a database scoped credential for PolyBase Connectivity to Azure Data Lake Store

以下示例创建的数据库范围凭据可用于创建可以由 SQL 数据仓库中的 PolyBase 使用的外部数据源The following example creates a database scoped credential that can be used to create an external data source, which can be used by PolyBase in Azure SQL Data Warehouse.

Azure Data Lake Store 使用 Azure Active Directory 应用程序进行服务到服务身份验证。Azure Data Lake Store uses an Azure Active Directory Application for Service to Service Authentication. 请先创建 AAD 应用程序,并记录 client_id、OAuth_2.0_Token_EndPoint 和密钥,然后再尝试创建数据库范围凭据。Please create an AAD application and document your client_id, OAuth_2.0_Token_EndPoint, and Key before you try to create a database scoped credential.

-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';

-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL ADL_User
WITH
    IDENTITY = '<client_id>@\<OAuth_2.0_Token_EndPoint>',
    SECRET = '<key>'
;

详细信息More information