Certificate login anomaly

Question

Hello,

I've implemented a procedure based solution, which use SERVERPROPERTY with PASSWORDHASH property inside. I'm pulling CAST(LOGINPROPERTY(p.name, 'PasswordHash' ) AS varbinary (256) ) password from within a sp that is signed with a certificate where the login for the certificate is control server (requirement for PASSWORDHASH property).

In almost 99% of the usage the password is returned properly, but sometimes, when I've tried to run it again passwords do not appear. The only way to get it working again is to follow the steps to drop the cert user and rebuild it back from scratch. I've been bitten by this anomaly more than once.

Do you have any idea, what could be the reason for?

Answer 1

Someone may have altered the procedure? That would cause the signature to be dropped.

Next time this happens, run this query which should return all procedures in the database that are signed by a server certificate:

SELECT quotename(s.name) + '.' + quotename(o.name) AS Module,
       c.name AS Cert, c.subject, dp.name AS [Username], cp.*
FROM   master.sys.crypt_properties cp
JOIN   master.sys.certificates c ON cp.thumbprint = c.thumbprint
LEFT   JOIN sys.database_principals dp ON c.sid = dp.sid
JOIN   sys.objects o ON cp.major_id = o.object_id
JOIN   sys.schemas s ON o.schema_id = s.schema_id

Even better run it now in the database where you have the procedure to check that it returns correct data, so that you can trust it, next time you have an incident.

Answer 2

Hello,

just two DBAs have access and proper permissions on this instance and I'm sure, that the procedure is not changing between the tries.

Is there any other reason (or reasons) for this behavior?