Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Scrape the currently loaded SQL Server TLS certificate from the available error logs
CREATE PROCEDURE [dbo].[get_server_tls_certificate]
@maximum_results INT = 1
AS
BEGIN;
DECLARE @error_log_count INT = 0;
DECLARE @i INT = 0;
SET NOCOUNT ON;
CREATE TABLE #error_log (
[event_date] DATETIME2
, [process_info] NVARCHAR(200)
, [event_text] NVARCHAR(MAX)
);
CREATE TABLE #error_log_list (
[log_number] INT
, [log_start_date] DATETIME
, [log_file_size_bytes] BIGINT
);
INSERT [#error_log_list] ([log_number], [log_start_date], [log_file_size_bytes])
EXEC [master].[sys].[xp_enumerrorlogs];
SET @error_log_count = (
SELECT MAX([log_number]) + 1
FROM [#error_log_list]
);
SET @i = 0;
WHILE @i < @error_log_count
BEGIN;
BEGIN TRY;
INSERT [#error_log] ([event_date], [process_info], [event_text])
EXEC [master].[sys].[sp_readerrorlog] @i, 1, 'cert';
SET @i = @i + 1;
END TRY BEGIN CATCH;
BREAK;
END CATCH;
END;
DELETE [error_log]
FROM [#error_log] [error_log]
WHERE
[error_log].[event_text] != 'A self-generated certificate was successfully loaded for encryption.'
AND [error_log].[event_text] NOT LIKE 'The certificate [[]Cert Hash(%) "%"] was successfully loaded for encryption.'
;
IF (@maximum_results IS NULL OR @maximum_results < 1)
SET @maximum_results = (SELECT COUNT(*) FROM [#error_log]);
SELECT TOP (@maximum_results)
[error_log].[event_date]
, [error_log].[event_text]
, CASE
WHEN [error_log].[event_text] = 'A self-generated certificate was successfully loaded for encryption.' THEN NULL
WHEN [error_log].[event_text] LIKE 'The certificate [[]Cert Hash(%) "%"] was successfully loaded for encryption.'
THEN SUBSTRING([error_log].[event_text]
, CHARINDEX('"', [error_log].[event_text]) + 1
, CHARINDEX('"', [error_log].[event_text], 1 + CHARINDEX('"', [error_log].[event_text])) - (CHARINDEX('"', [error_log].[event_text])) - 1
)
ELSE NULL
END AS [certificate_hash]
, CASE
WHEN [error_log].[event_text] = 'A self-generated certificate was successfully loaded for encryption.' THEN NULL
WHEN [error_log].[event_text] LIKE 'The certificate [[]Cert Hash(%) "%"] was successfully loaded for encryption.'
THEN SUBSTRING([error_log].[event_text]
, CHARINDEX('(', [error_log].[event_text]) + 1
, CHARINDEX(')', [error_log].[event_text]) - CHARINDEX('(', [error_log].[event_text]) - 1
)
END AS [certificate_hash_algorithm]
FROM [#error_log] [error_log]
ORDER BY [error_log].[event_date] DESC;
DROP TABLE [#error_log];
DROP TABLE [#error_log_list];
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment