SQL Server System Audit Report

Question

Post reply

SQL Server System Audit Report

Rudy Panigas

SSChampion

Points: 10702
More actions
February 1, 2016 at 11:13 pm

#315470

Comments posted to this topic are about the item SQL Server System Audit Report
Rudy

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

dainolib SSC Enthusiast Points: 127 More actions · Answer 1

Section "Remote Admin Connections" is not correct.

1. This option is not related to restriction on local administrator but on local connections

2. The recommended value is 0 (only local connections are allowed by using the DAC) and not 1

Jefferson Elias Ten Centuries Points: 1379 More actions · Answer 2

Hi, thanks for sharing.

This seems interesting but it doesn't work against a 2008 R2 instance.

Reason:

Msg 207, Level 16, State 1, Line 245

Invalid column name 'physical_memory_kb'.

You could use an sp_executesql based on the version you get as the column name is 'physical_memory_in_bytes' for versions below 2012.

Best regards.

Jeff'.

r.kaligis 40194 Valued Member Points: 57 More actions · Answer 3

Thanks, It's a great script!

and thanks dainolib for pointing out the incorrect part about Remote Admin, saves me an audit issue 🙂

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 4

There are couple of bugs in this script, but otherwise it's very thorough, very comprehensive security settings as well as recommendations report.

If not bugs, would put 5 stars certainly.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 5

Sreekanth B

SSCertifiable

Points: 6145

February 2, 2016 at 12:56 pm

#1856089

Superb Report. Very helpful...Thanks Rudy!

Adam Seniuk SSCrazy Points: 2281 More actions · Answer 6

Hey this is a great tool for a security conscious DBA!

I have one suggestion, for the SA check could you maybe add an additional check to see if the SA account has been renamed as a security measure.

Thank you for this tool.

Over 12yrs in IT and 10yrs happily stuck with SQL.
- SQL 2008/R2/2012/2014/2016/2017
- Oracle 8/9/10/11
- MySQL 4/5 and MariaDB

shadighaith Grasshopper Points: 23 More actions · Answer 7

Good day, the script does not work with SQL 2008 R2. Multiple errors shown that require to be resolved one by one. Do you have an older version for 2008 R2? Thanks,

Rudy Panigas SSChampion Points: 10702 More actions · Answer 8

shadighaith (2/3/2016)
Good day, the script does not work with SQL 2008 R2. Multiple errors shown that require to be resolved one by one. Do you have an older version for 2008 R2? Thanks,

I am currently working on a version of this script for SQL 2008/2008R2. This script is only for SQL versions 2012 and 2014.

Thanks,

Rudy

Rudy Panigas SSChampion Points: 10702 More actions · Answer 9

Jefferson Elias (2/2/2016)
Hi, thanks for sharing.
This seems interesting but it doesn't work against a 2008 R2 instance.
Reason:
Msg 207, Level 16, State 1, Line 245
Invalid column name 'physical_memory_kb'.
You could use an sp_executesql based on the version you get as the column name is 'physical_memory_in_bytes' for versions below 2012.
Best regards.
Jeff'.

Hi Jeff,

Did you see the name of the file and it's contents? The script is called SASAT-Analysis and Audit of SQL Server 2012-2014-ver4.9.sql and doesn't work for earlier versions. I am working on another script for older versions of SQL and will post when ready.

Thanks,

Rudy

Rudy Panigas SSChampion Points: 10702 More actions · Answer 10

dainolib (2/2/2016)
Section "Remote Admin Connections" is not correct.
1. This option is not related to restriction on local administrator but on local connections
2. The recommended value is 0 (only local connections are allowed by using the DAC) and not 1

Yes you are correct. the display is showing the wrong value.

Thanks for point this out.

Rudy

Rudy Panigas SSChampion Points: 10702 More actions · Answer 11

SQL Guy 1 (2/2/2016)
There are couple of bugs in this script, but otherwise it's very thorough, very comprehensive security settings as well as recommendations report.
If not bugs, would put 5 stars certainly.

Thanks SQL Guy. Can you tell me what bugs you have found? I would like to make whatever changes needed and repost the script.

Thanks,

Rudy

dainolib SSC Enthusiast Points: 127 More actions · Answer 12

Very good script!

It works on Sql Server 2008 and 200R2 just replacing the line

--SET @TotalMEMORYinBytes = CONVERT(NVARCHAR(10),(select physical_memory_kb from sys.dm_os_sys_info))

With

CREATE TABLE #SASAMemory (TotalMEMORYinBytes NVARCHAR(10));

DECLARE @sqlToExecute AS NVARCHAR(300)

IF (@ProductVersion LIKE 'SQL Server 2008%')

-- Sql Server 2008 and 2008R2

SET @sqlToExecute = 'SELECT CONVERT(NVARCHAR(10),(select physical_memory_in_bytes/1024 from sys.dm_os_sys_info))'

IF (@ProductVersion LIKE 'SQL Server 2012%') OR (@ProductVersion LIKE 'SQL Server 2014%') OR (@ProductVersion LIKE 'SQL Server 2016%')

-- Sql Server 2012 and above

SET @sqlToExecute = 'SELECT CONVERT(NVARCHAR(10),(select physical_memory_kb from sys.dm_os_sys_info))'

SET @sqlToExecute = 'INSERT INTO #SASAMemory ' + @sqlToExecute

EXECUTE sp_executesql @sqlToExecute

SELECT @TotalMEMORYinBytes = TotalMEMORYinBytes FROM #SASAMemory

DROP TABLE #SASAMemory

terry.home Ten Centuries Points: 1004 More actions · Answer 13

Really like the script and wanted to thank you for this.

Not sure about turning database mail functionality off, appreciate that if anybody hacks system they can send email to anybody, but that requires emails to go via SMTP server where security on this can be restricted appropriately. Hopefully with all other security items enabled, the chances of anybody hacking into your system in the first place will be minimal.

Problem I have with turning it off is that I believe I use database mail to send error messages when there are problems with servers.

We manage around 70 SQL Servers, accross three domains and have already started writing something similar (but not as good) to take an audit of all servers and databases on them. Ideally I hope for script to be sceduled to run daily, then for results to be inserted into a database on an AUDIT server. At the end of the day, a report should be emailed if there are any FAILED security features that need to be addressed.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 14

terry.home (2/3/2016)
Really like the script and wanted to thank you for this.
Not sure about turning database mail functionality off, appreciate that if anybody hacks system they can send email to anybody, but that requires emails to go via SMTP server where security on this can be restricted appropriately. Hopefully with all other security items enabled, the chances of anybody hacking into your system in the first place will be minimal.
Problem I have with turning it off is that I believe I use database mail to send error messages when there are problems with servers.
We manage around 70 SQL Servers, accross three domains and have already started writing something similar (but not as good) to take an audit of all servers and databases on them. Ideally I hope for script to be sceduled to run daily, then for results to be inserted into a database on an AUDIT server. At the end of the day, a report should be emailed if there are any FAILED security features that need to be addressed.

Good question and the answer is that if you need a "very" secure system then you would turn this feature off. The recommendation are just that. You can easily show an auditor why you would need this feature left on. Now if you use a commercial product to monitor your environments and it has alerting built in, then you would turn off this feature. Just remember that the audit it just to show what your system has enabled and you may have exceptions that you can get approval once you have explained why you need it.

Glad you like the script and thanks for the feedback.

Rudy