A SQL Server Blog by - Mohammad Darab

The BEST Personal Lab Setup Course

In order to learn a topic such as Windows Server Failover Clustering, you must have a complete personal lab. You’re only as good as your lab environment.

My friend and mentor, Microsoft Certified Master and MVP Edwin Sarmiento (b | t) recently published a video course on setting up a personal lab for deploying SQL Server on a Windows Server Failover Cluster.

This course is a preclude to his awesome course on Windows Server Failover Clusters.

Edwin’s course is a complete A to Z guide on setting up a personal lab. He provides detailed explanations, links, articles and additional resources for people interested in pursuing a deeper understanding of all the key components.

I completed setting up my personal lab using his course and I cannot thank him enough. I learned so many things outside the duties of an “everyday DBA” like Active Directory, DNS, and networking that I never knew before.

One beautiful thing about owning this course is once you go through the process of setting up the lab, you can delete it and start over from scratch. In doing so, you solidify certain topics, scripts, procedures and become a stronger IT professional.

If you’re serious about setting up an awesome HA / DR lab environment, I highly recommend purchasing Edwin’s course.

The Best Online Course to Learn Windows Server Failover Cluster

I am extremely proud to announce what I believe to be the best video course on Windows Server Failover Clustering by my friend and mentor, Microsoft Certified Master and MVP Edwin Sarmiento (b | t). I am very fortunate to be an early subscriber of the course and compared to all other videos/courses I came across in the past, Edwin’s is the best by far.

Why is Edwin’s WSFC Course the Best?

Prior to Edwin’s course, I watched a couple different videos on YouTube as well as Microsoft‘s Channel 9 course on WSFC. Now, I’m not saying Microsoft’s Channel 9 video series on WSFC is bad. It’s not. My personal “gripe” about the series is the length of time it spends on WSFC features that normally aren’t used in real-world production environments. Microsoft talks about it because it’s a feature so they have to talk about. So, as you can imagine, that adds tremendous time to the video series. The YouTube videos are either bad audio quality, difficult to understand, the analogies they use don’t make sense, or it’s flat out boring.

Below is a list of reasons why I believe Edwin’s course is the best out there:

What I love is that Edwin only presents what’s applicable in a real-world production environment. He does a great job in “trimming the fat off the steak.” His video course goes over setting up a lab environment, explaining high level concepts, and diving deep into the details. So he’s not wasting anyone’s time. Kudos!
Edwin has an amazing ability to take a complicated concept and break it down in the most simplest terms for the common layman, like myself, to understand. I was a little shocked at how easily I understood the concept behind WSFC. Read Edwin’s Traffic Light analogy to see what I mean.
To go off #2 above: Learning something new (WSFC in this case) not only gives you that professional work life boost but a personal one too. A great teacher not only teaches, but instills understanding and confidence. The positive psychological impact of being able to say, “I understand this!” is priceless.
Edwin’s course audio is amazing. It sounds like it was recorded in a professional studio. We all know that bad audio is a pain to listen to. It can lead to impatience, boredom, and eventually not learning anything.
Edwin is extremely kind and humble. I have known Edwin for some time now and he has thoroughly answered more of my questions than any single person I can think of.
Quite frankly, it’s not boring like other courses. I have watched other courses and they have put me to sleep faster than reading “If You Give a Mouse a Cookie” to my 3 year old son.
Amazing PRICE! Sign Up Now!

About Edwin Sarmiento

Edwin Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures running on the Microsoft server technology stack. He is very passionate about technology but has interests in music, professional and organizational development, leadership and management matters when not working with databases. He lives up to his primary mission statement – “To help people and organizations grow and develop their full potential as God has planned for them” *

V-40951 – 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-40951

Title: “SQL Server must support the organizational requirement to employ automated mechanisms for enforcing access restrictions.”

Severity = Medium

Details = Obtain the SQL Server software library installation directory location.

From a command prompt, type regedit.exe, and press [ENTER].

Navigate to HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> Instance Names. Each instance installed on the server possesses a key inside a folder under this registry entry.

Analysis Services Instances are registered in the OLAP subfolder.
Reporting Services Instances are registered in the RS subfolder.
Standard SQL Server Instances are registered in the SQL subfolder.

Inside each one of these folders, a single key is used to reference an instance’s specific Windows Registry tree. Each key will have its own registry tree at the following registry location: HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME].

An [INSTANCE NAME] is listed as the data component of a key found in one of the above OLAP, RS, or SQL folders.

To find the installation location of a particular instance, navigate to the following location in the Windows Registry:
HKEY_LOCAL_MACHINE >> SOFTWARE >> Microsoft >> Microsoft SQL Server >> [INSTANCE NAME] >> Setup. Examine the value of the ‘SqlProgramDir’ key. The value of the ‘SqlProgramDir’ key is the SQL Server installation directory for that SQL Server Instance.

Navigate to that folder location using a command prompt or Windows Explorer. Note any custom subdirectories within the SQL Server software library directory. Only applications that are required for the functioning and administration of SQL Server should be located in the same disk directory as the SQL Server software libraries.

If any directories or files not installed with the SQL Server software exist within the SQL Server software library directory, this is a finding.

FIX: Install SQL Server software using directories separate from the OS and other application software library directories.

Relocate any directories or reinstall other application software that currently shares the DBMS software library directory to separate directories.

Recommend dedicating a separate partition for the SQL software libraries.

This is pretty self explanatory. You should never install your SQL Server on the default “C” drive. Let your SAN team, or Windows Admin team know that you will need separate disk partitions. For example, the installation files can reside on the “E” drive, data file on the “F” drive and the log file on the “G” drive.

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.

V-40950 – 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-40950

Title: “SQL Server must support the employment of automated mechanisms supporting the auditing of the enforcement actions.”

Severity = Medium

Details = Verify that Files and Folders that are part of the SQL Server 2012 Installation have auditing enabled.

Right click the root folder of the SQL Server installation. Typically, this is at :\Program Files\Microsoft SQL Server\. Select Properties.

Click on the Security tab

Click on the Advanced button

Click on the Auditing tab

If “Everyone” is not listed in the “Name” column, this is a finding.

If “This folder, subfolders and files” is not listed in the “Apply To” column, this is a finding.

When “Everyone” … ” is listed, select the “Everyone” row and click on the Edit button.

If either the Successful or Failed checkbox is not selected for any of the following access types, this is a finding:
Traverse folder/execute file
List folder/read data
Read attributes
Read extended attributes
Create files/write data
Create folders/append data
Write attributes
Write extended attributes
Delete
Read permissions

FIX: Navigate to Advanced Security Settings by selecting Properties > Security > Advanced > Auditing > Continue.

Where “Everyone” is missing from the “Name” column, click the Add button; type “Everyone” in the object names box; click the OK button. The Auditing Entry dialog opens.

Where “Everyone” is in the “Name” column, select that row and click on the Edit button. The Auditing Entry dialog opens.

In the Auditing Entry dialog, set “Apply onto” to “This folder, subfolders and files”.

In the Auditing Entry dialog, select both the Successful and the Failed checkbox for each of the following access types, where not already selected:
Traverse folder/execute file
List folder/read data
Read attributes
Read extended attributes
Create files/write data
Create folders/append data
Write attributes
Write extended attributes
Delete
Read permissions

Click OK, OK, OK, OK to save the new settings and exit the dialog boxes.

This is pretty self explanatory. 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.

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.

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

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

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.