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 !!

Check Uptime of SQL server

It is important to find out for how long the SQL server is up & running to diagnose any issues. Here is one of the scripts which I use almost every day to find the uptime of the Server.

SET NOCOUNT ON;

DECLARE @UpTime VARCHAR(12)
	,@StartDate DATETIME
	,@sqlmajorver INT
	,@sqlcmd NVARCHAR(500)
	,@params NVARCHAR(500)

SELECT @sqlmajorver = CONVERT(INT, (@@microsoftversion / 0x1000000) & 0xFF);

IF @sqlmajorver = 9
BEGIN
	SET @sqlcmd = N'SELECT @StartDateOUT = login_time, @UpTimeOUT = DATEDIFF(mi, login_time, GETDATE()) FROM master..sysprocesses WHERE spid = 1';
END
ELSE
BEGIN
	SET @sqlcmd = N'SELECT @StartDateOUT = sqlserver_start_time, @UpTimeOUT = DATEDIFF(mi,sqlserver_start_time,GETDATE()) FROM sys.dm_os_sys_info';
END

SET @params = N'@StartDateOUT DATETIME OUTPUT, @UpTimeOUT VARCHAR(12) OUTPUT';

EXECUTE sp_executesql @sqlcmd
	,@params
	,@StartDateOUT = @StartDate OUTPUT
	,@UpTimeOUT = @UpTime OUTPUT;

SELECT @@servername AS ServerName
	,GETDATE() AS [Current_Time]
	,@StartDate AS Last_Startup
	,CONVERT(VARCHAR(4), @UpTime / 60 / 24) + 'd ' + CONVERT(VARCHAR(4), @UpTime / 60 % 24) + 'h ' + CONVERT(VARCHAR(4), @UpTime % 60) + 'm' AS Uptime
	,@@version AS Version
GO

Hope it helps !!

Who issued " DROP Database " command ?

Sometimes it is hard to track who dropped a SQL Server database,  especially when you are in a  large shop managing several databases. It should have been dropped by one of your team members as part of any decommission request or the application users themselves; who have enough privileges on the DB or Server. Here is one quick script I use. If we are lucky enough, we will find an entry in the default trace.😎

DECLARE @FileName VARCHAR(255)

SELECT @FileName = SUBSTRING(path, 0, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1) + '\Log.trc' --'
FROM sys.traces
WHERE is_default = 1;

SELECT TOP 1 LoginName
	,HostName
	,ApplicationName
	,StartTime
	,TextData
FROM sys.fn_trace_gettable(@FileName, DEFAULT) AS gt
WHERE EventClass = 47
	AND DatabaseName = 'Pass DB Name here'
ORDER BY StartTime DESC;

Hope it helps !!

SQL Server Blocking Email Notification

Blocking is common in any RDBMS product, but it is important to get a notification if it is unusual. Steps to set up an automated email notification for any blocking greater than 30 min are:-

Step 1: Configure Database Mail 

You can go here to setup DatabaseMail if you don't have one already 

Step 2: Create Stored Procedure in the DBA utility database or any database using the below script. The script makes use of the SQL Server DatabaseMail & SQL Server Agent feature to send Blocking Notification either to the Database Team or Application Team if it is greater than 30 minutes. You can adjust the duration based on your business requirement.

 

USE DBA_DB --Change Database Name
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[BlockingReport]
AS
IF object_id('tempdb..#temp_requests') IS NOT NULL
	DROP TABLE #Password

SELECT @@servername AS [ServerName]
	,[Blocker SPID] = B.[SPID]
	,[Blocker Last Batch] = B.[Last_Batch]
	,[Blocker Open Tran] = B.[Open_Tran]
	,
	--[Blocked For] = convert(varchar,(V.waittime/(1000))/(60*60)) + ' Hours' + convert(varchar,(V.waittime/(1000))/60) + ' Minutes ' + convert(varchar,(V.waittime/(1000))%60) + ' Seconds' ,
	[Blocked For_hh:mm:ss] = convert(VARCHAR, dateadd(ms, V.waittime, 0), 114)
	,[Blocker Login Name] = B.[LogiName]
	,[Blocker Host Name] = B.[HostName]
	,[Blocker Program Name] = B.[Program_Name]
	,[Blocker status] = B.[Status]
	,[Blocker Db Name] = DB_NAME(B.[DBID])
	,[Blocker Command Text] = BlockerText.[text]
	,[Victim SPID] = V.[SPID]
	,[Victim Last Batch] = V.[Last_Batch]
	,[Victim Open Tran] = V.[Open_Tran]
	,[Victim Wait Time_hh:mm:ss] = convert(VARCHAR, dateadd(ms, V.waittime, 0), 114)
	,[Victim Login Name] = V.[LogiName]
	,[Victim Host Name] = V.[HostName]
	,[Victim Program Name] = V.[Program_Name]
	,[Victim status] = V.[Status]
	,[Victim Db Name] = DB_NAME(V.[DBID])
	,[Victim Command Text] = VictimText.[text]
INTO #temp_requests
FROM [master].[dbo].[sysprocesses] B(NOLOCK)
JOIN [master].[dbo].[sysprocesses] V(NOLOCK) ON V.Blocked = B.SPID
OUTER APPLY [sys].[dm_exec_sql_text](B.sql_handle) BlockerText
OUTER APPLY [sys].[dm_exec_sql_text](V.sql_handle) VictimText
WHERE V.waittime > 1000 * 60 * 30 --30 Min
	AND V.Blocked != 0
	AND V.Blocked != V.SPID

DECLARE @count INT

SET @Count = @@ROWCOUNT

IF @Count > 0
BEGIN
	BEGIN
		-- Email Querry--
		DECLARE @Body VARCHAR(max)
		DECLARE @TableHead VARCHAR(max)
		DECLARE @TableTail VARCHAR(max)
		DECLARE @mailitem_id AS INT
		DECLARE @statusMsg AS VARCHAR(max)
		DECLARE @Error AS VARCHAR(max)
		DECLARE @Note AS VARCHAR(max)

		SET NOCOUNT ON;
		SET @mailitem_id = NULL
		SET @statusMsg = NULL
		SET @Error = NULL
		SET @Note = NULL
		SET @TableTail = '</table>
<br/>Please review the Blocking Queries & take necessary action<br/>
<br/>Thanks<br/>DBA Team</body></html>';
		--HTML layout--
		SET @TableHead = '<html><head>' + 'Hello,<br/>
<br/>

<br/>
Please find the blocking details on SQL server  :  <br/>
<br/>
' + '<style>' + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' + '</style>' + '</head>' + '<body><table cellpadding=0 cellspacing=0 border=0>' + '<tr bgcolor=#F6AC5D>' + '<td align=center><b>Server Name </b></td>' + '<td align=center><b>Blocker SPID </b></td>' + '<td align=center><b>Blocker Last Batch</b></td>' + '<td align=center><b> Blocker Open Tran </b></td>' + '<td align=center><b>Blocked For_hh:mm:ss </b></td>' + '<td align=center><b>Blocker Login Name </b></td>' + '<td align=center><b> Blocker Host Name</b></td>' + '<td align=center><b>Blocker Program Name </b></td>' + '<td align=center><b>Blocker status</b></td>' + '<td align=center><b>Blocker Db Name</b></td>' + '<td align=center><b> Blocker Command Text</b></td>' + '<td align=center><b> Victim SPID </b></td>' + 
			'<td align=center><b>Victim Last Batch</b></td>' + '<td align=center><b> Victim Open Tran </b></td>' + '<td align=center><b> Victim Wait Time_hh:mm:ss </b></td>' + '<td align=center><b> Victim Login Name </b></td>' + '<td align=center><b> Victim Host Name</b></td>' + '<td align=center><b> Victim Program Name </b></td>' + '<td align=center><b> Victim status</b></td>' + '<td align=center><b> Victim Db Name</b></td>' + '<td align=center><b> Victim Command Text</b></td></tr>';

		SELECT @Body = (
				SELECT [ServerName] AS [TD]
					,[Blocker SPID] AS [TD]
					,[Blocker Last Batch] AS [TD]
					,[Blocker Open Tran] AS [TD]
					,[Blocked For_hh:mm:ss] AS [TD]
					,[Blocker Login Name] AS [TD]
					,[Blocker Host Name] AS [TD]
					,[Blocker Program Name] AS [TD]
					,[Blocker status] AS [TD]
					,[Blocker Db Name] AS [TD]
					,[Blocker Command Text] AS [TD]
					,[Victim SPID] AS [TD]
					,[Victim Last Batch] AS [TD]
					,[Victim Open Tran] AS [TD]
					,[Victim Wait Time_hh:mm:ss] AS [TD]
					,[Victim Login Name] AS [TD]
					,[Victim Host Name] AS [TD]
					,[Victim Program Name] AS [TD]
					,[Victim status] AS [TD]
					,[Victim Db Name] AS [TD]
					,[Victim Command Text] AS [TD]
				FROM #temp_requests
				FOR XML raw('tr')
					,Elements
				)

		SET @Body = Replace(@Body, '_x0020_', space(1))
		SET @Body = Replace(@Body, '_x003D_', '=')
		SET @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
		SET @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
		SET @Body = @TableHead + @Body + @TableTail

		EXEC msdb.dbo.sp_send_dbmail @profile_name = ''
			,-- replace with your SQL Database Mail Profile 
			@body = @body
			,@body_format = 'HTML'
			,@recipients = ''
			,-- replace with your email address
			@subject = 'Blocking Detected';

		--EXEC msdb.dbo.sysmail_help_profile_sp;
		DROP TABLE #temp_requests
	END
END

Step 3: Schedule a SQL Server Agent job to execute the Stored Procedure created(in step 2) to run every 15 minutes

Done!! I hope this helps 😊

Password Expiry Notification for SQL Logins

Most of the Organisation enforces password expiration policies to manage the lifespan of a password. When SQL Server enforces password expiration policy, users must reset the password. Steps to set up an automated email notification 7 days prior to the actual date of expiry are:-

Step 1: Configure Database Mail 

You can go here to setup DatabaseMail if you don't have one already 

Step 2: Create Stored Procedure in the DBA utility database or any database using the below script. The script makes use of the SQL Server DatabaseMail & SQL Server Agent feature to send Password Expiration Notification either to the Database Team or Application Team 1 week before the expiry date.

 

USE DBA_DB --Change Database Name
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PasswordExpiration_SP]
AS
IF object_id('tempdb..#Password') IS NOT NULL
	DROP TABLE #Password

DECLARE @count INT

SELECT @@Servername AS ServerName
	,'ITG' AS Env
	,SL.name AS LoginName
	,LOGINPROPERTY(SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration
	,LOGINPROPERTY(SL.name, 'IsExpired') AS IsExpired
INTO #Password
FROM sys.sql_logins AS SL
WHERE is_expiration_checked = 1
	AND is_disabled = 0
	AND sl.name NOT LIKE '##%'
	AND DATEDIFF(dd, getdate(), DATEADD(DAY, CAST(LOGINPROPERTY(SL.name, 'DaysUntilExpiration') AS INT), GETDATE())) < 7 --Change the number of days here

SET @Count = @@ROWCOUNT

--select * from #Password 
IF @Count > 0
BEGIN
	BEGIN
		-- Email Querry--
		DECLARE @Body VARCHAR(max)
		DECLARE @TableHead VARCHAR(max)
		DECLARE @TableTail VARCHAR(max)
		DECLARE @mailitem_id AS INT
		DECLARE @statusMsg AS VARCHAR(max)
		DECLARE @Error AS VARCHAR(max)
		DECLARE @Note AS VARCHAR(max)

		SET NOCOUNT ON;
		SET @mailitem_id = NULL
		SET @statusMsg = NULL
		SET @Error = NULL
		SET @Note = NULL
		SET @TableTail = '</table>
<br/>Request you to reset the password before it expires<br/>
<br/>Thanks<br/>DB Team</body></html>';
		--HTML layout--
		SET @TableHead = '<html><head>' + 'Hello,<br/>
<br/>
Greetings from DBA Team. <br/>
<br/>
Please reset password of below account(s). <br/>
<br/>
' + '<style>' + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' + '</style>' + '</head>' + '<body><table cellpadding=0 cellspacing=0 border=0>' + '<tr bgcolor=#F6AC5D>' + '<td align=center><b>ServerName</b></td>' + '<td align=center><b>Env</b></td>' + '<td align=center><b>LoginName</b></td>' + '<td align=center><b>DaysUntilExpiration</b></td>' + '<td align=center><b>IsExpired</b></td></tr>';

		--Select information for the Report-- 
		SELECT @Body = (
				SELECT [ServerName] AS [TD]
					,[Env] AS [TD]
					,[LoginName] AS [TD]
					,[DaysUntilExpiration] AS [TD]
					,[IsExpired] AS [TD]
				FROM #Password
				FOR XML raw('tr')
					,Elements
				)

		--convert(varchar(19), [PasswordLastSetTime] + ' ' + @modtime, 20)
		-- Replace the entity codes and row numbers
		SET @Body = Replace(@Body, '_x0020_', space(1))
		SET @Body = Replace(@Body, '_x003D_', '=')
		SET @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
		SET @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
		SET @Body = @TableHead + @Body + @TableTail

		-- return output--
		--Email
		EXEC msdb.dbo.sp_send_dbmail @recipients = 'mail_ID1;mail_ID2'
			,--Specify the mail ID's for which notification is required
			@profile_name = ''
			,-- Specify the Profile name
			@subject = 'Please Reset the password '
			,@body = @Body
			,@body_format = 'HTML';

		DROP TABLE #Password
	END
END

Step 3: Schedule a SQL Server Agent job to execute the Stored Procedure created(in step 2) to run daily

Done!! I hope this helps 😊