Scrape the currently loaded SQL Server TLS certificate from the available error logs

get_server_tls_certificate.sql

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;

And how to monitor the expiry date auf this certificate ?

And how to monitor the expiry date auf this certificate ?

The SQL Error log does not emit information about most of the certificate's contents, such as the valid period. The thumbprint captured by this script can facilitate a different script that can examine the contents of the local certificate store to find the full certificate and extract relevant details such as valid period or certifying authority. Doing so has many complications and cannot be executed via T-SQL (except by indirectly launching cmd or powershell, which I would avoid). From memory, the WMI provider for interacting with remote certificate stores also forbids access to private keys (even such actions as setting permissions on them), which was a roadblock I ran into years ago when working on a more comprehensive solution. Suffice to say, this T-SQL has a very narrow focus.