How to Configure Credentials in SQL Server

by Manzoor Siddiqui · Published June 27, 2015 · Updated September 24, 2015

SQL Server Credentials are used to access external resources of windows by enabling SQL Server login. Credentials creates an internal connectivity with SQL Server login to outside resources so that by using SQL Server login you can fetch or access those resources. These credentials are SQL Server database objects only which is used for accessing outside applications or resources and passwords are encrypted using service master key in this method.

Using a windows identity a user who is connected to SQL Server with SQL Server authentication can access outside resources of server instance. One SQL Server login can be mapped to only one credential but single credential can be mapped to multiple SQL Server logins.

We will see how we can configure credentials in SQL Server. We can create credential by T-SQL or by SSMS with both methods.

Create Credential by T-SQL

USE master
GO
CREATE CREDENTIAL DB_Credential
WITH IDENTITY = 'MANZOOR\ManzoorSiddiqui',
SECRET = 'B@6P@$$w0r6'
GO

As you can see, credentials are stored in master system database and password is called secret. In previous tutorial we have already seen database encryption technique and master key details so when new service master key is generated on regular basis, password for credential are automatically decrypted and again encrypted with new service master key.

To alter the credentials we can use below query, in alter credential, values for identity and secret gets reset and we can change password for credential.

USE master
GO
ALTER CREDENTIAL DB_Credential
WITH IDENTITY = 'MANZOOR\ManzoorSiddiqui',
SECRET = 'N3wP@$$w0r6'
GO

If you want to verify the credential, you can fire the below query.

SELECT *FROM sys.credentials
GO

In sys.credentials you can find credential id, credential name, credential identity and other details.

Now we will map the credential with SQL Server Login ‘Lisa’ so that Lisa can access external resources with windows identity.

ALTER LOGIN [Lisa] ADD CREDENTIAL [DB_Credential]
GO

To drop the credentials, use below query.

DROP CREDENTIAL DB_Credential
GO

Create Credential by SQL Server Management Studio and Map to SQL Login

Go to Object Explorer –> Security –> right click on Credentials and select New Credential… as shown below.

Figure 1: Create New Credential

Once New Credential window will pop up, provide details about credential name, Identity and password. Here I am selecting my local domain user ‘MANZOOR\ManzoorSiddiqui’ as identity. In your case, you can provide proper network domain user and write some strong password.

Figure 2: New Credential

After entering all detail, click on OK button and refresh the credential node and check the newly created credential as shown below.

Figure 3: Credential Created

Now, to map the credential with SQL Server Login, go to Security and select login which you want to map with your credential, here I am mapping SQL Server login ‘Lisa’ with credential ‘DB_Credential’ as given below. Select Map to Credential and choose credential from drop down menu and click OK.