The certificate-based logins that start and end with double hash marks are stated below -
##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_PolicySigningCertificate##
##MS_SmoExtendedSigningCertificate##
##MS_AgentSigningCertificate##
By default, they are Windows-based logins and are enabled. If you want to look into both windows and SQL based logins, query against sys.server_principals as mentioned below.
USE master
GO
SELECT * FROM sys.server_principals
WHERE name LIKE '%##%'
If you look into sys.server_principals for the certificate-based logins, you might confuse with the "create date" and "modify date" of those logins. The logins were created when SQL Server is installed however the dates are different than the installation date.
These logins will be dropped and recreated during the 'script level upgrade' which usually takes place after cumulative updates/patches or an unexpected restart of the active SQL node.
There won't be any change in the dates (since no logins will be recreated) when the services are restarted or routine failover takes place.
If you need more information on Server Principals, please click here.
