Credentials

Credentials typically are used to map a SQL Server authenticated account to a Windows account. You can also use this feature to give an account access to items outside the SQL Server instance, like a file share. This is used often in SQL CLR and, most important, to allow SSIS packages to impersonate Windows accounts when running. This is all done through setting up proxy accounts, which will be discussed later.

You can create a credential through Management Studio or through T-SQL. To create a credential in Management Studio, right-click the Credentials folder in the Security tree and select New Credential. In the New Credential dialog box, type a name for the credential, as shown in Figure 9-5, and select which Windows identity you'd like that credential to impersonate. Finally, type the password to protect the credential.

Figure 9-5

In T-SQL, you use the CREATE CREDENTIAL T-SQL command to create a new credential. The IDENTITY keyword specifies which Windows account you wish to map the credential to, and the SECRET keyword specifies the password. To create the same credential we created in Figure 9-6, use the following syntax:

 CREATE CREDENTIAL [WindowsAdmin] WITH IDENTITY = N'BKNIGHT\Administrator', SECRET = N'password' 

Figure 9-6