2017

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...

Continue reading...

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...

Continue reading...

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...

Continue reading...

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. To check the version of the AzureRM module currently installed on your system, use the following command. The screenshot below shows I am running version 1.4.0 and the output of Get-AzureRmSqlDatabaseAuditingPolicy command-let. Next, I install the latest AzureRM module. After the install is complete, I rerun the same commands. The screenshot below shows that I am now running version 3.8.0 followed by the same Get-AzureRmSqlDatabaseAuditingPolicy command-let. Notice the difference in the output? There are several differences, but in...

Continue reading...