Certificate login anomaly

Anthony Axlen 1 Reputation point
2022-02-14T07:28:18.647+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2022-02-14T23:08:19.667+00:00

    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.

    0 comments
  2. Anthony Axlen 1 Reputation point
    2022-02-15T13:03:22.577+00:00

    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?