Blog Stats – 2015

My first blog was posted in August of 2015. I didn’t think anyone would ever come to my site but that started to change the more I kept making posts.

Why Post Your Blog Stats?

To me, it’s pure motivation. I love numbers, stats, graphs, etc. So why not? I was a little hesitant to publish in the beginning because of how small my numbers were. It’s like the guy who just started going to lift weights at the gym and is embarrassed that he’s barely bench-pressing the bar next to guys benching 45s. Well, where did they start out? :) Everyone starts at the bottom and works their way up.

Below you will find three snapshots with key page view/visitor information.

Blog Stats Visitors 2015

As you can see, the stats are pretty flat until August.

Google Analytics Blog Pageviews 2015

I was, and still am, super excited that people from around the world are coming to my blog. A true motivation!

Blog Stats for 2015

On to a great 2016!

V-43196 – SQL Server 2012 Database Instance DISA STIGs

If you have any additional questions that aren’t addressed in the blog, feel free to contact me. I’d be more than happy to help you out!

V-43196

Title: “Domain accounts used to manage a SQL Server platform must be different from those used to manage other platforms.”

Severity = Medium

Details = Determine the accounts being used to manage the SQL Server operating system. Determine whether the same accounts are being used to manage other platforms. If the same account is used to manage more than one platform, this is a finding.

Note: If, because of the application configuration, there are multiple instances of SQL that would share a given exploit, a single account would be allowed to be used for the group and would not be considered a finding.

FIX: Set up and use separate domain accounts to manage the SQL Server platform. These accounts must be different from those used to manage other platforms.

MSDN has a great article that addresses security considerations for a SQL Server installation. Read the article here. I also highly recommend buying a great book by Denny Cherry called, “Securing SQL Server.” I did a review of the book that you can read here.

Book Review – “Securing SQL Server 3rd Edition” by Denny Cherry

 

Create Database Audit for INSERT, UPDATE, DELETE in SQL Server

There are a few ways to monitor when someone makes an INSERT, UPDATE or DELETE in SQL Server. One quick and easy way is through SQL Server Audit. Below is a step-by-step way to create that audit:

Expand Security, then right-click Audits and click ‘New Audit…’

Create Database Audit 1

Pick how, and where, you want to save the file.

Create Database Audit 2

After choosing where/how to save the file, expand the database, then expand Security, right-click ‘Database Audit Specifications‘ and choose ‘New Database Audit Specification

Create Database Audit 3

The next set of sequences all relate to what objects/permissions/roles you want to audit.

Create Database Audit 4

Create Database Audit 5

Create Database Audit 6

Create Database Audit 7

Output of fn_get_audit_file:

Create Database Audit 8

The code to recreate the above steps is below:

use master;
GO

IF DATABASEPROPERTYEX('testDB', 'version') > 0
BEGIN
    ALTER DATABASE testDB SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
	DROP DATABASE testDB;	
END


CREATE DATABASE testDB;
GO


use testDB;
GO


CREATE TABLE testTable(
col1 int IDENTITY,
col2 char(20));
GO

--Insert Data
INSERT INTO testTable
VALUES('Insert Test');
GO

--Update that data
UPDATE testTable
SET col2 = 'Update Test'
WHERE col2 = 'Insert Test';
GO

--Delete that data
DELETE testTable
WHERE col2 = 'Update Test';
GO

--Now check 'fn_get_audit_file'
--to see the above Insert, Update, Delete

SELECT aud.event_time,
aud.action_id,
aud.session_id AS [Session ID],
aud.server_principal_name,
aud.database_name AS [Database],
aud.schema_name AS [Schema],
aud.object_name AS [Object],
aud.statement
FROM fn_get_audit_file('E:\Backups\testDB\IUD-Audit_810D2516-4695-4824-8384-EFC19C99C62E_0_130969930856890000.sqlaudit',DEFAULT, DEFAULT) aud


--Drop table
DROP TABLE testTable;
GO

SQL Server Backup Under the SIMPLE Recovery Model

SQL Server Backup Under the SIMPLE Recovery Model

I tend to forget how to do certain database administration tasks if I don’t do them often enough. No matter how *SIMPLE* they might be. :)

Recently at work I saw a production database server that was in the SIMPLE recovery model, that was scheduled for weekly FULL backups and daily DIFFERENTIAL backups. I was a little confused when I first saw this. I wondered, “I thought you cannot do FULL backups in SIMPLE recovery model?” So I checked out Books Online.

To my surprise, you CAN run a FULL/DIFF backup on a database in the SIMPLE recovery model. What you cannot do is run transaction log backups.

Look at what happens if you try to do a transaction log backup on a database in the SIMPLE recovery model (see snapshot below)

Backup Under the SIMPLE Recovery Model FAILS

Going back to the production database server I mentioned above. If that server crashes right before the daily DIFF backup then a total of 23:59 of data can be lost.

So the point between FULL and SIMPLE recovery model is:

FULL Recovery Model – Allows you to take transaction log backups. With log backups you can perform point-in-time restore, and minimize data loss.

SIMPLE Recovery Model – You can only take FULL backups and DIFFERENTIAL backups. You cannot take log backups, which means no point-in-time restore capability. IF you need point-in-time restore then switch your database to the FULL recovery model.

Moral of the story…

So what’s the moral of the story? You, as the DBA, need to know what your organization wants. Don’t ever assume. A very crucial question to ask in order to find out exactly what they want is, “In case of a crash, how much data are you okay with losing?” If the answer is, “NONE!”, then stick with the FULL recovery model and schedule frequent transaction log backups depending on how much data the organization is comfortable with losing. If the answer to the question is, “ah, a day or half a day’s worth of data.” Then, you can stick with SIMPLE recovery model and schedule FULL backups with intermittent DIFFERENTIAL backups.

Hope that helps!

Book Review – “Securing SQL Server 3rd Edition” by Denny Cherry

Over the past few years I have been applying DISA STIGs on countless SQL Server environments and never had a “go to” single source that would answer all my SQL Server security-related questions. If not answer, at least guide me in the right direction. That’s why I was super excited when I heard Brent Ozar recommend a book called, “Securing SQL Server” by Denny Cherry’s on his new Office Hours podcast.

I wish I came across Denny Cherry’s “Securing SQL Server” book earlier, but I believe everything happens for a reason so I’m not going to complain.

Securing SQL Server 3rd Edition

Securing SQL Server by Denny Cherry
Securing SQL Server by Denny Cherry

I purchased the latest 3rd edition which includes SQL Server 2014. The book has a little over 400 pages spread over 15 chapters and is extremely easy to read. Denny does a great job taking a dry and boring topic like ‘security’ and making it interesting.

Over the span of 15 chapters, Denny talks not only about SQL Server database related security but network security (Chapter 2), SAN security (Chapter 11), Analysis Services and Reporting Services.

If you’re new to database security, there’s nothing to fear because Denny starts the very first chapter with “Identifying Security Requirements.” He goes over basic concepts and questions such as; what is PII (Personal Identifiable Information), what are security objectives, and how to identify them. So a SQL Server “accidental DBA” or novice won’t feel lost.

SQL Server security is something that a lot of people (including myself at one point) take for granted. It’s a very daunting task to learn and implement security settings within SQL Server as there are a huge number of factors to consider. There is always the fear of “breaking” something. A well-rounded knowledge of networking, windows system administration, database and storage concepts is crucial to understand security as a whole. By including real world client scenarios, Denny does an awesome job explaining these complicated topics in a simple fashion.

About the Author

Denny Cherry (b | t) is a Microsoft Certified Master (MCM), MVP and has over 15 years of experience in all areas of SQL Server such as performance tuning, troubleshooting and system architecture. He is the owner and Principal Consultant for Denny Cherry & Associates.

Podcast Review – Office Hours with Brent Ozar Unlimited

It takes me roughly 45 minutes to get to work. I don’t like to listen to the radio. Last time I listened to the radio while driving I was blasting Pearl Jam, STP, Nirvana (yes, back in the 90s). Nowadays, I spend my driving time pondering about life, work, etc. How can I get better at work? How can I increase my knowledge of SQL Server? (Yes, I actually do think about that) What blogs, books, videos, podcasts are out there at I have not yet read/heard?

Office Hours Podcast

Podcast Review - Office Hours with Brent Ozar Unlimited
Office Hours with Brent Ozar Unlimited

Luckily for me, Brent Ozar (b | t) has recently launched his “Office Hours” live webinar event as a podcast and it’s available on iTunes. I recently subscribed to it and binge listened to all the episodes over the course of two days. (Yes, I binge listened to a SQL Server podcast on my way to and from work. Don’t hate. :)

What I love about Office Hours Podcast

There are numerous things I love about this podcast. First, I love the fact that they get STRAIGHT to the questions. There isn’t any fluff or filler. Second, the content they talk about are questions that you and I ask during the live event recording. So just ask whatever question/s you have during the live event and it’ll be on the podcast for others to listen and hopefully learn from. Last, but not least, I love their sense of humor. Everyone on the team seems to have a great sense of humor which keeps the listener from wanting to press stop. +1 :)

Can it be better?

Since this is an audio-only podcast, it’s crucial to have great audio. There are times during the podcast that I cannot audibly understand what people are saying. I don’t know if it’s the quality of the headset, or earphones / microphone. If they can address the audio quality, then that would be icing on the “podcast cake.”

How to Subscribe

I highly recommend subscribing to this podcast especially if you love learning about SQL Server. In fact, if you leave a rating and review via iTunes, Brent will offer you a 78% OFF coupon code to apply on their video classes! That is a deal you cannot refuse! Find out more details about that here.

How to Change Autogrowth in SQL Server 2012

It’s wise to set SQL Server file autogrowth to a set number rather than the default percent. That is because the percent setting can end up growing your file very large. The larger your file, the larger that percent will be. It’s better to set your file autogrowth to a set number and keep an eye on it for proper maintenance.

Start by right-clicking the database in SSMS and choosing Properties. Then in the “Database Properties” window, choose Files. Click the ellipses under the “Autogrowth” column (blue arrow below).

Database Properties Files

The  “Change Autogrowth for …” window will pop up. Under File Growth, you can click on the “In Megabytes” radio button and set a fix amount.

SQL Server Autogrowth Settings

It’s as easy as that.

V-40948 – SQL Server 2012 Database Instance DISA STIGs

If you have any additional questions that aren’t addressed in the blog, feel free to contact me. I’d be more than happy to help you out!

V-40948

Title: “Software, applications, and configuration files that are part of, or related to, the SQL Server 2012 installation must be monitored to discover unauthorized changes.”

Severity = High

Details = Verify that files and folders that are part of, or related to, the SQL Server 2012 installation have only the appropriate privileges.

In Windows Explorer, right-click the file/folder, click Properties. On the Security tab, modify the security permissions, so that at most the following permissions are present:

Trusted Installer (Full Control)
SYSTEM (Full Control)
Administrators (Full Control) [See Note 1]
Users (Read, List Folder Contents, Read & Execute)
Creator Owner (Special Permissions – Full control – Subfolders and files only)

All Application Packages (Read & Execute) [Only as needed – see Note 2]

If any less restrictive permissions are present (and not specifically justified and approved), this is a finding.

Verify that files and folders that are part of, or related to, the SQL Server 2012 installation have auditing enabled. Right-click on the file/folder, click Properties. On the Security tab, click Advanced. On the Auditing tab, verify that the following is set up on at least one audit:

Type: All
Principal: Everyone
Access: Modify
Applies to: This Folder, subfolder, and files [where applicable]

If the required audit settings are not configured, there is a risk that unauthorized changes to the software will go undetected, and this is a finding.

If a third-party security and data integrity tool is not used for monitoring and alerting files and folders based on cryptographic hashes, this is a finding.

If the tool does not verify files/folder locations as listed in the documentation, this is a finding.

FIX: Include locations of all files, libraries, scripts, and executables that are part of, or related to, the SQL Server 2012 installation in the documentation.

Ensure that files and folders that are part of, or related to, the SQL Server 2012 installation have only the following privileges. Right-click the file/folder, click Properties. On the Security tab, modify the security permissions, so that at most the following permissions are present:

Trusted Installer (Full Control)
SYSTEM (FULL CONTROL)
Administrators (FULL CONTROL)
Users (READ, LIST FOLDER CONTENTS, READ & EXECUTE)
Creator Owner (Special Permissions – Full control – Subfolders and files only)
All Application Packages (Read & Execute) [Only as needed – see Note 2]

Ensure that files and folders that are part of, or related to, the SQL Server 2012 installation have auditing enabled. Right-click on the file/folder, click Properties. On the Security tab, click Advanced. On the Auditing tab, use the Add or Edit buttons and the dialogs that follow from them, to set up the following on at least one audit:

Type: All
Principal: Everyone
Access: Modify
Applies to: This Folder, subfolder, and files [where applicable]

Deploy a third-party security and data integrity tool for monitoring and alerting files and folders based on cryptographic hashes, to verify files/folder locations as listed in the documentation.

Note 1: In the interest of separation of responsibilities with least privilege, consider granting Full Control only to SQL Database Administrators (or another appropriate group of administrators) and providing the local Administrators group with Read access only.

Note 2: Some files also require ‘ALL APPLICATION PACKAGES (READ, EXECUTE)’ permissions for certain functionality to work appropriately, and this is considered acceptable where those permissions are required. (All SQL Server files that require this access reside by default in the ..\Microsoft SQL Server\110\ directory.)

It’s extremely crucial to configure appropriate folder access. The last thing you want is some random user deleting files!

V-40945 – SQL Server 2012 Database Instance DISA STIGs

If you have any additional questions that aren’t addressed in the blog, feel free to contact me. I’d be more than happy to help you out!

V-40945

Title: “Vendor-supported software and patches must be evaluated and patched against newly found vulnerabilities.”

Severity = High

Details = Check Microsoft’s list of supported SQL Server versions http://www.microsoft.com/sqlserver/en/us/support/support-updates.aspx

To be considered supported, Microsoft must report that the version is supported by security patches to known vulnerabilities.

Check SQL Server version by running the following script:

print @@version

If the security patch support for SQL Server cannot be determined or SQL Server version is not shown as supported, this is a finding.

If SQL Server does not contain the latest security patches, this is a finding.

FIX: Upgrade SQL Server to the Microsoft-supported version.

Apply the latest SQL Server patches after evaluation of impact.

This fix is self-explanatory. Run the command above and see what version you are running. Go to SQL Server Updates (by Brent Ozar) and check if you have the latest Service Pack or Cumulative Update. If not, update. If so, close this “Not a Finding.”

*NOTE: Test any service packs or cumulative updates on a test / staging server first!

V-40941 – SQL Server 2012 Database Instance DISA STIGs

If you have any additional questions that aren’t addressed in the blog, feel free to contact me. I’d be more than happy to help you out!

V-40941

Title: “SQL Server must have the SQL Server Data Tools (SSDT) software component removed from SQL Server if SSDT is unused.”

Severity = High

Details = Review the list of components and features installed with the database. Using an account with System Administrator privileges, from Command Prompt, open control.exe.

Navigate to Programs and Features. Check for the following entries in the ‘Uninstall or change a program’ window.

Microsoft SQL Server Data Tools – Database Projects – Web installer entry point – Prerequisites for SSDT

If SQL Server Data Tools is not documented as a server requirement, and these entries exist, this is a finding.

FIX: Document the requirement for SQL Server Data Tools to reside on this server.

If your organization requires SSDT to be installed then just document that and close the finding.

If it is not required, using an account with System Administrator privileges, from Command Prompt, open control.exe.

Navigate to Programs and Features. Remove the following entries in the ‘Uninstall or change a program’ window.

Removing SSDT

Microsoft SQL Server Data Tools – Database Projects – Web installer entry point – Prerequisites for SSDT

Removing SSDT 2

Once you click NEXT, you can choose the SSDT checkbox and click NEXT to remove it.

Removing SSDT 3