master Credentials

Overview

A credential is the engine’s way of being somebody else.

It holds an identity and a secret, and whatever uses it acts as that identity outside SQL Server:

  • an Agent proxy running a package as a domain account
  • a login mapped to a Windows identity for EKM
  • a backup to URL using a shared access signature
  • a linked server reaching another instance

Every one of those is a standing trust, and the list of them exists in exactly one place, which is master.

The master Credentials page: stored identities and what uses each one
The whole page. One credential and four linked server logins on this instance, ranked with the ones nothing uses first, and the grid naming what each one acts as.

Where to find it

Expand a server in the tree, expand the master database, then Master → Credentials.

The column that makes this page worth opening

Used by.

A credential nothing references is a stored secret with no owner, usually left behind by a feature somebody turned off years ago, and there is no other way in the product to find one.

Two things this page will not do

It never shows a secret

Because it cannot and should not be able to. The secret is encrypted by the service master key and is not readable from T-SQL.

It reads msdb in a query of its own

Rather than in the same batch as the rest, because a login that can read master and not msdb would otherwise lose the whole page to one permission error on the proxy lookup.

Reading the chart

The bars: one per stored identity, the ones nothing uses first
The amber bar at the top is a credential nothing points at, which is a stored secret with no owner. The green bars below it are linked server logins with a user each.

One bar per stored identity, ranked with the ones nothing uses first, and the count of things that use it on the right.

That ranking is the whole point of the picture. A credential with a user is ordinary, and the one with nothing behind it is the row somebody has to make a decision about.

Reading the grid

The grid: name, what it is, what it acts as, and what uses it
Used by is the column that makes the page worth opening. The row reading nothing is the one it exists to find, and the Note column says what would break if it were dropped.
Column What it is
Name The credential.
What it is A plain credential, a shared access signature for backup to URL, or an EKM credential mapped to a cryptographic provider.
Acts as The identity it holds. For a SAS, the storage container it names.
Used by Agent proxies, logins, linked servers. Blank is the finding.
Created create_date.
Last changed modify_date.
Note What this credential appears to be for, and what would break if it were dropped.

The toolbar

Control What it does
Linked servers Opens the Linked Servers report.
Keys and certificates Opens master Keys and Certificates.
Server permissions Opens master Server Permissions.

Where the data comes from

Source What it gives
sys.credentials The credentials, their identity, create and modify dates, and the cryptographic provider for an EKM one.
sys.server_principals Logins mapped to a credential.
sys.servers Linked servers that use one.
msdb.dbo.sysproxies Agent proxies, read in a query of its own.
sys.cryptographic_providers The provider behind an EKM credential.
Report Why you would go there
Agent Security The proxies these credentials sit behind, and what they reach.
master Keys and Certificates The other half of what master protects.
Linked Servers The linked servers that use one.
master Server Permissions Who can use these, in permission terms.
Backup Status Whether a SAS credential is behind your backups.

Frequently asked questions

A credential has nothing in Used by. Can I drop it? Nothing in SQL Server references it. That does not prove nothing outside does, which is why the Note column describes what it appears to be for rather than telling you to drop it.

Why is the msdb lookup separate? Because a login that can read master and cannot read msdb is ordinary. Putting the proxy lookup in the same batch would lose the entire page to that one permission.

Can I see the password? No. Neither can anything else through T-SQL. The secret is protected by the service master key.

What is a shared access signature credential? The credential behind BACKUP TO URL. Its name is the storage container URL, and its secret is the SAS token. Those tokens expire, which is worth knowing before the night they do.