Security Audit – Part 4

So, since it has been some time since we looked at our Security Audit, let’s review what we’ve gone over so far:

And We’re Back!

Security Audit – Part 2

Security Audit

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…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s