98 lines
3.0 KiB
PL/PgSQL
98 lines
3.0 KiB
PL/PgSQL
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
|