SQL Server System Audit Report

  • Rudy Panigas

    SSChampion

    Points: 10683

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

    Rudy

  • dainolib

    SSC Enthusiast

    Points: 127

    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

    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

    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: 15722

    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: 6121

    Superb Report. Very helpful...Thanks Rudy!

  • Adam Seniuk

    SSCrazy

    Points: 2171

    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

    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: 10683

    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

  • Rudy Panigas

    SSChampion

    Points: 10683

    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

  • Rudy Panigas

    SSChampion

    Points: 10683

    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

  • Rudy Panigas

    SSChampion

    Points: 10683

    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

    Rudy

  • dainolib

    SSC Enthusiast

    Points: 127

    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

    SSC Eights!

    Points: 864

    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: 10683

    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

    Rudy

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

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