CREATE CREDENTIAL (Transact-SQL)CREATE CREDENTIAL (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance

创建服务器级别的凭据。Creates a server-level credential. 凭据是包含连接到 SQL Server 以外的资源时所需的身份验证信息的记录。A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. 多数凭据包括一个 Windows 用户和一个密码。Most credentials include a Windows user and password. 例如,将数据库备份保存到某个位置可能需要 SQL Server 提供访问该位置的特殊凭据。For example, saving a database backup to some location might require SQL Server to provide special credentials to access that location. 有关详细信息,请参阅凭据(数据库引擎)For more information, see Credentials (Database Engine).

备注

若要创建数据库级别的凭据,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)To make the credential at the database-level use CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL). 需要为服务器上的多个数据库使用相同凭据时,请使用服务器级别凭据。Use a server-level credential when you need to use the same credential for multiple databases on the server. 使用数据库范围的凭据以使数据库更易于移植。Use a database-scoped credential to make the database more portable. 数据库移动到新服务器时,数据库范围的凭据将随之移动。When a database is moved to a new server, the database scoped credential will move with it. 使用 SQL 数据库SQL Database 上的数据库范围的凭据。Use database scoped credentials on SQL 数据库SQL Database.

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

语法Syntax

CREATE CREDENTIAL credential_name
WITH IDENTITY = 'identity_name'
    [ , SECRET = 'secret' ]
        [ FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name ]

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

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

重要

使用共享访问签名 (SAS) 时,该名称必须与容器路径匹配,以 https 开头并且不能包含正斜杠。When using a shared access signature (SAS), this name must match the container path, start with https and must not contain a forward slash. 请参见示例 DSee example D.

IDENTITY ='identityname' 指定从服务器外部进行连接时要使用的帐户名称 _IDENTITY ='identity_name' Specifies the name of the account to be used when connecting outside the server. 当凭据用于访问 Azure Key Vault 时,IDENTITY 是该密钥保管库的名称。When the credential is used to access the Azure Key Vault, the IDENTITY is the name of the key vault. 请参阅以下示例 C。See example C below. 凭据使用共享访问签名 (SAS) 时,IDENTITY 是 SHARED ACCESS SIGNATURE。When the credential is using a shared access signature (SAS), the IDENTITY is SHARED ACCESS SIGNATURE. 请参见下面的示例 D。See example D below.

重要

Azure SQL 数据库仅支持 Azure Key Vault 和共享访问签名标识。Azure SQL Database only supports Azure Key Vault and Shared Access Signature identities. 不支持 Windows 用户标识。Windows user identities are not supported.

SECRET ='secret' 指定发送身份验证所需的机密内容。SECRET ='secret' Specifies the secret required for outgoing authentication.

当该凭据用于访问 Azure Key Vault 时,CREATE CREDENTIAL 的 SECRET 参数要求将 <Client ID>(无连字符)和 Azure Active Directory 中服务主体的 <Secret> 一起传递,且二者之间不留空格 。When the credential is used to access the Azure Key Vault the SECRET argument of CREATE CREDENTIAL requires the <Client ID> (without hyphens) and <Secret> of a Service Principal in the Azure Active Directory to be passed together without a space between them. 请参阅以下示例 C。See example C below. 凭据使用共享访问签名时,SECRET 是共享访问签名令牌。When the credential is using a shared access signature, the SECRET is the shared access signature token. 请参见下面的示例 D。See example D below. 有关如何在 Azure 容器上创建存储访问策略和共享访问签名的信息,请参阅第 1 课:在 Azure 容器上创建存储访问策略和共享访问签名For information about creating a stored access policy and a shared access signature on an Azure container, see Lesson 1: Create a stored access policy and a shared access signature on an Azure container.

对于加密提供程序 cryptographic_provider_name,指定企业密钥管理提供程序 (EKM) 的名称 。FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name Specifies the name of an Enterprise Key Management Provider (EKM). 有关密钥管理的详细信息,请参阅可扩展密钥管理 (EKM)For more information about Key Management, see Extensible Key Management (EKM).

备注Remarks

当 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.

创建凭据后,可以使用 CREATE LOGINALTER LOGIN,将该凭据映射到 SQL ServerSQL Server 登录名。After creating a credential, you can map it to a SQL ServerSQL Server login by using CREATE LOGIN or ALTER LOGIN. 一个 SQL ServerSQL Server 登录名只能映射到一个凭据,但是单个凭据可以映射到多个 SQL ServerSQL Server 登录名。A SQL ServerSQL Server login can be mapped to only one credential, but a single credential can be mapped to multiple SQL ServerSQL Server logins. 有关详细信息,请参阅凭据(数据库引擎)For more information, see Credentials (Database Engine). 服务器级别凭据只能映射到登录名,不能映射到数据库用户。A server-level credential can only be mapped to a login, not to a database user.

可以在 sys.credentials 目录视图中查看有关凭据的信息。Information about credentials is visible in the sys.credentials catalog view.

如果该提供程序没有任何登录名映射的凭据,则使用映射到 SQL ServerSQL Server 服务帐户的凭据。If there is no login mapped credential for the provider, the credential mapped to SQL ServerSQL Server service account is used.

一个登录名可以有多个映射的凭据,只要它们用于不同的提供程序即可。A login can have multiple credentials mapped to it as long as they are used with distinctive providers. 每个登录名的每个提供程序只能有一个映射的凭据。There must be only one mapped credential per provider per login. 相同的凭据可以映射到其他登录名。The same credential can be mapped to other logins.

权限Permissions

需要 ALTER ANY CREDENTIAL 权限。Requires ALTER ANY CREDENTIAL permission.

示例Examples

A.A. 创建用于 Windows 标识的凭据Creating a Credential for Windows Identity

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

CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5',
    SECRET = '<EnterStrongPasswordHere>';
GO

B.B. 创建用于 EKM 的凭据Creating a Credential for EKM

下面的示例使用一个名为 User1OnEKM 的帐户,它是以前通过 EKM 的管理工具在 EKM 模块中创建的,并带有一个基本帐户类型和密码。The following example uses a previously created account called User1OnEKM on an EKM module through the EKM's Management tools, with a basic account type and password. 服务器上的 sysadmin 帐户创建用于连接到 EKM 帐户的凭据,并将其分配给 User1 SQL ServerSQL Server 帐户:The sysadmin account on the server creates a credential that is used to connect to the EKM account, and assigns it to the User1 SQL ServerSQL Server account:

CREATE CREDENTIAL CredentialForEKM
    WITH IDENTITY='User1OnEKM', SECRET='<EnterStrongPasswordHere>'
    FOR CRYPTOGRAPHIC PROVIDER MyEKMProvider;
GO

/* Modify the login to assign the cryptographic provider credential */
ALTER LOGIN User1
ADD CREDENTIAL CredentialForEKM;

C.C. 使用 Azure 密钥保管库创建用于 EKM 的凭据Creating a Credential for EKM Using the Azure Key Vault

下面的示例使用了用于 Microsoft Azure Key Vault 的 SQL Server 连接器,创建了供 数据库引擎Database Engine 访问 Azure Key Vault 时使用的 SQL ServerSQL Server 凭据。The following example creates a SQL ServerSQL Server credential for the 数据库引擎Database Engine to use when accessing the Azure Key Vault using the SQL Server Connector for Microsoft Azure Key Vault. 有关使用 SQL ServerSQL Server 连接器的完整示例,请参阅使用 Azure Key Vault 的可扩展密钥管理 (SQL Server)For a complete example of using the SQL ServerSQL Server Connector, see Extensible Key Management Using Azure Key Vault (SQL Server).

重要

CREATE CREDENTIALIDENTITY 参数需要 key vault 名称。The IDENTITY argument of CREATE CREDENTIAL requires the key vault name. CREATE CREDENTIAL 的 SECRET 参数要求将 <Client ID>(无连字符)和 <Secret> 一起传递,且二者之间不留空格 。The SECRET argument of CREATE CREDENTIAL requires the <Client ID> (without hyphens) and <Secret> to be passed together without a space between them.

在下例中, 客户端 ID (EF5C8E09-4D2A-4A76-9998-D93440D8115D) 去掉了连字符,并输入为字符串 EF5C8E094D2A4A769998D93440D8115D ,而 Secret 则表示为字符串 SECRET_DBEngineIn the following example, the Client ID (EF5C8E09-4D2A-4A76-9998-D93440D8115D) is stripped of the hyphens and entered as the string EF5C8E094D2A4A769998D93440D8115D and the Secret is represented by the string SECRET_DBEngine.

USE master;
CREATE CREDENTIAL Azure_EKM_TDE_cred
    WITH IDENTITY = 'ContosoKeyVault',
    SECRET = 'EF5C8E094D2A4A769998D93440D8115DSECRET_DBEngine'
    FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;

下面的示例使用客户端 ID 和 Secret 字符串的变量创建相同的凭据,然后将其连接在一起形成 SECRET 参数 。The following example creates the same credential by using variables for the Client ID and Secret strings, which are then concatenated together to form the SECRET argument. REPLACE 函数用于从客户端 ID 中删除连字符。The REPLACE function is used to remove the hyphens from the Client ID.

DECLARE @AuthClientId uniqueidentifier = 'EF5C8E09-4D2A-4A76-9998-D93440D8115D';
DECLARE @AuthClientSecret varchar(200) = 'SECRET_DBEngine';
DECLARE @pwd varchar(max) = REPLACE(CONVERT(varchar(36), @AuthClientId) , '-', '') + @AuthClientSecret;

EXEC ('CREATE CREDENTIAL Azure_EKM_TDE_cred
    WITH IDENTITY = 'ContosoKeyVault', SECRET = ''' + @PWD + '''
    FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;');

D.D. 使用 SAS 令牌创建凭据Creating a Credential using a SAS Token

适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x)当前版本,以及 Azure SQL 托管实例。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through current version and Azure SQL Managed Instance.

下面的示例使用 SAS 令牌创建共享访问签名凭据。The following example creates a shared access signature credential using a SAS token. 若要详细了解如何在 Azure 容器上创建存储访问策略和共享访问签名,以及使用共享访问签名创建凭据,请参阅教程:将 Microsoft Azure Blob 存储服务用于 SQL Server 2016 数据库For a tutorial on creating a stored access policy and a shared access signature on an Azure container, and then creating a credential using the shared access signature, see Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases.

重要

CREDENTIAL NAME 参数需要名称与容器路径匹配,以 https 开头并且末尾不包含正斜杠。THE CREDENTIAL NAME argument requires that the name match the container path, start with https and not contain a trailing forward slash. IDENTITY 参数需要名称 SHARED ACCESS SIGNATURE。The IDENTITY argument requires the name, SHARED ACCESS SIGNATURE. SECRET 参数需要共享访问签名令牌。The SECRET argument requires the shared access signature token.

共享访问签名密钥不应具有前导值?。The SHARED ACCESS SIGNATURE secret should not have the leading ?.

USE master
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] -- this name must match the container path, start with https and must not contain a trailing forward slash.
    WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.
    , SECRET = 'sharedaccesssignature' -- this is the shared access signature token
GO

E.E. 创建用于托管标识的凭据Creating a Credential for Managed Identity

以下示例会创建表示 Azure SQL 或 Azure Synapse 服务的托管标识的凭据。The following example creates the credential that represent Managed Identity of Azure SQL or Azure Synapse service. 此情况下,不适合使用密码和机密。Password and secret are not applicable in this case.

CREATE CREDENTIAL ServiceIdentity WITH IDENTITY = 'Managed Identity';
GO

另请参阅See Also