So, since it has been some time since we looked at our Security Audit, let’s review what we’ve gone over so far:
On our next look at the Security Audit, let’s look at the server itself –
SELECT COUNT(*) FROM sys.servers WHERE is_linked ='1'
This short block of code will show us what servers we are linked to. These should be validated that we definitely do want and/or need access to these servers for a legitimate reason via SQL. Since there are so many ways to bring data into a server now – PowershellSSIS – that it can be considered a red flag if there is a linked server listed on the server. Also, it should be noted that this only shows what servers we are linked to, not what servers are linked to us.
Now, this is not to say that all linked servers are bad. A potential for users to have more permissions than necessary is extremely possible. This should be noted in your security audit for this very reason.
Now for one of my favorite pieces of code that should scare auditors.
SELECT Name as 'Configuration Name'
, CONVERT (NVARCHAR(6),[VALUE]) as 'Configured Value'
, CONVERT (NVARCHAR(6),[VALUE_IN_USE]) as 'Value in Use'
, CASE (CONVERT (NVARCHAR(15),[is_dynamic]))
WHEN 0 THEN CAST('Service Restart Needed' as VARCHAR(25))
WHEN 1 THEN CAST('Change is Immediate' as VARCHAR(25))
END as ' Change Effect'
, CONVERT (NVARCHAR(80),[Description]) as 'Description'
FROM SYS.CONFIGURATIONS
GO
This code block shows many of the configurations setup by default and if they were changed by someone, to what the new value is currently. Now, this is a decent sized list – on SQL Server 2016 SP1, I show 76 rows – but all of them should be reviewed and validated.
And so, with that, I leave you to continue your security audits…