February 3, 2016 at 5:01 pm
Very nice script!
Thanks
February 4, 2016 at 1:44 am
Thanks Rudy for sharing this very useful report. You did a great job!
As many of us, my servers did not pass all the checks 😉 This is not an issue for me, as I can argue why this one setting or this other one is turned on.
However, there are some other settings I think I would not need but I as well know from my experience that if 'this bit is turned like that there must be a hidden reason somewhere'.
In other words, I would like to avoid to hang Production Down, if I tune some security settings of the servers, because this script could not run successfully or this program could not access the database the way it does it since years now or this email warning the financial staff of some weird investment did not reach its receipients.
IMO this report is a very useful one that I will certainly use to get a clear picture of my SQL servers' properties and settings. But I think that trying to reduce the warnings will need some extra work and cautious efforts ...
Again thanks for sharing !!!
🙂
February 4, 2016 at 6:30 am
Great script, thank you for sharing!
On top of the Remote DAC concerns others posted, there's one small typo I spotted:
linie 1071 has "XP_fixdrdrives Enabled for PUBLIC".
Your work gave me insight into a couple of securables I had not considered before - I appreciate the help!
February 5, 2016 at 3:22 pm
Perhaps you did it intentionally, and if so, totally disregard the rest of this message, but SQL Server 2016 is looking to be Version 13, right?
If so, line 220 would need a very light modification.
Other than that, pretty nice, thanks for the work you've put into it.
February 8, 2016 at 6:37 am
patrickpartin (2/5/2016)
Perhaps you did it intentionally, and if so, totally disregard the rest of this message, but SQL Server 2016 is looking to be Version 13, right?If so, line 220 would need a very light modification.
Other than that, pretty nice, thanks for the work you've put into it.
SQL Server 2016 detection was just a guess as it is not RTM. The version may be 13 or not and can easily be changed later.
Thanks for point that out.
Rudy
Rudy
February 19, 2016 at 3:49 am
You could always take a look at MS Baseline Security Analyser. Not fantastic, but useful.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
February 19, 2016 at 7:39 am
Ness (2/19/2016)
You could always take a look at MS Baseline Security Analyser. Not fantastic, but useful.
Yes good point. However, by the time you install it, this script would have been executed and results displayed.
Thanks,
Rudy
Rudy
February 19, 2016 at 7:56 am
You can run it against remote servers 🙂
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
February 19, 2016 at 8:08 am
You sure can. As in most things, there are many ways to get a job done.
Rudy
March 4, 2016 at 2:23 pm
"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."
Does this apply to PCI audit as well?
Thanks
Jeff
March 8, 2016 at 4:13 pm
Thanks for the script.
February 14, 2018 at 7:39 am
Rudy Panigas - Wednesday, February 3, 2016 7:18 AMJefferson 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 245Invalid 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
Hello I made a change to solve this issue :
replace the line 245 in the 4.9 scriptSET @TotalMEMORYinBytes = CONVERT(NVARCHAR(10),(select physical_memory_kb from sys.dm_os_sys_info))
with
DECLARE @vQuery NVARCHAR(1000)
IF @ProductVersion>='SQL Server 2012'
BEGIN
SET @vQuery = N'select @TotalMEMORYinBytes=convert(NVARCHAR(10),physical_memory_kb) from sys.dm_os_sys_info'
EXEC SP_EXECUTESQL
@Query = @vQuery
, @params = N'@TotalMEMORYinBytes NVARCHAR(10) OUTPUT'
, @TotalMEMORYinBytes = @TotalMEMORYinBytes OUTPUT
END
ELSE
BEGIN
SET @vQuery = N'select @TotalMEMORYinBytes=convert(NVARCHAR(10),physical_memory_in_bytes/(1024)) from sys.dm_os_sys_info'
EXEC SP_EXECUTESQL
@Query = @vQuery
, @params = N'@TotalMEMORYinBytes NVARCHAR(10) OUTPUT'
, @TotalMEMORYinBytes = @TotalMEMORYinBytes OUTPUT
END
April 8, 2019 at 4:49 pm
Hi!
Maybe I miss something but where is the script?
Vj
April 11, 2019 at 2:01 pm
Since this site changed the look and feel the scripts are not with the article. I have send the script to the site and believe it will be under the script section. Will try to figure out how to contact the admin here.
Sorry for the delay
Rudy
April 25, 2019 at 11:44 pm
Since this site changed the look and feel the scripts are not with the article. I have send the script to the site and believe it will be under the script section. Will try to figure out how to contact the admin here. Sorry for the delay
The script is not executable at all. It has a lot of bugs. It looks like that someone converted it to some kind of HTML/XML.
">" is replaced by ">"
IF (SELECT COUNT(*) FROM #CLRAssemblyPermission) > 0
"=" is missing
IF @InstanceName 'Default Instance'
etc.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply