forked from ktaranov/sqlserver-kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
CheckSpectreMeltdownStatus.sql
238 lines (192 loc) · 9.11 KB
/
CheckSpectreMeltdownStatus.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
-- Check Spectre/Meltdown Status for SQL Server
-- Glenn Berry
-- SQLskills.com
-- Last Modified: January 23, 2018
--******************************************************************************
--* Copyright (C) 2018 Glenn Berry, SQLskills.com
--* All rights reserved.
--*
--* For more scripts and sample code, check out
--* https://www.sqlskills.com/blogs/glenn
--*
--* You may alter this code for your own *non-commercial* purposes. You may
--* republish altered code as long as you include this copyright and give due credit.
--*
--*
--* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
--* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
--* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--* PARTICULAR PURPOSE.
--*
--******************************************************************************
-- Determine if SQL Server has been patched against Meltdown/Spectre
-- This only checks the SQL Server patch status, not the operating system or hardware
-- This works for SQL Server 2008 through SQL Server 2017
-- You can use a Microsoft-provided PowerShell script to check your
-- operating system and hardware patch status as decribed in this Microsoft KB
-- Windows Client Guidance for IT Pros to protect against speculative execution side-channel vulnerabilities
-- https://support.microsoft.com/en-us/help/4073119/protect-against-speculative-execution-side-channel-vulnerabilities-in
SET NOCOUNT ON;
-- Declare variables
DECLARE @MachineIdentification varchar(128) = '';
DECLARE @ProductVersion varchar(20) = '';
DECLARE @ProductBuild int = 0;
DECLARE @PatchStatus varchar(15) = 'not patched';
DECLARE @FinalPatchMessage varchar(256) = '';
SET @MachineIdentification = 'Server Name = ' + CONVERT(varchar(50), SERVERPROPERTY ('MachineName')) + ', Instance Name = ' + ISNULL(CONVERT(varchar(50), SERVERPROPERTY('InstanceName')), 'None') + ': ';
-- Figure out major version number and then get the build number
-- SQL Server 2008
IF EXISTS (SELECT * WHERE CONVERT(varchar(20), SERVERPROPERTY('ProductVersion')) LIKE '10.0%')
BEGIN
SET @ProductVersion = CONVERT(varchar(20), SERVERPROPERTY('ProductVersion'));
IF @ProductVersion = '10.0.6556.0'
BEGIN
SET @PatchStatus = 'patched';
END
SET @FinalPatchMessage = @MachineIdentification + N'SQL Server 2008, Build Number ' + @ProductVersion + '. This SQL Server instance is ' + @PatchStatus + ' against Spectre/Meltdown.';
PRINT @FinalPatchMessage;
SELECT @FinalPatchMessage AS [SQL Server Patch Status];
END
-- Description of the security update for SQL Server 2008 SP4 GDR: January 6, 2018
-- https://support.microsoft.com/en-us/help/4057114
-- Build 10.0.6556.0
-- SQL Server 2008 R2
IF EXISTS (SELECT * WHERE CONVERT(varchar(20), SERVERPROPERTY('ProductVersion')) LIKE '10.5%')
BEGIN
SET @ProductVersion = CONVERT(varchar(20), SERVERPROPERTY('ProductVersion'));
IF @ProductVersion = '10.50.6560.0'
BEGIN
SET @PatchStatus = 'patched';
END
SET @FinalPatchMessage = @MachineIdentification + N'SQL Server 2008 R2, Build Number ' + @ProductVersion + '. This SQL Server instance is ' + @PatchStatus + ' against Spectre/Meltdown.';
PRINT @FinalPatchMessage;
SELECT @FinalPatchMessage AS [SQL Server Patch Status];
END
-- Description of the security update for SQL Server 2008 R2 SP3 GDR: January 6, 2018
-- https://support.microsoft.com/en-us/help/4057113
-- 10.50.6560.0
-- SQL Server 2012
-- We have ProductBuild in SQL Server 2012 and newer
IF EXISTS (SELECT * WHERE CONVERT(varchar(20), SERVERPROPERTY('ProductVersion')) LIKE '11%')
BEGIN
SET @ProductVersion = CONVERT(varchar(20), SERVERPROPERTY('ProductVersion'));
SET @ProductBuild = CONVERT(int, SERVERPROPERTY('ProductBuild'));
IF @ProductBuild >= 7462
BEGIN
SET @PatchStatus = 'patched';
END
SET @FinalPatchMessage = @MachineIdentification + N'SQL Server 2012, Build Number ' + @ProductVersion + '. This SQL Server instance is ' + @PatchStatus + + ' against Spectre/Meltdown.';
PRINT @FinalPatchMessage;
SELECT @FinalPatchMessage AS [SQL Server Patch Status];
END
-- Description of the security update for SQL Server 2012 SP4 GDR: January 12, 2018
-- https://support.microsoft.com/en-us/help/4057116
-- Build 11.0.7462.6
-- SQL Server 2014
IF EXISTS (SELECT * WHERE CONVERT(varchar(20), SERVERPROPERTY('ProductVersion')) LIKE '12%')
BEGIN
SET @ProductVersion = CONVERT(varchar(20), SERVERPROPERTY('ProductVersion'));
SET @ProductBuild = CONVERT(int, SERVERPROPERTY('ProductBuild'));
IF @ProductBuild >= 5571 -- SP2 CU
BEGIN
SET @PatchStatus = 'patched';
END
IF @ProductBuild = 5214 -- SP2 GDR
BEGIN
SET @PatchStatus = 'patched';
END
SET @FinalPatchMessage = @MachineIdentification + N'SQL Server 2014, Build Number ' + @ProductVersion + '. This SQL Server instance is ' + @PatchStatus + + ' against Spectre/Meltdown.';
PRINT @FinalPatchMessage;
SELECT @FinalPatchMessage AS [SQL Server Patch Status];
END
-- Description of the security update for SQL Server 2014 SP2 CU10: January 16, 2018
-- https://support.microsoft.com/en-us/help/4057117
-- Build 12.0.5571.0
-- Description of the security update for SQL Server 2014 SP2 GDR: January 16, 2018
-- https://support.microsoft.com/en-us/help/4057120
-- Build 12.0.5214.6
-- SQL Server 2016
IF EXISTS (SELECT * WHERE CONVERT(varchar(20), SERVERPROPERTY('ProductVersion')) LIKE '13%')
BEGIN
SET @ProductVersion = CONVERT(varchar(20), SERVERPROPERTY('ProductVersion'));
SET @ProductBuild = CONVERT(int, SERVERPROPERTY('ProductBuild'));
IF @ProductBuild >= 4466 -- SP1 CU7
BEGIN
SET @PatchStatus = 'patched';
END
IF @ProductBuild = 4210 -- SP1 GDR
BEGIN
SET @PatchStatus = 'patched';
END
IF @ProductBuild = 2218 -- RTM CU
BEGIN
SET @PatchStatus = 'patched';
END
IF @ProductBuild = 1745 -- RTM GDR
BEGIN
SET @PatchStatus = 'patched';
END
SET @FinalPatchMessage = @MachineIdentification + N'SQL Server 2016, Build Number ' + @ProductVersion + '. This SQL Server instance is ' + @PatchStatus + + ' against Spectre/Meltdown.';
PRINT @FinalPatchMessage;
SELECT @FinalPatchMessage AS [SQL Server Patch Status];
END
-- Description of the security update for SQL Server 2016 CU7 SP1: January 3, 2018
-- https://support.microsoft.com/en-us/help/4058561
-- Build 13.0.4466.4
-- Description of the security update for SQL Server 2016 GDR SP1: January 3, 2018
-- https://support.microsoft.com/en-us/help/4057118
-- Build 13.0.4210.6
-- Description of the security update for SQL Server 2016 CU: January 6, 2018
-- https://support.microsoft.com/en-us/help/4058559
-- Build 13.0.2218.0
-- Description of the security update for SQL Server 2016 GDR: January 6, 2018
-- https://support.microsoft.com/en-us/help/4058560
-- Build 13.0.1745.2
-- SQL Server 2017
IF EXISTS (SELECT * WHERE CONVERT(varchar(20), SERVERPROPERTY('ProductVersion')) LIKE '14%')
BEGIN
SET @ProductVersion = CONVERT(varchar(20), SERVERPROPERTY('ProductVersion'));
SET @ProductBuild = CONVERT(int, SERVERPROPERTY('ProductBuild'));
IF @ProductBuild >= 3015 -- 2017 CU3
BEGIN
SET @PatchStatus = 'patched';
END
IF @ProductBuild = 2000 -- 2017 GDR
BEGIN
SET @PatchStatus = 'patched';
END
SET @FinalPatchMessage = @MachineIdentification + N'SQL Server 2017, Build Number ' + @ProductVersion + '. This SQL Server instance is ' + @PatchStatus + + ' against Spectre/Meltdown.';
PRINT @FinalPatchMessage;
SELECT @FinalPatchMessage AS [SQL Server Patch Status];
END
-- Description of the security update for SQL Server 2017 GDR: January 3, 2018
-- https://support.microsoft.com/en-us/help/4057122
-- Build 14.0.2000.63
-- Description of the security update for SQL Server 2017 CU3 RTM: January 3, 2018
-- https://support.microsoft.com/en-us/help/4058562
-- Build 14.0.3015.40
-- SQL Server Guidance to protect against speculative execution side-channel vulnerabilities
-- https://support.microsoft.com/en-us/help/4073225/guidance-for-sql-server
-- ToDo: Check for clr status, check for R Server, check for linked servers,etc.
---- Get selected instance-level configuration values that relate to SQL Server extensibility features
SELECT name AS [Configuration Name], value as [Value],
value_in_use AS [Value In Use], description
FROM sys.configurations
WHERE name IN (N'clr enabled', N'clr strict security',
N'external scripts enabled', N'Ole Automation Procedures',
N'xp_cmdshell')
ORDER BY name;
-- If clr enabled is 1, then strongly consider setting clr strict security to 1 (on new enough builds of SQL Server 2012, 2014, 2016, and 2017)
-- Update adds the "CLR strict security" feature to SQL Server 2016
-- https://support.microsoft.com/en-us/help/4018930/update-adds-the-clr-strict-security-feature-to-sql-server-2016
------ Check for Polybase and R Services
--SELECT SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled],
--SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [IsRServicesInstalled];
------ Linked servers
--EXEC sp_linkedservers;
--SELECT j.[name], s.step_name, s.subsystem
--FROM msdb.dbo.sysjobsteps AS s
--INNER JOIN msdb.dbo.sysjobs AS j
--ON s.job_id = j.job_id
--WHERE s.subsystem = N'ActiveScripting'
--GO