SQL Server Audit Recipe – xp_cmdshell

This article assumes you already have a basic understanding of SQL Server Audit, but if not, use this link to catch up on all the details.

Are you required to have xp_cmdshell enabled on one of your servers? If so, then setup a SQL Audit now to track its use. Never mind the implications of enabling xp_cmdshell, as a DBA you are responsible for what happens on your servers and tracking the use of xp_cmdshell should be a priority.

The first step is to create a server audit to hold the events that we collect.

USE master;
GO
CREATE SERVER AUDIT Audit_xp_cmdshell
TO FILE (FILEPATH = 'E:\SQL2017\SQL_Audit')
WITH (ON_FAILURE = CONTINUE)
WHERE (object_name = 'xp_cmdshell');
GO

You’ll notice that we added a WHERE clause that instructs the audit to only collect events that reference the object xp_cmdshell. All other events will be ignored.

Next, we need to create a server audit specification using the
SCHEMA_OBJECT_ACCESS_GROUP. This server-level action group is triggered when a permission is used to access an object such as xp_cmdshell.

CREATE SERVER AUDIT SPECIFICATION AuditSpec_xp_cmdshell
FOR SERVER AUDIT Audit_xp_cmdshell
  ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Running the following commands will make sure both the audit and audit specification are enabled.

ALTER SERVER AUDIT Audit_xp_cmdshell WITH (STATE = ON);
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpec_xp_cmdshell WITH (STATE = ON);
GO

To test our audit, we need to make sure xp_cmdshell is enabled.

EXEC sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1;
GO
RECONFIGURE;
GO

Then call xp_cmdshell to create some activity.

EXEC xp_cmdshell 'DIR E:\SQL2017\SQL_Tempdb\*.* /b';
GO

Viewing the audit log, you can clearly see the command that was executed and the login that called it.

As we have seen, if you have a server that has xp_cmdshell enabled, then using SQL Audit can help you keep track of the commands that have been executed.

Everything we have covered here will work in all editions of SQL Server 2012 and above.

SQL Server Morning Health Checks

Every now and again as a Microsoft PFE, you get a chance to make a big difference for a customer. One such occasion happened just recently when I was asked to help find a way to automate the daily checks the DBA had to perform every morning. The result was a PowerShell script that reduced that manual task down from an hour to less than a minute.

You can read the full article here on MSDN.
https://blogs.msdn.microsoft.com/samlester/2017/12/29/sql-server-dba-morning-health-checks/

The PowerShell script can be downloaded from here.
https://github.com/PatrickKeisler/SQLMorningHealthChecks

Create a Whitelist for SQL Server - UPDATED!!!

A while back, I posted an article about creating a WhiteList for access to SQL Server. Since then I have received a bit of feedback that it was not working as designed. My apologies for taking so long, but I believe I have come up with a fix.

The main issue is the trigger will block some or even all access to the server after it’s created. As it turns out, the issues were really permission being denied. To see it in action, let’s create everything using the original code from here.

We’ll add 1 row to the WhiteList table should allow all users from the workstation, ECHOBASE1, access, regardless of its IP address.

USE master;
GO
INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
VALUES ('*','ECHOBASE1','*');
GO

Next, we’ll create a SQL login with only connect permission to the server and nothing else.

CREATE LOGIN LogonTriggerTest WITH PASSWORD = 'Password1';
GO

Finally, we’ll open a new query window using that login.

As you can see, we are denied access to the server because of the logon trigger. If we look in the Errorlog, we can see that we lack the VIEW SERVER STATE permission.

This was my first mistake. I did my initial testing using an administrative login. Most users are not admins; therefore, they will not have the permission required to view sys.dm_exec_connections. I was using this DMV to get the IP address of the client connection, and it requires the VIEW SERVER STATE permission. To get around this, I can use the CONNECTIONPROPERTY function, as it does not require any additional permissions.

SELECT CONNECTIONPROPERTY(‘client_net_address’);
GO

Now let’s try to connect again.

Again, we failed. This would be my second mistake. I failed to grant SELECT access to the WhiteList table. By default, a user is will have public permission to the master database, but no permission to the table. To solve this, we can grant permission to the public database role. This will allow any authenticated user to read from the WhiteList table.

USE master;
GO
GRANT SELECT ON dbo.WhiteList TO public;
GO

Finally, our connection to SQL Server is successful. Using the same code from the trigger, we can compare it what’s in the WhiteList table.

USE master;
GO
SELECT
   ORIGINAL_LOGIN() AS 'LoginName'
  ,HOST_NAME() AS 'HostName'
  ,CONNECTIONPROPERTY('client_net_address') AS 'HostIpAddress';
GO
SELECT * FROM dbo.WhiteList;
GO

If I had followed my own rules, I could have discovered most of these issues before posting the original article.

The fully updated code is below. Please let me know if you run into any other issues with this new version. I also added another column to the WhiteList table that can be used for hold comments. The idea is to provide some documentation about what the white-listed item is attempting to do.

USE master;
GO

IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
  DROP TABLE dbo.WhiteList;
GO

CREATE TABLE dbo.WhiteList
(
   Id INT IDENTITY(1,1) PRIMARY KEY
  ,LoginName VARCHAR(255)
  ,HostName VARCHAR(255)
  ,HostIpAddress VARCHAR(50)
  ,Comments VARCHAR(2000)
);
GO

GRANT SELECT ON dbo.WhiteList TO PUBLIC;
GO

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress,Comments)
VALUES
   ('*','ECHOBASE1','*','Any user from the workstation "ECHOBASE1" is allowed to connect, regardless of IP address.')
  ,('WebSiteLogin','webserver1','192.168.100.55','Only the WebSiteLogin from webserver1 with an IP of 192.168.100.55 is allowed access.');
GO

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
  DECLARE 
     @LoginName VARCHAR(255) = ORIGINAL_LOGIN()
    ,@HostName VARCHAR(255) = HOST_NAME()
    ,@HostIpAddress VARCHAR(50) = CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address'));

  IF 
  (
    SELECT COUNT(*) FROM dbo.WhiteList
    WHERE 
    (
      (LoginName = @LoginName) OR (LoginName = '*')
    )
    AND
    (
      (HostName = @HostName) OR (HostName = '*')
    )
    AND
    (
      (HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*')
    )
  ) = 0
  ROLLBACK;
END;
GO

Dear McAfee, Why are you destroying my buffer pool?

For the past few months, one my customers had been trying to diagnose an issue with SQL Server paging out the entire buffer pool each morning around 8AM. See the error below.

Not only was this causing some serious performance issues, but it was also affecting every SQL Server in the environment.

I began my investigation with the standard troubleshooting techniques for buffer pool paging. One of the main workarounds that’s recommended is to enable the “locked pages in memory” permission for the SQL Server service account; however, this was not possible for this customer. Enabling that permission would violate one of their compliance rules, so I needed to find another solution.

The next stop on the investigation trail is the sys.dm_os_ring_buffers DMV. This is a great tool to help determine if the memory pressure came from within SQL Server or from the Windows OS.

WITH RingBuffer AS (
  SELECT
     CAST(dorb.record AS XML) AS xRecord
    ,dorb.timestamp
  FROM sys.dm_os_ring_buffers AS dorb
  WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
)
SELECT
   DATEADD(ms, rb.timestamp-dosi.ms_ticks, GETDATE()) AS 'DateTime'
  ,xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification
  ,xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess
  ,xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem
FROM RingBuffer AS rb
CROSS APPLY rb.xRecord.nodes('Record') record (xr)
CROSS JOIN sys.dm_os_sys_info AS dosi
ORDER BY 'DateTime' DESC;
GO

Matching the time of our Errorlog entry, we can see the resource monitor reported RESOURCE_MEMPHYSICAL_LOW with an IndicatorsProcess of 0 and IndicatorsSystem of 2. This helps us determine that SQL Server was not the cause of memory pressure and that the issue was caused by another process running on the server. Since these servers are dedicated to SQL Server, I know there are very few applications installed that should be competing with SQL Server for memory.

The next place to check was the Windows event logs. In the Application Log, I found the buffer pool entry by SQL Server (EventID 17890), and then proceeded to search backwards in time. A few minutes before SQL’s error, I noticed this entry.

What is the McShield service? A quick Bing search revealed that it’s one of the services for McAfee VirusScan Enterprise. Could this be the cause? To get a quick look at all the history, I filtered the application log for event IDs: 17890 and 5000. Each time McAfee got an updated virus DAT file, SQL Server soon followed that by paging out the entire buffer pool. I checked the application log on several other SQL Servers for the same event IDs, and sure enough the same events occurred in tandem each morning. I also got confirmation from the security administration team that McAfee is scheduled to check for new DAT files each morning around 8AM. Eureka!

This seems like it could be the cause of our paging, but a little more research is needed. Searching the McAfee knowledge base, lead me to this article about the “Processes on enable” option.

Enabling this option causes memory pages of running processes to get paged to disk. And the example given is “Oracle, SQL, or other critical applications that need to be memory-resident continually, will have their process address space paged to disk when scan Processes On Enable kicks in”. OUCH! So when the McAfee service starts up or it gets a new DAT file, it will page out all processes.

To verify this was really the cause, I needed to setup a test environment. I installed McAfee VirusScan Enterprise (VSE 8.8 Patch 7) on a Windows 2012 R2 server running SQL Server 2016, and then setup HammerDB to generate some activity against the server.

I configured Performance Monitor to capture the “Process:Working Set” and “Process:Page Faults/sec” for the SQL Server process (sqlservr.exe). Once HammerDB had a little bit of time to get running, I manually issued a DAT file update through the McAfee GUI.

Once completed, I checked the Windows Application Log and I could see event ID 5000 that said McShield service had restarted with the newer DAT version 8506.

About 90 seconds later, SQL Server’s buffer pool was paged out, as evident by event ID 17890.

The McAfee knowledge base article said this would happen when a new DAT file is received or when the McShield server starts up, so I decided to manually stop and restart the McShield service. As expected, I saw the same two event IDs in the application log. Below is the data collected by Performance Monitor for both of those tests.

The red line is the Working Set of SQL Server and the blue line is the Page Faults/sec for SQL Server. The first dip in the red line was from the DAT file update, and the second dip was when I manually restarted the McShield service. Since SQL Server’s buffer pool had just been paged out, it needed to read those pages from disk which results in a page fault.

The next step was to verify McAfee had all the correct exclusions for SQL Server.

Now that we know the exclusions were set properly, we can assume the issue is caused by the "Processes on enable" setting as previously mentioned. Let's turn off that setting and rerun our tests

After giving HammerDB a few minutes to generate some activity, I issue a manual DAT file update through the McAfee GUI. The application log shows the Event ID 5000 with a new DAT version of 8507, but no record of Event ID 17890 and there was nothing in the SQL Errorlog about memory paging.

Next, we'll test the manual restart of the McShield service. And we get the same result; Event ID 5000 but no indication of memory paging. Looking at perfmon, the counters show SQL Server's Working Set and Page Faults were unchanged during the tests.

Once we completed these tests, we took that data back to the security team and asked them to turn off that setting within McAfee. They obliged and so we waited and monitored our SQL Servers for the next few weeks. During that time, we did not have any SQL Server page out memory during the times that McAfee was getting new DAT updates.

After doing a little more research on McAfee VSE, I discovered this setting "Processes on enable" is enabled if you install McAfee with the Maximum Protection level. The Standard Protection level does not have this option enabled.

The key takeaway from this whole experience is that even though a program may be a standard install for all servers doesn't mean it won't impact SQL Server. And even though it may have been fully vetted in the past, a change to its settings could impact SQL Server in the future.

The lab tests were done using McAfee VirusScan Enterprise 8.8 Patch 7. I know this behavior is still the same in VSE Patch 8, so I would assume it’s the same in the other patch levels as well.

Additional resources:
https://kc.mcafee.com/corporate/index?page=content&id=KB76157
https://blogs.msdn.microsoft.com/karthick_pk/2012/06/22/a-significant-part-of-sql-server-process-memory-has-been-paged-out/
https://support.microsoft.com/en-us/help/2028324/how-to-troubleshoot-sql-server-error-message-17890

Blob Auditing for Azure SQL Database

In February 2017, Microsoft announced the general availability of Blob Auditing for Azure SQL Database. While auditing features were available before in Azure, this is a huge leap forward, especially in having more granular control over what audit records are captured.

Before Blob Auditing, there was Table Auditing. This is something I like to equate to the C2 auditing feature of SQL Server. It’s only configurable options were ON or OFF. In reality, Table Auditing has a few more controls than that, but you get the idea. There was no way to audit actions against one specific table. Blob Auditing provides us with that level of granularity. However, controlling that granularity cannot be accomplished through the Azure Portal; it can only be done with PowerShell or REST API.

Continue reading here...

AzureRM Module Version

When working with the AzureRM PowerShell module, remember the module is constantly being updated to take advantage of new features added to Azure.

Continue reading here...

A Goal of Speaking at PASS Summit

Everyone has goals or at least they should have goals. As a young professional out of college, I had plenty of goals, but none of them involved SQL Server. I really had no idea of where I wanted my career to go other than working in IT. Finally in 2000, I stumbled upon an opportunity that started me down the career path of a DBA. It didn't take long to realize I enjoy this type of work. Over the next decade, I learned a lot from my coworkers, training classes, and especially my mistakes. Yes, I really did drop the master database by mistake.

But one thing I didn't take advantage of was PASS and its huge community of SQL professionals. I knew about some of events, but it wasn't until 2012 that I got heavily involved. That decision forever changed my career. Since then, I started a blog about SQL Server, attended PASS Summit 2013, attended SQL Cruise 2014, started speaking at SQL user groups and SQL Saturdays, started working at Microsoft, attended SQL Cruise 2015, and most of all met a huge group of people that enjoy working with SQL Server as much as I do.

Eventually, I set a goal to be a speaker at PASS Summit. Well this past week I received notification that I had been chosen as a speaker for PASS Summit 2016. Upon hearing the news, I was extremely excited and humbled, especially considering there are far better speakers with more entertaining topics out there.

There are a lot of people that helped me along the way, but I'd like to really say thank you to my friend Andrew Kelly (b|t). Ever since I met him on board SQL Cruise, he has helped improve in the areas of public speaking, designing presentations, writing abstracts, code-review, and just an all-around great mentor.

However, this good news could not have come a worse time. 2016 has been a rough year for my family's health and it got substantially worse in early September when my wife was diagnosed with cancer. Luckily, doctors caught it very early and her prognosis is good, but we're still not taking any chances.

My good friend Scott, who is a cancer-survivor, told me "cancer is a radical disease and must be treated in radical ways" and to be prepared for any type of news throughout the treatment. I'm confident my wife can beat this and make a quick recovery, but until then I'll have to postpone speaking at PASS Summit. While I enjoy spending time with all of my friends within the SQL community, I love my wife and must focus all of my time to her recovery. There will always be time for another Summit.

SQLPSX is Finally Getting Updated

The most current code is now on Github, with the Codeplex version being depreciated. You can read all about the planned updates from Mike Shepard.

The Future of SQLPSX
https://powershellstation.com/2016/07/13/the-future-of-sqlpsx/

SQLPSX Update
https://powershellstation.com/2016/07/31/sqlpsx-update/

TSQL Tuesday #79 -SQL Server 2016 Launch Discovery Day (aka Hackathon)

The host for T-SQL Tuesday #79 is Michael J. Swart (b|t), and his topic of choice is to write about the new release of SQL Server 2016.

This past weekend I attended the SQL Server 2016 Launch Discovery Day in Raleigh, NC. I have attended several SQL Server launch events over the years, but this one was quite different. While it wasn’t a real launch event, it was marketed as a hands-on event to learn about the newest release of SQL Server. The hands-on part for everyone to breakup into teams of five to solve a pre-determined problem. Basically, this was hackathon for SQL Server, and something I’ve never done before.

We started early in the morning with a few presentations about SQL Server 2016. Kevin Feasel (b|t) spoke about the new features, and SQL Server MVP Rick Heiges (b|t) spoke about a real-world win using columnstore indexes in SQL Server 2016. Just before our lunch break the hack was revealed; the basics of which were simple.

First, you are given a set of data set; just five tables containing the following information.

• Virtual Chapter membership (with member location)
• SQL Saturday Registration Data and session/track
• PASS Membership Data
• Multi-year Summit Data including title, track, score etc
• Multi-year Summit Registration data including geographic location of attendee

Then you must design a solution to answer these questions.

• From how far away do attendees travel to SQL Saturday? Are there any geographic trends to the distances traveled?
• Does the SQL Saturday session data provide any insight into what sessions are chosen for Summit? Are there any trends in session or topic content that can be established?
• Are there are geographical insights that can discerned? Do the sessions presented at SQL Saturday help predict popularity or selection of sessions at Summit?
• Does virtual chapter member data provide any insights into PASS membership growth?

The judges score each solution based on the following criteria.

• Use of new features in SQL Server 2016
• Usefulness of the dashboard in Power BI/visualizations for the community
• Completeness of the solution
• Innovativeness of solution
• Bonus points for mobile friendliness

Your solution must be completed in 3 hours.

On paper this all sounds pretty easy, but in practice it was quite hard. I am no BI developer and the other members of my team did not have any expertise in that area either, but we still managed to create a solution and have fun doing so.

The first issue was had was how to combine our development work on the same database. This one was easy…just use Azure. In the span of about 30 minutes, I spun up a new Azure VM with SQL Server 2016 pre-installed, uploaded the database, setup logins, and opened the appropriate ports. I then gave my team members the URL and credentials so they each could connect from their laptops.

One of my team members, Dhruv, wanted to get SQL Server R Services installed to analyze the data set. Machine learning was his specialty, and since R Services is a new feature for SQL Server 2016, we thought this would be a good place to start. However, this proved to be mistake for our team. We spent way too much time trying to get it setup. This was mainly do to the the need to install the R components without an internet connection, or I should say a slow connection. I wish we could have implemented this, because Dhruv had some really good ideas. Even without R Services, he was able to create some nifty reports using Power BI.

One of my other team members, Mike, spent a lot of time trying to understand the data, and how to query it to produce the data for our reports. I’m not sure if this was by design, but let me just say the data set was horrible and every team complained about the time needed to clean it up to the point of being useful. Either way, it was just one of the many problems that we needed to overcome. Most of Mike's code was used in the Power BI dashboard that Dhruv created, but he was also able to write some code that made some good points about membership growth potential; however, we did not have time to build a report for it.

Our team (Team Tiger) finished our solution and presented to the group, but it was clear from the other presentations that we had been over matched. The winning solution was from Team Cheetah who had some unique insights into the data and designed their reports to reflect that detail. Not to mention, their presentation went into a lot of detail about what they had learned.

I really liked the entire event, but I wish that we had more time to work on the solution. Three hours seems like a lot, but after thinking about the challenge for a few days, there were so many more ideas that I came up with. For starters, one of the areas to score points was the use of new SQL Server 2016 features. That basically translates into ANY new feature whether it helps you produce a better looking report or not. With that in mind, I could have done the following.

• Enable the Query Store to capture performance metrics from our solution.
• Enabled Row-Level Security so we could the reports show different data based on which user is running it.
• Spin up additional VMs in different Azure datacenters so I could create a load-balanced Availability Group that would provide high availability and better response time for users closer to each datacenter.
• Setup Stretch Database for a single table to store older data in Azure.

While none of these things would have improved our presentation using Power BI, they are tasks that could have been easily implemented by me given my skillset. And by implementing them it would have definitely scored us a lot more points for the use of new SQL Server 2016 features. This is the big lesion that I learned from the event…always play to your strengths. Don’t try to learn a new skill in a few hours, just use the ones you already have. It will be a much better use of your time, and will most likely produce a better end result.

As I said, this was my first hackathon of any kind, but now I can’t wait to attend another one; especially one that deals with SQL Server.

Database Restore Fails with Msg 3154

Have you ever tried to restore over an existing database only to receive the following error message?

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'AdventureWorks2012' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

We could easily avoid this error by using the WITH REPLACE option in the RESTORE DATABASE command, but we want to know why this error occurs and how SQL Server knows they are not from the same family. The answer is the database_guid.

The database_guid is a GUID that is unique to every database; even across servers. If you restore a backup to another server, the restored database will get a new database_guid. You can view this value for every database by looking in the catalog view sys.database_recovery_status.

SELECT DB_NAME(database_id), database_guid
FROM master.sys.database_recovery_status;

If the database_guid is different for every database regardless of which SQL Server it’s on, then how does the restore still know the backup and database are from two different families? The answer is the family_guid.

The family_guid is the original database_guid when the database was first created, and does not change for the life of that database. When you issue a RESTORE DATABASE command, SQL Server will compare the family_guid to the database_guid if you are attempting to restore over an existing database. When those numbers don’t match, you will receive that error. You can get the family_guid from the same catalog view.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM master.sys.database_recovery_status

You can use the following code to pull the family_guid from the backup and then compare it to the database you are trying to overwrite.

-- Build a temp table to store the backup file header information.
IF OBJECT_ID('tempdb..#BackupHeader') IS NOT NULL
    DROP TABLE #BackupHeader;
GO

CREATE TABLE #BackupHeader (
     BackupName nvarchar(128)
    ,BackupDescription nvarchar(255)
    ,BackupType smallint
    ,ExpirationDate datetime
    ,Compressed bit
    ,Position smallint
    ,DeviceType tinyint
    ,UserName nvarchar(128)
    ,ServerName nvarchar(128)
    ,DatabaseName nvarchar(128)
    ,DatabaseVersion int
    ,DatabaseCreationDate datetime
    ,BackupSize numeric(20,0)
    ,FirstLSN numeric(25,0)
    ,LastLSN numeric(25,0)
    ,CheckpointLSN numeric(25,0)
    ,DatabaseBackupLSN numeric(25,0)
    ,BackupStartDate datetime
    ,BackupFinishDate datetime
    ,SortOrder smallint
    ,CodePage smallint
    ,UnicodeLocaleId int
    ,UnicodeComparisonStyle int
    ,CompatibilityLevel tinyint
    ,SoftwareVendorId int
    ,SoftwareVersionMajor int
    ,SoftwareVersionMinor int
    ,SoftwareVersionBuild int
    ,MachineName nvarchar(128)
    ,Flags int
    ,BindingID uniqueidentifier
    ,RecoveryForkID uniqueidentifier
    ,Collation nvarchar(128)
    ,FamilyGUID uniqueidentifier
    ,HasBulkLoggedData bit
    ,IsSnapshot bit
    ,IsReadOnly bit
    ,IsSingleUser bit
    ,HasBackupChecksums bit
    ,IsDamaged bit
    ,BeginsLogChain bit
    ,HasIncompleteMetaData bit
    ,IsForceOffline bit
    ,IsCopyOnly bit
    ,FirstRecoveryForkID uniqueidentifier
    ,ForkPointLSN numeric(25,0) NULL
    ,RecoveryModel nvarchar(60)
    ,DifferentialBaseLSN numeric(25,0) NULL
    ,DifferentialBaseGUID uniqueidentifier
    ,BackupTypeDescription nvarchar(60)
    ,BackupSetGUID uniqueidentifier NULL
    ,CompressedBackupSize bigint
    ,containment tinyint NOT NULL
);

--SQL Server 2014/2016 have three extra columns in the file header.
IF (@@MICROSOFTVERSION/POWER(2,24) > 11)
ALTER TABLE #BackupHeader
ADD  KeyAlgorithm nvarchar(32)
    ,EncryptorThumbprint varbinary(20)
    ,EncryptorType nvarchar(32);
GO

-- Insert file header info into the temp table.
INSERT #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\SQL_Backup\AdventureWorks2012.bak''');
GO

-- Compare the family_guid values.
SELECT DatabaseName,FamilyGUID FROM #BackupHeader;
GO

SELECT DB_NAME(database_id),family_guid FROM sys.database_recovery_status
WHERE database_id = DB_ID('AdventureWorks2012');
GO

As you can see they do not match, which is why we get the error.

BONUS INFO:
There is one case where the database_guid can be the same even for different databases. If you detach a database, make a copy of the MDF and LDF files, and then re-attach those files as a different database name, the database_guid values will be the same.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM sys.database_recovery_status;

As you can see, the database_guid and family_guid are the same for all three copies of the AdventureWorks database.

Additional resource about the Database and Family GUIDs:
https://msdn.microsoft.com/en-us/library/ms178575.aspx
https://msdn.microsoft.com/en-us/library/ms178536.aspx

Create a Whitelist for SQL Server

UPDATED - May 16, 2017 - Please review the updated code here.

In my previous post, Configuring the Azure SQL Database Firewall, I discussed how you can configure the firewall rules to manage access to the SQL Server. Today, I wanted to demonstrate how you can accomplish the same thing with your on-premise SQL Servers by creating a whitelist.

So let’s start off by defining “whitelist”. According to the American Heritage Dictionary, it is “a list of people or organizations that have been approved to receive special considerations”. In our example, that “special consideration” is access to the SQL Server only if your workstation IP address is on the whitelist.

So why would we care about this? Why not just manage access to SQL Server the normal way with logins and passwords. Here is one example. Let’s say you have a company policy that prohibits anyone from using a common login to connect to a SQL Server. But your application uses a single SQL login to make its connection to SQL Server, and EVERY developer in the company knows the password. Even though there is a written policy in place, what would prevent one of those developers form connecting to SQL Server to fix a bug, or worse, change data to circumvent the application logic.

A whitelist will define which logins are allowed to connect to SQL Server from a specific IP address. Using our scenario from above, we can walk through an example. First we need to create the whitelist table to enforce this policy: the application login (WebSiteLogin) should only be allowed to connect to SQL Server if it originates from the web server’s hostname (webserver1) and IP address: 192.168.100.55.

USE master;
GO

IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
    DROP TABLE dbo.WhiteList;
GO

CREATE TABLE dbo.WhiteList
(
     Id INT IDENTITY(1,1) PRIMARY KEY
    ,LoginName VARCHAR(255)
    ,HostName VARCHAR(255)
    ,HostIpAddress VARCHAR(15)
);
GO

Next, we need to add an entry to allow access.

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
SELECT 'WebSiteLogin','webserver1','192.168.100.55';
GO

Just creating the whitelist does nothing. That’s why we need to use a logon trigger to enforce the whiltelist rules.

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE 
         @LoginName varchar(255) = ORIGINAL_LOGIN()
        ,@HostName varchar(255) = HOST_NAME()
        ,@HostIpAddress varchar(15);

    SELECT @HostIpAddress = client_net_address 
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID;

    IF 
    (
        SELECT COUNT(*) FROM dbo.WhiteList
        WHERE 
        (
            (LoginName = @LoginName) OR (LoginName = '*')
        )
        AND
        (
            (HostName = @HostName) OR (HostName = '*')
        )
        AND
        (
            (HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*')
        )
    ) = 0
    ROLLBACK;
END;
GO

The logon trigger simply compares the user’s login name, hostname, and IP address to what’s in the WhiteList table. If there is a match, then access is allowed. If not, then the connection is terminated via a rollback. To test this, we can attempt to login using WebSiteLogin from an IP address other than 192.168.100.55.

Immediately, we are greeted with a logon error message.

In addition to comparing the logins, the code also adds a wildcard option using the asterisk (*).

SELECT * FROM dbo.WhiteList;
GO

Line 1 has an asterisk for LoginName and HostIpAddress. This means that any login from any IP address is allowed to connect as long as the HostName is dbserver. Line 2 has an asterisk for HostName and HostIpAddress. This means the sa login is allowed to connect from any hostname or IP address. Line 3 is the example we used above. An asktrisk in all three columns would allow any login from any hostname or IP address to connect.

In the event you lock yourself (or everyone) out of the SQL Server, there is a quick way to restore access. You’ll need to connect to SQL Server using the Dedicated Admin Connection, either through Management Studio or the SQLCMD command line using a login with SysAdmin permission. Once connected, you can disable the logon trigger.

From the example above, you can see how quickly you can implement your own firewall for your on-premise SQL Servers using a whitelist and a logon trigger.

Additional resources about Logon Triggers:
https://msdn.microsoft.com/en-us/library/bb326598.aspx

Configuring the Azure SQL Database Firewall

Azure SQL Database is a Platform as a Service (PaaS) that provides a relational database for use over the internet. That sounds super cool and easy to use. But wait, there’s one word I’d like to highlight in that first sentence: “internet”. Anyone with an internet connection could access your database. Now that’s no cool. So how does Microsoft keep your database safe? The answer is a multipronged approach of using encryption, authentication, authorization, and firewalls.

Continue reading...

A New Achievement

To celebrate a recent achievement, I have decided to create a whole new section to my blog that will be dedicated to the subject. Please follow the link to learn more.

Continue reading...

Speaking at SQL Saturday #452 - Charlotte, NC - October 17

In case you missed it last week, there will be another SQL Saturday this weekend in Charlotte, NC. If you are in the area, please come out to this free training event to hear a bunch of great professionals talk about SQL Server. You can register for the event here: http://www.sqlsaturday.com/452/eventhome.aspx.

I will be presenting one session on Performance Monitoring Tools.

No Money for Performance Monitoring Tools? No Problem!
So you like the idea of using one of the commercially available performance monitoring tools but can’t convince your boss to spend the money? Then join me as we explore some absolutely free tools born right out of Microsoft’s customer support teams: DiagManager and SQLNexus. We will see just how easy it is to use them to collect and analyze performance data from your SQL Servers. These tools can get you started quickly without having to deal with the red tape.

Speaking at SQL Saturday #445 - Raleigh, NC - October 10

I will be speaking this weekend at SQL Saturday #445 - Raleigh, NC. If you are in the area, please come out to this free training event to hear a bunch of great professionals talk about SQL Server. You can register for the event here: http://www.sqlsaturday.com/445/eventhome.aspx. So come on out and meet some great people who might be able to help you solve your worst problems.

I will be presenting two sessions on Policy Based Management and Performance Monitoring Tools.

Policy Based Management - Beyond the Basics
Policy Based Management is a great feature of SQL Server and easy to implement; however, for most organizations you need a way to customize it to your enterprise. Join me for this session as we take a deeper dive into PBM to see how you can customize polices, evaluate those polices across your entire enterprise environment, track those changes over time, and deliver reports to management showing your progress.

No Money for Performance Monitoring Tools? No Problem!
So you like the idea of using one of the commercially available performance monitoring tools but can’t convince your boss to spend the money? Then join me as we explore some absolutely free tools born right out of Microsoft’s customer support teams: DiagManager and SQLNexus. We will see just how easy it is to use them to collect and analyze performance data from your SQL Servers. These tools can get you started quickly without having to deal with the red tape.

PSSDiag Configuration Manager – A Companion for SQLNexus

Have you ever called Microsoft Customer Support for help with a SQL Server performance issue? The engineer will typically send you a self-extracting exe file with instructions to unzip the file on the server and run the pssdiag.cmd file to collect data while you reproduce your issue. Afterwards, you’re supposed to zip up the contents of the output folder and send it back to the engineer. How did the engineer compile that collection code so quickly? The answer is PSSDiag Configuration Manager, or simply DiagManager.

DiagManager is just a tool that you use to configure which performance items are to be collected by the SQLDIAG utility. The configuration files that come with SQLNexus (see my previous post) can be edited by hand, but sometimes it is much easier to use a GUI, and that’s where DiagManager can help you.

Version 12.0.0.1001 is the latest and to get it installed, just download it from codeplex and run SetupX86.msi. Go to the Start Menu and click on PSSDiag Configuration Manager to launch the program. Initially, you’ll get a warning screen to remind you the SQL client tools are required to run the collector. Just click OK to continue.

The first thing you’ll likely notice is a line of tabs across the top for different versions of SQL Server. Even though there are tabs for SQL Server 7.0 and 2000, they are no longer supported by this tool. If you really need a collection tool for those old version, then follow this link. But on the bright side, this latest version supports up to SQL Server 2014.

To get started using DiagManager, choose your version of SQL Server by selecting a tab.

Next, select the type of processor that your server has; for example, Intel or AMD. Itanium-based processors are no longer support for current versions of SQL Server. My server uses AMD processors, so I’ll need to click the AMD button.

Now you will need to enter the Machine name and the Instance name that will be monitored. The default values “.” and “*” will capture data for all SQL Server instances on the local machine. Typically for troubleshooting you won’t need to capture data for all instances. If your SQL Server installation is running on a Windows Failover Cluster, then you need to enter the SQL Server Virtual Name and not the physical host name. You’ll also need to specify the credentials used to connect to SQL Server. In my example, I’ll be monitoring a stand-alone instance named TRON\SQL2012 and connecting using Windows Authentication.

The section below that contains the Machine-wide Diagnostics are the counters you would normally see in Performance Monitor. All of the counters for SQL Server are checked by default as well as several for the Windows OS. At this point, you can add or remove counters that you want to capture. You can also specify the max file size (in MB) as well as the collection interval. The fault values are 256MB and 5 seconds.

Unchecking the Perfmon box will disable the collection of all perfmon counters.

The three check boxes above the perfmon box are for the Windows Event Logs. By default, DiagManager will only grab the event logs when SQLDIAG is shutdown; however, you can check the Startup box and it will grab them at both startup and shutdown. This is helpful if you need to do a before after comparison of the Windows Event logs. The collected data will be saved in the output folder as comma-separated (CSV) files. Unchecking the Event Logs box will disable the collection of the event logs.

In the top right half of the window, you will see Instance-specific Diagnostics; aka SQLDIAG and SQL trace. By default, SQLDIAG will only collect its data at shutdown, but you can check the Startup box to get before and after collections. This works the same as the event log collection. If you want to see the TSQL code that is used to generate this output, then just open the MSDiagProcs.sql file in Management Studio. Unchecking the SQLDIAG box will disable the collection of the SQLDIAG data.

Below SQLDIAG, you will see three tabs: Database Engine, Analysis Services, and XEvent. The Analysis Services tab is disabled for the public version of DiagManager, and in the current version, the XEvent tab should not be used to collect database engine data. This leaves the Database Engine tab, which is controls the events that will be captured using a SQL Trace. If you have ever used SQL Profiler to create a trace, then you should be very familiar with these options. Simply browse through the list to check or uncheck the items you need to collect.

Above the list of trace events, you will see a Trace checkbox and two dropdown lists. Unchecking the Trace box will disable the collection of any trace data. The “Max file size” dropdown just controls how large the trace files will get before they rollover. The default is 350MB. The Template dropdown list is a way to quickly check/uncheck events that will be collected. There are three templates included: General Performance, Details Performance, and PSSReplay. The PSSReplay is similar to the replay template within Profiler. General vs Details events is basically batch-level events vs statement-level events. The coolest thing about the trace templates, is that you can create your own. To do so, navigate to the TraceTemplates folder in the DiagManager install folder (C:\Program Files (x86)\Microsoft\Pssdiag\TraceTemplates). Open the folder that matches your SQL version, and make a copy of one of the XML files. Open that cop in your favorite XML editor and just change the “enabled” value to “true” for the events to collect and “false” for the events you do not want to collect, and then save the XML file.

The next time you open up DiagManager you should see your custom template in the dropdown list.

One big word of caution; a SQL Trace will cause the HEAVIEST overhead to your SQL Server, so be careful what items you choose to capture. If you are worried about adding too much overhead to your SQL Server, then consider using the “XEvent Batch_RPC Only” option in the Custom Diagnostics section. Checking this option will create an Extended Events session that will only capture “rpc_completed” and “sql_batch_completed” events. An Extended Events sessions adds far less overhead than a SQL Trace, but DO NOT enable both the SQL Trace box and the XEvent Batch_RPC option. Doing so would run both a trace and xevent session and add a lot of overhead to your SQL Server.

For the other items in the Custom Diagnostics section, you can select each one to see a description of what they do in the Instructions window. For example, the SQL 2012 Perf Stats script will gather data from DMVs about various items such as: top N queries, missing indexes, statistics, and even Availability Group info.

The Custom Diagnostics section is exactly as it sounds. You can create your own TSQL scripts or other types of custom collectors. Navigate to the CustomDiagnostics folder in the DiagManager install folder (C:\Program Files (x86)\Microsoft\Pssdiag\CustomDiagnostics), and create a new folder a new folder for your custom code. In my example, I created a folder called “SQL Database List” and added three files to the folder.

The first is just a TSQL script that has one command: “SELECT * FROM sys.databases;”. The DatabaseList.cmd is a Windows batch file that calls sqlcmd.exe and runs the DatabaseList.sql file.

The CustomDiag.XML file was copied from another custom folder and edited to reference the TSQL file and define whether it runs at startup or shutdown.

Once you have your custom collector created, close and open DiagManager and you should see your custom collector in the Custom Diagnostics list.

My example just captures a list of the databases, but you could easily create a script that captures data internal to one of your application databases.

Now that you have finally selected all the items that you want to collect, click the Save button at the top of the window. A Save Configuration window will pop up asking you what and where to save the collector files.

The Filename filed is where you want to save the PSSDiag.XML configuration file as well as all the collection scripts and tools. The option to create a CAB file is where DiagManager will compress and save all the files and scripts from the top folder. The CAB file is just a compressed (ZIP) file, and makes it easier to copy your collection tools to a remote server where the CAB file can then be uncompressed.

The last option available in DiagManager is the ability to compare PSSDiag.XML files. Just click the “Compare Config Files” button at the top of the window.

In the popup window, just select the two XML files that you want to compare, click the Compare button, and the output will be shown below.

Once you have created your collection set and/or the CAB file. All you need to do is copy the CAB file to the server to be monitored and execute the pssdiag.cmd batch file.

It will open a command prompt window and kick off each of the collection items you specified. Once you see the message “SQLDIAG Collection started” in green-colored font, then you can go recreate your performance issue.

Once you are ready to stop the collection, just press Ctrl+C. All of the data collected will be placed in the output folder, including any custom scripts that you may have created. That output folder can then be copied back to your workstation and fed into SQL Nexus for analysis. See my previous post for a detailed tutorial.

DiagManager definitely has a lot of configuration options to choose from, so learning which ones to use might take time. A good way to get started it is turn off all options except for one, and then run the data capture. Then you can look at the results in the output folder to really understand what that option does.

DiagManager, as well as SQLNexus, was created by the support teams at Microsoft, and they have made both of them freely available to the public to help in troubleshooting issues, so there’s no excuse not to try them out. I hope this tutorial will help you get started.