Category: Credentials

ALTER CREDENTIAL SYNTAX – SQL SERVER

Posted on by deviprasadg

ALTER CREDENTIAL SYNTAX

SYNTAX:
[sql]
ALTER CREDENTIAL credentialname WITH IDENTITY = ‘identityname’
, SECRET = ‘secret’
[/sql]

The IDENTITY and SECRET for a credential can be changed using ALTER CREDENTIAL.

Example of Alter Credential:
[sql]
USE [master]
GO
ALTER CREDENTIAL [credential1] WITH IDENTITY = N’username1′, SECRET = N’password1′
GO
[/sql]

USING SQL SERVER MANAGEMENT STUDIO (SSMS) TO ALTER CREDENTIAL:

  • Open SSMS and connect to the server in object explorer.
  • Expand server and Security
  • In Security Expand

    Alter Credential
    Double Click on the credential name
  • DoubleClick on the Credential name
  • Credential properties dailog will pop up

    Credential Properties
    Credential Properties
  • Now change the credential properties such as IDENTITY, Password and click on Ok

CREATE CREDENTIAL SYNTAX – SQL SERVER

Posted on by deviprasadg

CREATE CREDENTIAL SYNTAX:

Syntax for creating credentials:
[sql]
CREATE CREDENTIAL credentialname WITH IDENTITY = ‘identity_name’
, SECRET = ‘secret’
FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name
[/sql]

credentialname – Name of the credential, this is used for refereceing the credential
IDENTITY – This is basically the username
SECRET – Password of the identity/username.
CRYPTOGRAPHIC PROVIDER – Name of Enterprise Key Management Provider.

SECRET and cryptographic_provider_name are optional in the above syntax.

Example:
[sql]
USE [master]
GO
CREATE CREDENTIAL [credential1] WITH IDENTITY = N’username’, SECRET = N’password’
GO
[/sql]

CRYPTOGRAPHIC PROVIDER can be created and used with credentials.

Syntax for creating CRYPTOGRAPHIC PROVIDER:
[sql]
CREATE CRYPTOGRAPHIC PROVIDER provider_name
FROM FILE = ‘path of the dll’
[/sql]

The dll provided above should implement SQL Server Extensible Key Management interface.

CREATING CREDENTIAL USING SQL SERVER MANAGEMENT STUDIO:

  • Open SSMS and connect to the server in object explorer.
  • Expand server and Security
  • In Security Expand

    Create Credential
    Expand Security
  • Right Click on “Credentials” and click on “New Credential…”

    Click on new credential
    Click On New Credential
  • Now a New Credential Window will pop up

    New Credential Window
    New Credential Window
  • Now Enter values for Credential Name, Identity, Password.
    Password and Confirm password must match.
  • Now click on OK.

Thats all.. Now you are done creating a sql server credential.. 🙂

WHAT IS CREDENTIAL – SQL SERVER

Posted on by deviprasadg

WHAT IS SQL SERVER CREDENTIAL?

SQL Server credential stores the authentication information which is required to access a resource.

Credentials store the following details:

  • Credential Name – Name of the credential, this is used for refereceing the credential
  • Identity – This is basically the username
  • Password – Password of the identity/username.
  • Encryption Provider – Authentication details can be encrypted using this option.

Basic Example:
[sql]
USE [master]
GO
CREATE CREDENTIAL [credential1] WITH IDENTITY = N’username’, SECRET = N’password’
GO
[/sql]

In the above example Credential1 is created with identity :username and password : password

The created credentials can be used in agent proxies