Patrick

Apr 182017
 

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.

In the image below, you can see that Blob Auditing is on, but we can not see what actions are being collected.


Using PowerShell, we can easily see the default audit action groups.

Get-AzureRmSqlDatabaseAuditingPolicy `
  -ServerName 'imperialwalker' `
  -DatabaseName 'Lahman2015' `
  -ResourceGroupName 'MCPLABv2'


We can see there are three action groups listed: SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, and BATCH_COMPLETED_GROUP. Without even looking at the documentation, we can assume that we are auditing both successful and failed logins, as well as all successful batches against this database. When you compare the Azure action groups side-by-side with the box product, they line up almost exactly.

So how do we customize it further? Well let’s say our auditing requirements only need to capture changes to structure of the database; for example, an ALTER TABLE. First, we need to remove BATCH_COMPLETED_GROUP and add DATABASE_OBJECT_CHANGE_GROUP. To accomplish this, we will use Set-AzureRmSqlDatabaseAuditingPolicy.

Set-AzureRmSqlDatabaseAuditingPolicy `
  -ServerName 'imperialwalker' `
  -DatabaseName 'Lahman2015' `
  -ResourceGroupName 'MCPLABv2' `
  -AuditActionGroup `
     'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP' `
    ,'FAILED_DATABASE_AUTHENTICATION_GROUP' `
    ,'DATABASE_OBJECT_CHANGE_GROUP'

To verify the changes were successful, we run Get-AzureRmSqlDatabaseAuditingPolicy again.


Now, we’ll be able to collect audit records anytime a CREATE, ALTER, or DROP is executed against a database object. However, let’s say we need something more granular. In our sample database, we have a table that stores salary data and we need to audit anything that touches it. We ae already covered with schema changes by the action group, DATABASE_OBJECT_CHANGE_GROUP, but that doesn’t audit DML changes. Adding BATCH_COMPLETED_GROUP would capture what we need, but that would cover all tables and we have a requirement for just one. This is where we can audit actions on specific objects. In the statement below, we just add an audit action for SELECT, INSERT, UPDATE, and DELETE on the Salaries table.

Set-AzureRmSqlDatabaseAuditingPolicy `
  -ServerName 'imperialwalker' `
  -DatabaseName 'Lahman2015' `
  -ResourceGroupName 'MCPLABv2' `
  -AuditActionGroup `
     'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP' `
    ,'FAILED_DATABASE_AUTHENTICATION_GROUP' `
    ,'DATABASE_OBJECT_CHANGE_GROUP' `
  -AuditAction 'SELECT, INSERT, UPDATE, DELETE ON dbo.Salaries BY public'

To verify the changes were successful, we run Get-AzureRmSqlDatabaseAuditingPolicy again.


If you have multiple objects or actions to audit, then just separate them with a comma, just like the AuditActionGroups parameter. The one key piece to remember is you must specify all audit actions and action groups together with each execution of Set-AzureRmSqlDatabaseAuditingPolicy. There is no add or remove audit item. This means if you have 24 actions to audit and you need to add one more, then you have to specify all 25 in the same command.

Now let’s run a few queries to test the audit. First, we’ll run a simple select from the Salaries table.

SELECT TOP 10 * FROM dbo.Salaries;
GO

Next, we’ll create a view that selects from the Salaries table.

DROP VIEW IF EXISTS dbo.PlayerSalaryByYear;
GO
CREATE VIEW dbo.PlayerSalaryByYear
AS
SELECT
   m.nameLast + ', ' + m.nameFirst AS 'Player'
  ,s.yearID
  ,t.name
  ,s.salary
FROM dbo.Salaries s JOIN dbo.[Master] m ON m.playerID = s.playerID
JOIN dbo.Teams t ON s.teamID = t.teamID AND s.yearID = t.yearID;
GO

Finally, we’ll select from that view.

SELECT * FROM dbo.PlayerSalaryByYear
WHERE Player = 'Jones, Chipper';
GO

Back in the Azure Portal, click on the view button so we can view the captured audit records for the statements we just executed.


What is displayed is one line item for each audit record captured.


Selecting each record will open another blade with the contents of that record. In our example, we have one for the initial SELECT against the table, one for the CREATE VIEW statement, and one for the SELECT against the view which references the Salaries table.


While using the Azure Portal is a quick and easy way to view audit records, the best way to consume the records for reporting is to use the function, sys.fn_get_audit_file(). This is the same function used in the box product. The only difference is we need to specify the Azure URL for the audit log. All audit logs are stored in a container named sqldbauditlogs. In side that container, additional containers server name, database name, and a date/time stamp are created to further organize it. This is something to keep in mind if you plan to programmatically process the audit records.

SELECT *
FROM sys.fn_get_audit_file ('https://mcplabv2storage.blob.core.windows.net/sqldbauditlogs/imperialwalker/Lahman2015/SqlDbAuditing_Audit_NoRetention/2017-04-17/13_48_34_960_0.xel',default,default);
GO


If you don’t know the URL full path, you can use the Azure Storage Explorer to help find it.

As of this writing, there are two DMVs missing from Azure SQL Database: sys.dm_audit_actions and sys.dm_audit_class_type_map. These DMVs allow us to translate the actions_id and class_type values into a readable description. Since they are not available in Azure, I have created my own version of those as user tables within my database: dbo.audit_actions and dbo.audit_class_types. This allows me to join them against the audit function to produce a better report.

SELECT
   a.event_time
  ,aa.name AS 'action_name'
  ,c.securable_class_desc AS 'securable_class'
  ,c.class_type_desc AS 'class_type'
  ,a.statement
  ,a.client_ip
  ,a.application_name
FROM sys.fn_get_audit_file ('https://mcplabv2storage.blob.core.windows.net/sqldbauditlogs/imperialwalker/Lahman2015/SqlDbAuditing_Audit_NoRetention/2017-04-17/13_48_34_960_0.xel',default,default) a
LEFT JOIN dbo.audit_actions aa ON a.action_id = aa.action_id
LEFT JOIN dbo.audit_class_types c ON a.class_type = c.class_type;
GO


If you are familiar with auditing in the box product, then you might be aware that common properties like client hostnames (or IP address) and application names are not captured for each audit record; however, in Azure they are collected and viewable in the columns client_ip and application_name. See the picture above.

Next, let’s create a stored procedure that selects from that view and add an EXECUTE audit action for it.

DROP PROCEDURE IF EXISTS dbo.usp_PlayerSalaryByYear;
GO
CREATE PROCEDURE dbo.usp_PlayerSalaryByYear(@playerName varchar(100))
AS
SELECT * FROM dbo.PlayerSalaryByYear
WHERE Player = @playerName;
GO

Now to add the EXECUTE audit action via PowerShell.

Set-AzureRmSqlDatabaseAuditingPolicy `
  -ServerName 'imperialwalker' `
  -DatabaseName 'Lahman2015' `
  -ResourceGroupName 'MCPLABv2' `
  -AuditActionGroup `
     'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP' `
    ,'FAILED_DATABASE_AUTHENTICATION_GROUP' `
    ,'DATABASE_OBJECT_CHANGE_GROUP' `
  -AuditAction `
     'SELECT, INSERT, UPDATE, DELETE ON dbo.Salaries BY public' `
    ,'EXECUTE ON dbo.usp_PlayerSalaryByYear BY public'

Using Get-AzureRmSqlDatabaseAuditingPolicy, you can see the additional audit action that was added.


Now we need to execute the stored procedure to test.

EXECUTE dbo.usp_PlayerSalaryByYear 'McGriff, Fred';
GO

The Azure Portal shows the two audit records that were captured; one for the execute of the stored procedure and the second for the select on the underlying table.


Using the query below, we can extract some more useful data from the additional_information column. This will show the nested objects and we’ll be able to extract the parent object name.

SELECT
   a.event_time
  ,aa.name AS 'action_name'
  ,c.securable_class_desc AS 'securable_class'
  ,c.class_type_desc AS 'class_type'
  ,a.statement
  ,CONVERT(XML,a.additional_information).value('(/tsql_stack/frame/@database_name)[1]','varchar(100)')
  + '.' + CONVERT(XML,a.additional_information).value('(/tsql_stack/frame/@schema_name)[1]','varchar(100)')
  + '.' + CONVERT(XML,a.additional_information).value('(/tsql_stack/frame/@object_name)[1]','varchar(100)') AS parent_object_name
FROM sys.fn_get_audit_file ('https://mcplabv2storage.blob.core.windows.net/sqldbauditlogs/imperialwalker/Lahman2015/SqlDbAuditing_Audit_NoRetention/2017-04-17/17_32_29_811_0.xel',default,default) a
LEFT JOIN dbo.audit_actions aa ON a.action_id = aa.action_id
LEFT JOIN dbo.audit_class_types c ON a.class_type = c.class_type;
GO


As you can see, Blob Auditing for Azure SQL Database provides us with major improvements over Table Auditing, and gives us the flexibility and granular control that we are used to in the box product.

For more information on the auditing features for Azure SQL Database, follow these links.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqldatabaseauditingpolicy
https://msdn.microsoft.com/library/azure/mt695939.aspx

Apr 132017
 

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.

Get-Module AzureRM -List | Select-Object Name, Version, Path

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 the newer version there properties for AuditAction and AuditActionGroup which were just recently added to Azure.

For more information on installing the AzureRM module, as well as running multiple versions side-by-side, follow these links.
https://docs.microsoft.com/en-us/powershell/azure/install-azurerm-ps
https://github.com/Azure/azure-powershell/releases

Nov 032015
 

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.

All connections to Azure SQL Database use SSL/TLS to protect your data while “in transit”, and you can use Transparent Data Encryption (TDE) to protect your data “at rest”. Authentication and authorization are no different from the on premise version of SQL Server. Authentication just means you must have a valid login to SQL Server, and authorization means you must have permissions on an object; for example, SELECT permission on a TABLE.

The other way Azure protects your data is by use of a firewall. It works like any other firewall; it blocks unauthorized traffic from passing through. By default, Azure blocks ALL traffic to your database. This may sound a bit crazy but it’s no different from the way the Windows firewall works; you must allow access through a port. In Azure SQL Database, it always listens on port 1433, so Azure uses the client’s IP address to authorize access. For example, if your workstation IP address is 192.168.1.5, then you would need to explicitly allow access to your database from that IP.

Azure SQL Database has several ways to configure the firewall. Access can be granted at the server-level or at the database-level. Each level can be managed through the Azure Portal, TSQL, PowerShell, or Rest API. Let’s take a closer look at the Azure Portal and TSQL options.

Configuring the firewall through the Azure Portal offers you two options; one will allow access from any Azure service, and the other will define server-level entries. The first is the “Allow access to Azure services” button. When this option is turned on, it allows any traffic from services within your Azure subscription to pass through. This is usually on by default when your SQL Server is first created.

AzureFirewall1

The second way to manage the firewall through the Azure Portal is by defining server-level entries. On the Firewall settings page, you just need to give the setting a name, and then list the starting and ending range for the IP address. If it’s just a single IP address, then you would use the same value for both. The picture below shows a single entry and a range of IP addresses that are allowed to connect.

AzureFirewall2

When defining the rules through the Azure Portal, you are just creating server-level rules under the hood. If we run a TSQL query against sys.firewall_rules on our SQL Server, we should see 3 entries.

AzureFirewall3

There are the two entries we created: IP Range and Single IP. But there is also a third. That’s the entry for having “Allow access to azure services” set to ON. If we turn that option off and then rerun the TSQL query, we can see that entry for 0.0.0.0 is removed.

AzureFirewall4

If we wanted to create a server-level firewall from TSQL, then we would use the system stored procedure sp_set_firewall_rule.

EXEC sp_set_firewall_rule
     @name = N'TSQL Server Level'
    ,@start_ip_address = '192.168.10.10'
    ,@end_ip_address = '192.168.10.10';
GO

AzureFirewall5

To delete a server-level firewall rule, you can use the Azure Portal or the system stored procedure sp_delete_firewall_rule.

EXEC sp_delete_firewall_rule
     @name = N'TSQL Server Level';
GO

To create the rule, it’s as easy as running a query using the system stored procedure, sp_set_database_firewall_rule, in the context of the user database.

EXEC sp_set_database_firewall_rule
     @name = N'TSQL Database Level'
    ,@start_ip_address = '192.168.100.55'
    ,@end_ip_address = '192.168.100.55'
GO

We can query the sys.database_firewall_rules DMV to verify we have successfully created the rule.

AzureFirewall6

Notice, I have added this rule to the DEMO database, which will give the IP address access to only that database. Now let’s try to connect to the database.

What’s this error? My IP address does not have access to the server? But I just added it as a database-level rule.

AzureFirewall7

This error is because I’m attempting to connect to the default database, master, instead of the database, Demo, that I have explicit access. If I change my connection properties, then I will be allowed to connect to the Demo database.

AzureFirewall8

After connecting, the first thing you will notice is there is only one database listed. Because I only have explicit firewall access to the DEMO database, I can’t even see DEMO2 on the list of databases.

AzureFirewall9

To delete a database-level firewall rule, you can use the system stored procedure sp_delete_database_firewall_rule.

EXEC sp_delete_database_firewall_rule
     @name = N'TSQL Database Level';
GO

In the event you lose all access to your database, either by entering the wrong IP address or removing all of them, there is an easy way to get it back through the Azure Portal. On the Firewall settings page, click the “Add client ip” button and click Save. This will add the IP address of your current client to the server-level rules list. Once you have server-level access, you can then connect to the server and correct the database-level rules.

AzureFirewall101

 

The one thing to remember from all of the firewall settings, is these settings only open ports. There is no setting to deny an IP address.

As you can see, Azure provides several options for configuring the firewall settings for a SQL Database. I also think this is great example of how the Azure Portal can be viewed as nothing more than a different version of Management Studio. It’s just a graphical interface for managing SQL Server databases.

Additional resources:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure

Oct 162015
 

MSOver the past few months, I have been working diligently to learn more about Azure. As a result of my studies, I have successfully passed the Implementing Microsoft Azure Infrastructure Solutions certification exam (70-533). By far, this is the hardest exam I have taken to date.

Over the past 15+ years I have worked very hard to learn as much as possible about SQL Server and the Windows operating systems that it runs on, but that knowledge only took me so far within Azure. I had to look at Azure as an entire suite of products that seamlessly work together, and to successfully pass the exam, I had to learn about each one of them. I found the content around websites to be the toughest to understand. I’m pretty comfortable with how websites are hosted, but I needed to know more on the internals of features such as application settings, diagnostic logs, and monitoring. And of course we can’t forget PowerShell. It wouldn’t be an accurate test without a few PowerShell syntax questions.

The skills we learn over the years tend to be forgotten if we don’t use them. That’s why I’d like to welcome you to the Azure edition of Everyday SQL. I thought the best way to keep my skills up-to-date would be to host a portion of my blog within Azure. Going forward, I plan to post more articles about using SQL Server and other features within Azure.

Before I end this article, there is one thing that does bug me from time to time. It’s the correct way to pronounce “Azure”. It’s a little hard to type out, so I’ll provide you will a link to the Cambridge Online Dictionary where you can play the US version of the pronunciation.

Apr 282015
 

Recently I’ve been learning more about how Azure functions and how it can help my customers. One of the best ways for me to learn about Azure was to build out my own environment using VMs, or Infrastructure as a Service (IaaS). All of that was easy; however, once the VMs were built I soon learned that Azure functions differently than an On-Premise solution.

The most basic network connectivity test that administrator use is the PING command. It is part of the ICMP protocol, but it’s disabled by default on each VM that I deployed. While it was easy enough just to configure the Windows Firewall to allow that traffic through, I decided to search for an alternative.

I found PsPing from SysInternals. It performs the same functionality as PING, but it uses the TCP protocol instead. When working in Azure, it can give you the same functionality without having to reconfigure your VMs.

For example, when I setup a SQL Server instance on one VM, I had difficulty establishing a connection to it from another VM. The standard PING proved to be useless, but PsPing proved its worth. By specifying the server name and a port number, I was able to successfully connect directly to the port number that SQL Server was listening on.

PsPing_1

 

Immediately, I could see the benefit of using this new tool for more than just testing SQL Server connectivity. I can use it to specify any port number for any service. For example, I could ping port 80 if I’m testing a website, port 21 if it were an FTP server, or port 23 for telnet.

As I dig deeper into Azure, I’m sure I will discover many other new utilities that I can add to my own toolbox.

You can download PsPing from here.