T-SQL to check TLS settings

Manually checking the TLS setting from the registry is a tedious task. We have to open the registry setting and navigate to the path below to determine if legacy protocols are enabled or not on the DB server. We can use either use iiscrypto tool or the T-SQL script to check the setting. The below script works well for 2008R2+ & if the DB instance is clustered, it shows results from the ACTIVE node.

Registry Path:  HKEY_LOCAL_MACHINE\SYSTEM\Current Control Set\ Control\ Security Providers\ SCHANNEL\ Protocols\ 

DECLARE @temp TABLE (
	active_node VARCHAR(20)
	,instance VARCHAR(20)
	,protocol VARCHAR(20)
	,flag INT
	)
DECLARE @regkey VARCHAR(255)
DECLARE @value INT

--Get Status for SSL2.0
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\SSL 2.0\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\SSL 2.0\Server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

--Get Status for SSl3.0
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\SSL 3.0\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\SSL 3.0\Server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

--get Status for TLS1.0
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.0\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.0\Server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

-- get Status for TLS1.1
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.1\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.1\Server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
END

--get status for TLS1.2
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.2\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.2\server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
END

--display all flags.
SELECT *
FROM @temp

Hope it helps !!