Blog

DoD STIGs – V-32368

Overview:

Title: The DBMS must produce audit records containing sufficient information to establish what type of events occurred.

Vulnerability ID: V-32368

STIG ID:

IA Controls: None

Severity: medium

Description: Information system auditing capability is critical for accurate forensic analysis. Audit record content that may be necessary to satisfy the requirement of this control includes: time stamps, source and destination addresses, user/process identifiers, event descriptions, success/fail indications, file names involved, and access control or flow control rules invoked.

Database software is capable of a range of actions on data stored within the database. It’s important, for accurate forensic analysis, to know exactly what actions were performed. This requires specific information regarding the event type an audit record is referring to. If event type information is not recorded and stored with the audit record, the record itself is of very limited use.

Check Text: Check DBMS settings and existing audit records to verify information specific to the audit event type is being captured and stored with the audit records. If audit records exist without information regarding what type of event occurred, this is a finding.

Fix Text: Modify DBMS audit settings to include event type as part of the audit record.

Interpreting V-32368:

SQL Server Auditing is quite robust and should be able to collect the required information for the DoD selected list of auditable events. Options are also available to use tools and components other than just SQL Server Audit. Policy Governor and PowerShell are both powerful tools that can be used along with SQL Audit to create a complete auditing solution for SQL Server.
(more…)

DoD STIGs – V-32413

Overview:

Title: Database software directories, including DBMS configuration files, must be stored in dedicated directories, or DASD pools, separate from the host OS and other applications.

Vulnerability ID: V-32413

STIG ID:

IA Controls: None

Severity: medium

Description: When dealing with change control issues, it should be noted any changes to the hardware, software, and/or firmware components of the information system and/or application can potentially have significant effects on the overall security of the system.

Multiple applications can provide a cumulative negative effect. A vulnerability and subsequent exploit to one application can lead to an exploit of other applications sharing the same security context. For example, an exploit to a web server process that leads to unauthorized administrative access to host system directories can most likely lead to a compromise of all applications hosted by the same system. Database software not installed using dedicated directories both threatens and is threatened by other hosted applications. Access controls defined for one application may by default provide access to the other application’s database objects or directories. Any method that provides any level of separation of security context assists in the protection between applications.

Check Text: Review the DBMS software library directory and note other root directories located on the same disk directory or any subdirectories. If any non-DBMS software directories exist on the disk directory, examine or investigate their use. If any of the directories are used by other applications including third-party applications that use the DBMS, this is a finding.

Only applications that are required for the functioning and administration, not use, of the DBMS should be located on the same disk directory as the DBMS software libraries.
(more…)

What’s Going On? A review of 2015 and forecast for 2016

It’s been quiet… Too quiet

Yes, yes it has.

Things have been quiet here as of late. Posts and podcasts both have been lacking.

I felt it important to share will all of you what has been happening, and what is going to happen in the future with this site, my company WaterOx Consulting, and me.

 

What Happened?

2015 was a year of amazing accomplishments paired with some tough decisions and sacrifices.

This past year saw the brand of WaterOx Consulting grow as I presented at over 12 events, including SQL Saturdays, user groups, virtual chapters, various companies, SQLLive360 in Orlando and the Information Security Summit again, as well as adding the PASS Summit! I also pulled together and ran the inaugural SQL Saturday in Nova Scotia along with continuing to organize the Washington D.C. SQL Saturday. I managed to spread my presentations and SQL Server passion, in person, all the way across the US and even internationally to Canada!

This past year saw the birth of my WOxPod! podcast too. It is a lot of work to pull together, but it is fun and I hope to get a lot more episodes coming out in the near future. It has also helped to spread my brand further than just my local area. It isn’t huge, but it has a small following and I appreciate every one of you that do.

I have continued to gain followers online on Twitter. I am just shy of 1,000 followers! I swear I never, ever, thought the number would get even close to that. Thanks go out to all of you that keep up with me, and also share my information and continue to help my grow the business. Her’es hoping in 2016 I can bury that 1,000 follower mark!
(more…)

What Alerts Us About The SQL Agent?

The SQL Agent is our friend!

Setting up SQL Server Agent to automate jobs and alerts is a great idea.

It is always better to learn about a failing job or system prior to end users so we, as DBAs, can try to deal with it.

But what happens when the SQL Agent stops and we can’t get those alerts?

The problem with a lot of alerting systems is that they are either constantly pinging your server and service, or they tell you after the fact that a service failed as it comes back online (hopefully).

Who Alerts us when the SQL Agent itself fails?

That’s a good question. By default there is no alert of the agent failing, except something written to the log. Unless you are using some sort of log or service monitoring solution, you don’t get told things aren’t running. At least you are not told until your phone or email blows up with angry end users since their reports or data loads are not working.

It would be nice to have something alert you when the agent itself stops wouldn’t it?

Especially if you could get that capability with tools you already have. Always nice to not have to pay for extra tools and overhead when you don’t have to right?

What to do?

All the services running on your server have a recovery tab. This tab has settings that tell Windows what to do if the service fails. The options are: Take No Action (the default), Restart the Service, Run a Program, or Restart the Computer. Some drastic, some not so much.
(more…)

SQL F.A.D. – Trace flag 834 and columnstore indexes

Trace flags can be good

Are special codes we, the DBA, can use to change the way the SQL Server engine works.

Some perform simple changes, like turning off excessive backup success notices in the logs.

Other can completely cripple your SQL Server if you don’t know what you are doing; Trace flag T3609, is undocumented, but it tells SQL to not create a tempdb when it starts. Needless to say, this trace flag should be used only if Microsoft support team directs you to use it.

 

Columnstore indexes can be good

Are a special kind of index in SQL server. They essentially turn row indexing on their side to make things like aggregates work a lot faster. Generally they are used in data warehouse environments.

 

Together they can be not good

Sometimes combining columnstore indexes and trace flags can impact performance in a not good way.

How much?

I don’t know, but I do know it is not recommended to have trace flag 834 on when using columnstore indexes in your databases.

Since the 834 trace flag is a global level flag, and columnstores are in individual databases I wrote the script below to go through and check if you ave any columnstore indexes, and then check if the trace flag is enabled.

It simply returns an alert message if the conflict is found, otherwise nothing happens.

This code also does not fix any issues you may have with the trace flag.

You need to turn it off yourself using the following command:

You also have to make sure it isn’t set as a startup parameter on the SQL Server startup parameters. There is would look like:  ; -T3226
(more…)

DoD STIGs – V-32412

Overview:

Title: Database objects must be owned by accounts authorized for ownership.

Vulnerability ID: V-32412

STIG ID:

IA Controls: None

Severity: medium

Description: Database functions and procedures can be coded using definers rights. This allows anyone who utilizes the object to perform the actions the object allows as if they were the owner. This can lead to privileged actions being taken by unauthorized individuals.

If critical tables or other objects rely on unauthorized owner accounts, these objects can be lost when an account is removed.

Within the database, object ownership implies full privileges to the owned object including the privilege to assign access to the owned objects to other subjects. Unmanaged or uncontrolled ownership of objects can lead to unauthorized object grants and alterations.

Check Text: Review system documentation to identify accounts authorized to own database objects. Review accounts in DBMS that own objects.

If any database objects are found to be owned by users not authorized to own database objects, this is a finding.

Fix Text: Update system documentation to include list of accounts authorized for object ownership.

Re-assign ownership of authorized objects to authorized object owner accounts.

Interpreting V-32412:

Check each DB to see who the owner is. You can do this with the following script:

Use the results from this query to verify that each owner is authorized to own the database and either update documentation accordingly, or change the owner to an approved account.

Return to the DoD STIGs – Database Security Requirements Guide