Certificate Based Server Logins - SQL Server

The SQL and Windows logins information will be available in sys.server_principals along with the logins that were created from certificates when SQL Server is installed. However, these certificate-based logins are for internal system use only and will not be used to login to the system.

As per the documentation, these principal accounts do not have passwords that can be changed by administrators as they are based on certificates issued to Microsoft.

Use the below query to identify those logins.

SELECT * FROM sys.server_principals
WHERE  name LIKE '%##%'
              AND is_disabled = 0

If you want to know all the logins that have double hash marks (##), use the below query.

If you see there are 8 principals that are created by SQL Server for both SQL and Windows logins and based on configuration SQL based logins were disabled. As I stated earlier, these logins cannot be used to login to SQL Server instance since they do not have access. Please check below -

USE master
GO

SELECT a.name, a.is_disabled, b.hasaccess FROM sys.server_principals a
JOIN sys.syslogins b ON a.name = b.name
WHERE a.name LIKE '%##%'

The login ##MS_AgentSigningCertificate## is not disabled and has access to the system however one cannot impersonate a login mapped to a certificate/asymmetric key. 

These certificates are either be used as permission containers or to encrypt the data. If you want to see what permissions they have, use the below query-

SELECT a.name principal_name, b.class_desc, b.permission_name
FROM sys.server_permissions b
JOIN sys.server_principals a ON b.grantee_principal_id = a.principal_id
WHERE name LIKE '%Certificate%'
ORDER BY a.name

Please note that these are created by the system, and it should be not be deleted.

Have you ever observed the creation date and modify date for these certificate-based logins?

Click here to know more.