Dela via

  • Artikel

CREATE CREDENTIAL (Transact-SQL)

Creates a credential.

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

Arguments

  • credential_name
    Specifies the name of the credential being created. credential_name cannot start with the number (#) sign. System credentials start with ##.

  • IDENTITY ='identity_name'
    Specifies the name of the account to be used when connecting outside the server.

  • SECRET ='secret'
    Specifies the secret required for outgoing authentication. This clause is optional.

  • FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name
    Specifies the name of an Enterprise Key Management Provider (EKM). For more information about Key Management, see Understanding Extensible Key Management (EKM).

Remarks

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.

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.

After creating a credential, you can map it to a SQL Server login by using CREATE LOGIN or ALTER LOGIN. A SQL Server login can be mapped to only one credential, but a single credential can be mapped to multiple SQL Server logins. For more information, see Credentials (Database Engine).

Information about credentials is visible in the sys.credentials catalog view.

If there is no login mapped credential for the provider, the credential mapped to SQL 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

Requires ALTER ANY CREDENTIAL permission.

Examples

The following example creates the credential called AlterEgo. The credential contains the Windows user Mary5 and a password.

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

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. The sysadmin account on the server creates a credential that is used to connect to the EKM account, and assigns it to the User1SQL 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;
/* Modify the login to assign a non cryptographic provider credential */ 
ALTER LOGIN User1
WITH CREDENTIAL = AlterEgo;
GO