How to Configure Credentials in SQL Server
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.
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.
After entering all detail, click on OK button and refresh the credential node and check the newly created credential as shown below.
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.
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]