none
SQL Server 2012 STIG

    General discussion

  • Hi, I'm looking for expertise from the DBAs out there. My background is not in SQL so, some things I just haven't been able to get figured out.

    1. Too many Vulnid's to list - Steps to setup TDE using PKI Certificates. (Cert provided in .p12 format initially)

    2. Vulnid:SQL2-00-015300 -  SQL Server method of monitoring security-relevant configuration settings to discover unauthorized changes.

    3. Vulnid:SQL2-00-014900, SQL2-00-015100, SQL2-00-015200 - SQL Server timed job that automatically checks all system and user-defined Functions, Triggers, and Stored Procedures for being modified. (With notifications)

    I know the STIG is Draft, I still need to secure these items though. Any Barney style help on these items would be greatly appreciated.

    Thanks in advance!

    Friday, November 01, 2013 4:59 PM

All replies

  • For other readers: STIG = Security Technical Implementation Guide

     

    Let me step back a little:

    What is your actual concern?

    "too many vulnid's"?

    Which vulnerabilities are you referring to? The list is public.

    The latest security hole inside the Microsoft SQL Server engine is back from version 2005 (!)

    -> We are not at a Oracle Forum here ;)  (I am totally serious actually :)

    Of course this does not mean, SQL Server cannot be attacked, but

    -> What exactly do you want/need to protect against?

    While encryption of databases becomes more and more common, I have barely ever seen a customer actually monitoring system objects (they are quite well protected in SQL Server anyways)

    Actually I have kind of a list of possible attack scenarios on SQL Server listed there: Security Session „SQL Attack..ed“ – Attack scenarios on SQL Server ("Hacking" SQL Server)

    After all, security can be a very broad topic, so I would advise to first concentrate on the requirements given.



    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Friday, November 01, 2013 9:26 PM
  • The latest security hole inside the Microsoft SQL Server engine is back from version 2005 (!)

    There have been security fixes released for SQL 2008 and later, although they may not relate to the engine.

    I was recently made aware of a pretty security issue which exists in all versions, although it's not one that can be exploited from the outside, but requires that you have contact with the DBA one way or another. And the DBA can take precautions for it not to happen. As I understand, Microsoft will not address it.

    While encryption of databases becomes more and more common, I have barely ever seen a customer actually monitoring system objects (they are quite well protected in SQL Server anyways)

    Certainly, many item on such lists fall are somewhat silly. A while back there were was a rush of posts from people wanted to revoke permissions to system procedure, but were not able to revoke permissions procedures for the PDW. The procedure were non-existing, but they had slipped into the system tables, causing some confusion.

    However, security is about being paranoid. There is no known hole through which an intruder can modify the system objects in SQL Server. But suddenly one such hole is found and exploited. If you have a check, you will find out sooner. (And it's is not that difficult to achieve: the intruder gains access to SQL Server one way or another, and manages to replace the resource database.)

    And while there are not true security holes in the product as such, the product permits you to create your own security by casually assigning permissions to persons and databases that should not be trusted.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, November 01, 2013 10:38 PM
  • Hi Andreas, 

    When I said too many Vulnids on the first item it was just that the STIG mentions encrypting data at rest about 20 different times in twenty different ways. Since you seem familiar with STIGS I'm sure you know what I mean, they're far from efficient documents.

    So, for item one the basic requirement is to secure the data, in-place/at rest. I wanted to use TDE for this purpose but then the STIGS mention if TDE is used then a DoD PKI Cert must be used. I'm not very well versed in this stuff though and probably do more harm then good by fumbling through it all in the KBs. Long story short, I have not been able to figure out how to get TDE working where a DoD PKI Cert is involved.

    Items two and 3 I assume would require some scripts, I just don't know where to even start on creating them or making them run. Apparently I can't post links or I'd link the STIG. It can be gotten from the disa STIG site though under application Security->Database->SQL Server. Then the vulnids can be searched for in that.


    Hopefully I'm in the right place, you guys seem to be way above my head.

    Friday, November 01, 2013 11:39 PM
  • Ok, so for other readers or search engines:

    You can find the current SECURITY TECHNICAL IMPLEMENTATION GUIDEs (STIG) for Microsoft SQL Server, developed for the DoD (Department of Defense) at the Defense Information Systems Agency (DISA) website: http://iase.disa.mil/stigs/app_security/database/sql.html

    For TDE you cannot use p12 certificate in .p12 format (PFX / PKCS #12) directly. You may be able to convert it – but I have no experience personally with that.

    Maybe you can use this as a starting point for TDE: Guidelines on Managing Certificates used in Transparent Data Encryption

    Rule Version (STIG-ID):  SQL2-00-015300 Rule Title: SQL Server must monitor for security-relevant configuration settings to discover unauthorized changes.

    “-…

    Verify within the system documentation that SQL Server is monitoring for security-relevant configuration settings to discover unauthorized changes. This can be done by a third-party tool or a SQL script that does baselining and then comparisons.

    …”

    If I read correctly, this is somewhat blurry. Can be all or nothing, really. You can use Policy Based Management, plain Triggers, Auditing or buy 3<sup>rd</sup> Party Software, to make it harder to disallow Administrators making undetected changes plus various combination of the techniques.

     

    The other ones I didn’t find, but based on your description it’s somehow clear.

    For validating User-defined code you could use signatures, or compare hashes/checksums, just monitor DDL statements or again use 3<sup>rd</sup> party tools

    Really many options and depending on how much effort you are supposed to put into. (there is always an artificial limit)

    For system-objects you can also compare checksums or compare exact copies. For the resource-database SQL Server actually has an in-build check -> SQL Server Error Log. Besides that I have once implemented a checksum-check via PowerShell.

    Which brings up the next item: who is checking the logs.

    My advice: get consulting from a security expert, at best he also has experience with STIG, if this is really critical.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, November 02, 2013 1:33 PM
  • The latest security hole inside the Microsoft SQL Server engine is back from version 2005 (!)

    >There have been security fixes released for SQL 2008 and later, although they may not relate to the engine.

    >I was recently made aware of a pretty security issue which exists in all versions, although it's not one that can be exploited from the outside, but requires that you have contact with the DBA one way or another. And the DBA can take precautions for it not to happen. >As I understand, Microsoft will not address it.

    While encryption of databases becomes more and more common, I have barely ever seen a customer actually monitoring system objects (they are quite well protected in SQL Server anyways)

    Certainly, many item on such lists fall are somewhat silly. A while back there were was a rush of posts from people wanted to revoke permissions to system procedure, but were not able to revoke permissions procedures for the PDW. The procedure were non-existing, but they had slipped into the system tables, causing some confusion.

    However, security is about being paranoid. There is no known hole through which an intruder can modify the system objects in SQL Server. But suddenly one such hole is found and exploited. If you have a check, you will find out sooner. (And it's is not that difficult to achieve: the intruder gains access to SQL Server one way or another, and manages to replace the resource database.)

    And while there are not true security holes in the product as such, the product permits you to create your own security by casually assigning permissions to persons and databases that should not be trusted.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thank you for making clear, that of course, while the SQL Server engine itself may not have security holes, the system a such of course could still be wide open.

    Especially if someone has access to an administrative account of course.

    I would be curious to know more about that security issue that you mention.

    If it only works with sysadmin I can quite well imagine that answer from Microsoft :-D.. but if it is somehow "indirect", it would be interesting anyhow.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, November 02, 2013 1:37 PM
  • Thanks again for the response Andreas.

    I was hoping these were common enough things that someone else had already cleared these STIG hurdles.

    Do you have any recommendations for professional security consultants that would be able to assist with these items?

    Monday, November 04, 2013 12:37 PM
  • Thanks again for the response Andreas.

    I was hoping these were common enough things that someone else had already cleared these STIG hurdles.

    Do you have any recommendations for professional security consultants that would be able to assist with these items?

    You're welcome. It's an interesting subject - never had seen those specific STIG's before.

    I talked to a fellow MCM in the US, who is also in security. He also says that he hasn't been asked for that specifically, but he might be able to help you decide what you really need to do.

    So obviously I am not recommending myself for that reason of locality ;-)

    Nevertheless it would be best if you just drop me an Email - you'll find many ways to reach me in my Profile - and I'll happily forward his contact details to you.


    Good luck

    Andreas


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, November 04, 2013 6:27 PM
  • Hi Andreas,

    I sent you a message on facebook. I think it sent but it's likely hidden since we're not friends.

    Worst case, send me an email to theerminespam at gmail and I'll sift through to find it then email you from a real account.

    Tuesday, November 05, 2013 9:26 PM
  • Got it. "No problemo".  ;-)


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, November 05, 2013 10:17 PM
  • BernerJ,

    I too am having issues with some of the same STIG's you mentioned above. If you were able to resolve them could you provide me any assistance?

    Thank you

    David

    Tuesday, November 19, 2013 5:15 PM
  • BernerJ,

    I too am having issues with some of the same STIG's you mentioned above. If you were able to resolve them could you provide me any assistance?

    Thank you

    David

    Hi David,

    Sorry for the delayed response, I've been out of the office for the last month.

    I was able to resolve my issues, but part of it required me hiring a consultant to develop working scripts. The TDE ended up being pretty simple once I was able to figure out the certificate request.

    Let me know the specific issues you're dealing with and I'll see if I can provide some assistance.

    Wednesday, December 18, 2013 1:19 PM
  • Does an auditing tool such as TFS count as a method to track these unauthorized changes?  If so, what specifics might need to be in place in order to qualify it as an auditing device? 

    Example:  (Assume tracking changes with Red Gate) Take advantage of the Red Gate library to trigger an alert.   (or with Microsoft tools if this is possible).

    Alternative: Keep all functions and procedures in a job file schema that are used by jobs and set triggers when schema changes are made.


    R, J

    Friday, January 17, 2014 8:59 PM
  • I don't know much about STIG, but I doubt that TFS alone counts, since TFS requires an active action by the person making for any thing to be tracked.

    And I would not call TFS an auditing tool, it's a source-control tool, which is something quite different.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 17, 2014 10:59 PM
  • STIG - Security Technical Implementation Guide

    I should define the terms.

    Red Gate has a local package running that does some scanning ("auditing") of the differences in the database against an image (snapshot) on the local machine.  Red Gate connects to TFS and uses the baseline established there, captures the change management using the local "diff" snapshots and combined with the history on TFS, you get a more complete picture.  The database is "shared" meaning updates do not go through TFS but history shows based on usage comparisons at the local machine level where the dba works from.  There are session snapshots listed under the profile (programdata\red gate) which track changes made against the last check-in on the baseline.  

    If the changes were mined properly, they could function as an audit, unfortunately, I think it must happen all at the server level.  The requirement is to send an alert when functions, procs, etc that are referenced by jobs undergo any changes.  There is really no tool I am aware of that does this sort of auditing.


    R, J

    Sunday, January 19, 2014 2:17 PM
  • Again, I am unfamiliar with STIG (I believe it comes from the US Department of Defence, why it also something which is not of direct concern to me).

    But I still see problem from an auditing perspective. With the method you describe, you would be able to track that changes occurred between distinct snapshots. But you would not be able to track:

    1) Who performed a certain change.
    2) The exact point it happened.
    3) Temporary change like disabling a trigger for a certain operation and enabling it again between the snapshots.

    Your method also opens the question how to track that local database you compare with, and not talking of the TFS database, which also is an SQL Server database.

    But, again, I don't know what's in STIG, so I cannot say whether the points I raise are of any concert for the STIG authors.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 19, 2014 6:38 PM
  • STIG - Security Technical Implementation Guide

    I should define the terms.

    Red Gate has a local package running that does some scanning ("auditing") of the differences in the database against an image (snapshot) on the local machine. ...

    If the changes were mined properly, they could function as an audit, unfortunately, I think it must happen all at the server level. ..

    As Erland noted, the STIG comes from the DoD and is very specific therefore.

    In comment #4 from the top, I a providing a link, where you can actually download the STIGs for SQL Server. In fact there was a new release for SQL Server 2012 just this month.. here it is: http://iase.disa.mil/stigs/app_security/database/sql.html

    And if you study it, you will find quotes like this one:

    “…In addition to these protections, auditing must also be utilized in order to track system activity, assist in diagnosing system issues, and provide evidence needed for forensic investigations post security incident.

    Does this answer your question? – I should note, that there are many solutions outlined in the STIG as well.



    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com


    Sunday, January 19, 2014 6:54 PM
  • Andreas,

    You hit the nail on the head.  The question is "HOW"?


    R, J

    • Edited by Crakdkorn Monday, February 10, 2014 8:33 PM
    Sunday, January 19, 2014 6:56 PM
  • Andrea,

    You hit the nail on the head.  The question is "HOW"?


    R, J

    before I elaborate on that: did you read through it?
    Because I don't want to repeat what's already in there.

    Which part is unclear? - The STIG lists several options, many of which utilize built-in functionality like Auditing and even old-style sql-tracing. - So I can assure you, that it is not extremely sophisticated in itself - its rather the language used that makes it hard to understand where it's going... government agency, you know ;-)


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Sunday, January 19, 2014 7:09 PM
  • If it helps, here's a script that checks for modified stored procedures. It's set to an error of 16. I then have an alert set for 16 to email me there was a change. At that point someone from the auditors would need to look at the logs.

    declare @name nvarchar(1000)
    declare @names nvarchar(4000)

    DECLARE findstoredprocedurechanges CURSOR FOR
    SELECT name
    FROM sys.objects
    WHERE type = 'P'
    AND DATEDIFF(D,modify_date, GETDATE()) < 300

    set @names=''

    OPEN findstoredprocedurechanges

    FETCH NEXT FROM findstoredprocedurechanges INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- Email goes here. the name of the stored procedure will be in @name
    set @names = @names + '|' + @name + '| '


    FETCH NEXT FROM findstoredprocedurechanges INTO @name

    END

    if @names <> ''
    Begin
    set @names = 'Stored Procedure Changes='+@names
    RAISERROR (@names, 16, 1) WITH LOG
    End

    CLOSE findstoredprocedurechanges
    DEALLOCATE findstoredprocedurechanges

    As for auditing there's a script that's contained in one of the STIG's that will cover several TraceID's. Also to note, the script is not 100% correct in the STIG, however go to the 2008 SQL STIG and it's correct there. It's also possible to find it out on the web. 

    Monday, January 20, 2014 12:49 PM
  • Nice solution... There are a couple of procedures that get modified after the fact in SQL 2008 R2 (probably 2008 and so on backward - sp_MScleanmergepublisher, sp_MSrepl_startup for example).  Time stamp is different but the day is the same.  In my database the modifydate and createdate matched the day of the installation so I added that information to the query.  That provided two checks but if I were using replication, I might not be able to trust that the modification was made by Microsoft during installation rather than someone toying with the procedures after the fact, same day.   I like the solution - but is there any way to be 100% sure that the two modified files are done by Microsoft?  I'm not confident with this workaround (below).

    Declare @installdate varchar(8)

    Select @installdate=convert(varchar(8),modify_date.112) from sys.tables where name='MSReplication_options'

    where...   Convert(varchar(8),create_date,112)=convert(varchar(8),modify_date,112)

                   AND convert(varchar(8),create_date,112)=@installdate

    


    R, J

    Tuesday, January 21, 2014 7:56 PM
  • ...is there any way to be 100% sure that the two modified files are done by Microsoft?  I'm not confident with this workaround (below).

    ...

    

    I would agree with you.

    It's nice for general purposes, but for bullet-proof auditing it is too simple.

    It's not all-too hard to change the modify_date of system objects by a determined "hacker". Also what happens after Service Pack, Cumulative Update, Hotfix...?

    If you combine that solution with some sort of auditing, even the built-in technology, it gives more confidence. of course also Auditing can be "tricked", but there are more options to prevent that this happens unnoticed.

    In the end you may also want to compare Checksums of known valid objects (from a safe backup i.e.) against the ones in place on regular basis.. All not sufficient in itself, but may be part of a solution.


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Tuesday, January 21, 2014 8:18 PM
  • Using modify_date for auditing is a little iffy. You could use it for an initial sieve, but you would not know if there has been an actual change. For instance, one way to change modify_date is sp_recompile.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 21, 2014 10:30 PM
  • I'm going back to this again.  I may be reading more into this than is necessary but given the "system and user-defined triggers" portion and given the nature of the Red Gate tool that constantly searches against the databases for "any" changes from the baseline, I'm going to go on a limb and say that the Red Gate stuff does this monitoring.   (I put "any" in quotes because it only works if the databases are baselined in TFS with Red Gate tools.)  

    Am I missing what exactly it is that the wording refers to: "check <snip> for the timed job that automatically checks all system and user-defined Triggers..."  

    Here is the exact wording of the STIG requirement:

    Check Content:

    Check the SQL Server configuration for the timed job that automatically checks all system and user-defined Triggers for being modified by running the following SQL Server query:

    DECLARE @Job_title varchar(20)

    --User -definded VARIABLE<<<<<<<<<<<<<<<<<<<<<<

    Set @job_title='<enter Trigger modification job name'>'

    -- user-defined VARIABLE<<<<<<<<<<<<<<<<<<<<<<<

    EXEC sp_help_job @job_name=@job_title

    Fix Text:

    Configure a SQL Server timed job that auomatically checks all system and user-defined Triggers for modification.

    Andreas, it sounds from your answer as though the answer is to use a third party tool for baselining.

    Erland, I can't think of any way possible to edit the disable and reenable of a trigger.  There is an audit script given http://www.stigviewer.com/check/v-41402   I am not well versed enough in the event_id audit values to know if one of these covers someone enabling and disabling a trigger.


    R, J


    • Edited by Crakdkorn Monday, February 10, 2014 8:32 PM
    Monday, February 10, 2014 6:31 PM
  • I was not able to find anything useful at the stigviewer link.

    A trigger can be disabled with DISABLE TRIGGER and you cannot trap this with a DDL trigger. I don't know on the top of my head whether it can be audited through SQL Audit or Event Notification. It certainly can be registered with Trace or X-Events.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 10, 2014 10:57 PM