USE [ActivePos_server]
GO
IF OBJECT_ID('dbo.SetTLSOptions')IS NOT NULL 
BEGIN;
    DROP PROCEDURE dbo.SetTLSOptions;
END;
GO
/*============================================================================= 

Set the TLS options of the current sql server instance.
Any change in those options won't be applied until the service is restarted.

Parameters
----------------------


Context
----------------------
This proc is called during update of TriaPharm, once the certificate has been created or renewed to update the configuration of the database.

!!!! Any change to those parameters won't be applied until the next restart of the database

@thumbprint:    varchar, default null   The thumbprint of the certificate to use for TLS encryption. 
                                        A null value means no certificate
@forceEncrypt:  bit, default 0          If 1 then forces all connections to be encrypted with TLS. 
                                        0 means the client can choose if it wants or not to use TLS.

Creation : 13.11.2023 / TSC
Modifications:

=============================================================================*/ 
CREATE PROCEDURE dbo.SetTLSOptions
    @thumbprint VARCHAR(4000) = NULL 
    ,@forceEncrypt BIT = 0
AS 
BEGIN 
    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    DECLARE @tt_read TABLE([value] VARCHAR(MAX), [data] VARCHAR(MAX), id INT NOT NULL IDENTITY )
    DECLARE @currentThumb VARCHAR(4000);
    DECLARE @currentForceEncrypt INT;
    DECLARE @varVal VARCHAR(4000);
    DECLARE @key VARCHAR(8000) = 'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer\SuperSocketNetLib\';

    IF @forceEncrypt IS NULL 
    BEGIN
        PRINT 'null @forceEncrypt ==> 0 '
        SET @forceEncrypt = 0;
    END 

    INSERT INTO @tt_read ([value],
                          [data])
    EXECUTE master.sys.xp_instance_regread 
        'HKEY_LOCAL_MACHINE', 
        @key, 
        'Certificate';
    
    SELECT @currentThumb = [data] FROM @tt_read;
    DELETE FROM @tt_read;

    INSERT INTO @tt_read ([value],
                          [data])
    EXECUTE master.sys.xp_instance_regread 
        'HKEY_LOCAL_MACHINE', 
        @key, 
        'ForceEncryption';
    
    SELECT @currentForceEncrypt = [data] FROM @tt_read;
    DELETE FROM @tt_read;

    IF ISNULL(@currentThumb, '') <> ISNULL(@thumbprint, '')
    BEGIN 
        PRINT 'updating certificate'
        EXEC master.sys.xp_instance_regwrite
            'HKEY_LOCAL_MACHINE', 
            'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer\SuperSocketNetLib\',
            'Certificate',
            'REG_SZ',
            @thumbprint
        ;
    END 

    IF ISNULL(@currentForceEncrypt, '') <> ISNULL(@forceEncrypt, '')
    BEGIN 
        PRINT 'updating forced encryption'
        SET @varVal = CAST(@forceEncrypt AS VARCHAR(5))
        EXEC master.sys.xp_instance_regwrite
            'HKEY_LOCAL_MACHINE', 
            'SOFTWARE\Microsoft\MSSQLSERVER\MSSQLServer\SuperSocketNetLib\',
            'ForceEncryption',
            'REG_SZ',
            @varVal
        ;
    END 
END 
GO