Create a Credential

This topic describes how to create a credential in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server. This is primarily used to execute code in Assemblies with EXTERNAL_ACCESS permission set. Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup.

A credential can be mapped to several SQL Server logins at the same time. A SQL Server login can only be mapped to one credential at a time. After a credential is created, use the Login Properties (General Page) to map a login to a credential.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To create a credential, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

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

Security

Permissions

Requires ALTER ANY CREDENTIAL permission to create or modify a credential and ALTER ANY LOGIN permission to map a login to a credential.

[Top]

Using SQL Server Management Studio

To create a credential

1. In Object Explorer, expand the Security folder.

2. Right-click the Credentials folder and select New Credential….

3. In the New Credential dialog box, in the Credential Name box, type a name for the credential.

4. In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account, but the identity can be an account of another type.

   Alternately, click the ellipsis (…) to open the Select User or Group dialog box.

5. In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.

6. Select Use Encryption Provider to set the credential to be verified by an Extensible Key Management (EKM) Provider. For more information, see Extensible Key Management (EKM)

7. Click OK.

[Top]

Using Transact-SQL

To create a credential

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

   -- Creates the credential called "AlterEgo.". 
   -- The credential contains the Windows user "Mary5" and a password.
   CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5', 
       SECRET = '<EnterStrongPasswordHere>';
   GO
   

For more information, see CREATE CREDENTIAL (Transact-SQL).

[Top]