CREATE CREDENTIAL (Transact-SQL)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Creates a credential.
Applies to: SQL Server (SQL Server 2008 through current version).
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.
A. Basic Example
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
B. Creating a Credential for 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. 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
C. Creating a Credential for EKM Using the Azure Key Vault
The following example creates a SQL Server credential for the Database Engine to use when accessing the Azure Key Vault using the SQL Server Connector for Microsoft Azure Key Vault. For a complete example of using the SQL Server Connector, see Extensible Key Management Using Azure Key Vault (SQL Server).
The IDENTITY argument of CREATE CREDENTIAL requires the key vault name. The SECRET argument of CREATE CREDENTIAL requires the <Client ID> (without hyphens) and <Secret> to be passed together without a space between them.
In 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 ;
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. 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 ;');