Sql-server – Capturing the Login Failure alerts specifc to bad password attempts

If logins are getting locked, that indicates the password is incorrect. Either the server has the incorrect password recorded for the login, or the client is presenting the wrong password.

Since this is a mirroring environment, I'd bet the client is using the same password for both instances; meaning the password for the login on the mirror is not the same as the password for the login on the master.

Recreate the login on the mirror server with the known-good password, i.e. not the hashed version.

Ensure you get the sid correct (use copy-and-paste) from this query, ran against the principal server:

SELECT sp.name
    , sp.sid
FROM sys.server_principals sp
WHERE sp.name = 'login_name';

Use that sid in the CREATE LOGIN statement you run on the mirror:

CREATE LOGIN login_name
WITH PASSWORD = 'thepassword'
    , SID = 0x000000000000 --put the real sid here.
    , DEFAULT_DATABASE = tempdb;

The sid is only important because it links the login to the user inside the database.

TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

// MessageId: CRYPT_E_NOT_FOUND
//
// MessageText:
//
// Cannot find object or property.
//
#define CRYPT_E_NOT_FOUND                _HRESULT_TYPEDEF_(0x80092004L)

What could be the issue with the certificate?

The certificate is corrupt or otherwise unusable in its current state, or lacks a private key component (either corrupt or account isn't able to access).

How and what I have to check?

A quite easy and quick check is to open up the MMC Certificates Manager console (certmgr.msc) and right-click on the certificate, choose "all tasks", "Manage Private Key", which will most likely result in an error stating that the key doesn't exist or some other random error message.

If you don't receive an error, then make sure the SQL Server Service account has permissions to read the private key (also on the manage private keys screen).

If you get an error, then SQL Server can't read the private key which means it's going to fail to start up. Thus, either remove the SSL Certificate from use for SQL Server, restore it from a good backup, or generate and use a new one.