Threat Protection for SQL IaaS VMs using Azure Security Center

by Scott Muniz | Jul 21, 2020 | Alerts, Microsoft, Technology, Uncategorized

This article is contributed. See the original author and article here.

As enterprises are working to reduce their attack surface area, one area that is often overlooked is the potential database vulnerabilities and detection of anomalous activity that could indicate a sign of intrusion. In the series of blog post, we will walk you through different scenarios on, how Azure Security Center protects SQL server hosted on either Azure VMs, Azure Arc and On-prem.

Advanced Data Security (ADS) is one of many features that is included in Azure Security Center and falls under Cloud Workload Protection Platform (CWPP) capability (read this article to understand CWPP). Advanced data security for SQL machines is an extension of Azure Security Center’s advanced data security package that’s already available for Azure SQL Databases, Synapse, and SQL Managed Instances. This feature (by the time this blog was written it was in public preview) brings the same level of protection that were introduced for SQL servers on Azure VMs which includes SQL Injection vulnerabilities, brute-force against SQL credentials, and much more.

Cloud security posture management and proactive threat protection with Azure Security Center helps SOC (Security Operation Center) operators to get handle on threats and keep pace with attackers and Advanced Data Security (ADS) for SQL servers on Azure Virtual Machines, is a unified package for advanced SQL security capabilities which is in public preview at the time of this writing.

This public preview feature that was announced last year includes functionalities for identifying and mitigating potential database vulnerabilities and detecting anomalous activities that could indicate threats to your database:

1. Vulnerability Assessment
2. Advanced Threat Protection

Breakthroughs in big data and machine learning make it possible for Security Center to detect anomalous database access and query patterns, SQL injection attacks, and other threats targeting your SQL databases in Azure. With Azure Security Center you receive alerts on suspicious activity and recommended actions for investigating and mitigating these threats.

Advanced Data Security pricing aligns with Azure Security Center standard tier, where each protected server or managed instance is counted as one node. Newly protected resources qualify for a free trial of Security Center standard tier. For more information, see the Azure Security Center pricing page.

In this blog post, we will be covering how Azure Security Center protects SQL IaaS machines hosted on Azure.

1. SQL server on an Azure VM

SQL Server on Azure Virtual Machines enables you to use full versions of SQL Server in the cloud without having to manage any on-premises hardware. SQL Server virtual machines (VMs) also simplify licensing costs when you pay as you go. Refer this article for more understanding, plus instructions to spin the Azure SQL VM.

As a prerequisite, make sure the optional bundle (SQL servers on Machine) is enabled in Security Center’s pricing and settings page (Subscription) as shown in ‘Image 1’

Image 1: Pricing settings in Security Center

If you have Auto-provisioning turned on (Refer, Image 2), Azure Security Center will automatically provision the Log Analytics Agent on the Azure VMs.

Image 2 : Auto Provisioning 

In an instance where you don’t have Auto-Provisioning turned on, you would have to manually install the agent. Refer this article for step-by-step process of the installation.

You may choose to collect the data in a default workspace that Azure Security Center creates or select your own workspace. Advanced Data Security for SQL servers on machines will be enabled on all SQL servers connected to the selected workspace. The protection will be fully active after the first restart of the SQL Server.

In this sample scenario, a SQL IaaS VM (sqlserver) was provisioned. Azure Security Center installed the Log Analytics agent and started gathering data to provide security recommendations for your SQL VM under Recommendations section (as shown in ‘Image 3’). Furthermore, you can select the security option at the left pane and view recommendations from Security Center and alternatively, you may navigate to Azure Security Center for more information on a recommendation. You can enable threat protection for Azure SQL Database SQL servers at either the subscription level or resource level.

Image 3: Recommendations

You can also filter just the SQL recommendations (Data & Storage hive) from Resource security hygiene section (as shown in the Image 4)

Image 4: Data & Storage 

Make sure to address and review all security recommendations for the SQL databases. In my example, I have these unhealthy resources which is grouped beautifully by Severity.

1.1 Executing the Attack

Hackers are always trying to find workloads that are exposed to the Internet and databases that are very common target. As part of this type of attack, it is common to see scanning to discover public IP addresses that have SQL workloads running on it, and once they find that they will move forward to try to crack their password using different methods, such as brute force attack. “Brute Force”, which is a type of attack that attempts to calculate or guess valid username/password combinations to gain unauthorized access to a host. Oftentimes, the sheer amount of Brute Force attempts can effectively result in DDoS of the targeted system. In case threat actors can compromise the SQL database and gain access to it, they will likely find a wealth of valuable information that includes, personally identifiable information, credit card numbers, intellectual property, etc. Even if the database does not have much information, a successful attack on an insecurely configured SQL installation can be leveraged to get full system admin privileges.

For this example, a PowerShell script was utilized to simulate a SQL brute force attack against a SQL database that is connected to the Azure Security Center.

The brute force attempts occurred over TCP port 1433, which was exposed on a public facing interface. TCP port 1433 is the default port for SQL server.

Note: It is a very common recommendation to change the SQL default port 1433, this may impart a “false sensation of security”, because many port scanning tools can scan a “range” of network ports and eventually find SQL listening on ports other than 1433.

The PowerShell script used in this case is simulating the attack by trying to login with ‘sa’ account and trying with a list of commonly used password defined in the text file or the inline array of the script. (In a real-world scenario, once the attacker finds TCP/UDP ports open, it starts to brute force the login with the set of most common passwords used by database administrators to perform a successful attack)

Assuming the attack was successful, Azure Security Center was able to detect and report the attack under Security alerts dashboard as shown in the ‘Image 5’

Image 5 : Security Alerts

You can further investigate the alert to view more information of the affected VM name, activity name, Environment, state of alert and more. The first step is to click on the alert itself, and see how many instances does that alert has occurred, as show in the ‘Image 6’

Image 6 : Brute Force attempt alert

You can further select the attacked resource to understand the extent and the details of the attack. Alert details will give you more information that could help during investigation of a security alert, such as, IP addresses, related processes, user accounts and more. ‘Image 7’ below has an example of how this looks like:

Image 7 : Security Alert details 

When you further navigate to the “Take action” tab in the right pane, you can benefit from information like, remediation steps for this alert to mitigate the threat, prevent future attacks by applying the security recommendations and increase security posture.

You can create a logic app defining a workflow automation so that, when an alert is created in Security Center and if it matches the evaluation criteria that you’ve configured in an automation, then the logic app triggers. For example, you might want your Logic App to run when a security alert that contains “SQL” is generated. You can also run Logic Apps manually when viewing a security alert or any recommendation that offers Quick Fix remediation.

You will also notice the option to Suppress the future alerts with similar characteristics if the alert is not relevant for your organization as highlighted below in ‘Image 8’

Image 8: Suppress Similar Alerts

It is recommended to configure Azure subscription(s) to receive future alerts and email notifications from Azure Security Center. Please refer to the article that explains how to configure the email settings. Below is an example of the email alert received from ASC when this SQL incident was detected:

Image 9: Email Notification

In the next blog posts, we will discuss on how you can leverage Azure Security Center to protect your SQL IaaS VMs hosted on Azure Arc and On-premises. Stay Tuned!!

Special thanks to:

Yuri Diogenes, Senior PM, CxE Security – ASC Team for reviewing this post.

How to connect to Azure Database for MySQL using Managed Identity of Function App

by Scott Muniz | Jul 21, 2020 | Alerts, Microsoft, Technology, Uncategorized

This article is contributed. See the original author and article here.

Managed Identity are automatically managed by Azure and enable you to authenticate to services that support Azure AD authentication, without needing to insert credentials into your code. You can learn more about this in the following document: how to connect with Managed Identity to Azure Database for MySQL 

The blog will outline how to use Function App System Managed Identity to connect to Azure Database for MySQL.

• Step 1: Configure Azure AD Authentication for MySQL
• Step 2:Enable Managed Identity for the Function App
• Step 3: Find the Managed Identity GUID and then create a user in MySQL
• Step 4: Writing code for function app
• Step 5: Test the function app

Step 1: Configure Azure AD Authentication for MySQL

First, we need to make sure that the Azure Database for MySQL is configured for Azure AD authentication. If your server is not already configured, follow the steps in this how-to guide: Use Azure Active Directory for authentication with MySQL

Step 2: Enable Managed Identity for the Function App

Next, enable Managed identify for a Function app. For this you need to log in to the Azure Portal and then select the Function App which you will be using. Select Identity under Settings. Change the Status to On.

Step 3: Find the Managed Identity GUID and then create a user in MySQL

Next, create a MySQL user for your Managed Identity. For this we need to get the application ID. For that, go to Azure Active Directory from the Azure portal and then go to Enterprise Applications.

Once you select the application, you can will be able to copy the Application ID.

Once you have the Application ID, log in to Azure Database for MySQL using the AAD login and execute the below query. This query ensures the managed identity now has access when authenticating with the username myuser. Replace the Application ID (GUID) in the query with your own. 

SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER 'myuser' IDENTIFIED BY 'cc5e6297-6bdc-4608-9ab5-ddb0c81e2a7e';
--I would also recommend to GRANTS necessary permission in DB
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'myuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Step 4: Writing code for the Function app

Next, let’s see how to get an access token using the Function app System managed identity and use it to call Azure Database for MySQL. Azure Database for MySQL natively supports Azure AD authentication, so it can directly accept access tokens obtained using managed identities for Azure resources. When creating a connection to MySQL, you pass the access token in the password field.

Here’s a .NET code example of opening a connection to MySQL using an access token. This code must run on the Function App to access the Function App system-assigned managed identity’s endpoint. .NET Framework 4.6 or higher or .NET Core 2.2 or higher is required to use the access token method.

Please note the community MySQL Connector/NET (MySql.Data)does not support cleartext plugin. You can use MySqlConnector instead. 

The below is a sample code. Replace the values of “Servername”, “User”, and “Database” with your own server credentials. 

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using MySqlConnector;
using Microsoft.Azure.Services.AppAuthentication;

namespace myfunappmysqlapp1
{
    public static class mysqlapp
    {
        private static string Host = "<Servername>.mysql.database.azure.com";
        private static string User = "myuser@<Servername>";
        private static string mydatabase = "db";
        [FunctionName("mysqlapp")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");

            /*Gettting the Token*/
            var sqlServerTokenProvider = new AzureServiceTokenProvider();
            var SqlAccessToken = await sqlServerTokenProvider.GetAccessTokenAsync("https://ossrdbms-aad.database.windows.net");
            //log.LogInformation(SqlAccessToken); /*For troubleshooting in case you need to print the token */
            log.LogInformation("Connecting to database.");

            //
            // Open a connection to the MySQL server using the access token.
            //
            var builder = new MySqlConnectionStringBuilder
            {
                Server = Host,
                Database = mydatabase,
                UserID = User,
                Password = SqlAccessToken,
                SslMode = MySqlSslMode.Required,

            };

            using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                log.LogInformation("Opening connection using access token...");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "SELECT VERSION()";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            log.LogInformation("nConnected!nnMySQL version: {0}", reader.GetString(0));
responseMessage = reader.GetString(0);
                        }
                    }
                }
            }
            
             responseMessage = "The versions is Azure Database for Mysql is:" + responseMessage;
            return new OkObjectResult(responseMessage);
        }
    }
}

Step 5: Test the Function app

Once you publish the Application, you can test the with the following steps below. 

From the Azure Portal, go to Function App you published and select Functions.

Once the Function is selected, you can select Code+Test and click on Test/Run.

Click on Run on the on the pop up window

You can see the output in terminal for App Insights.

We can see that we connected to the database and could successfully run the query.

Hope this helps!

Sudheesh Narayanaswamy

Technical Support Engineer

Exploring Azure SQL Managed Instance Management Pack

by Scott Muniz | Jul 21, 2020 | Alerts, Microsoft, Technology, Uncategorized

This article is contributed. See the original author and article here.

We released System Center Operations Manager Management Pack for Azure SQL Managed Instance recently. It provides comprehensive monitoring capabilities for SQL Managed Instance. If you have a hybrid data environment, you can use a single monitoring solution, SCOM, to monitor all your data assets as we already have management packs for SQL Server and Azure SQL DB.

Discovery

After you install the management pack (MP), there are two templates to get you started: Manual and Automatic Discovery. As the names suggest, you can either discover your instances by adding them manually or automatically discover instances in a given subscription. Both options have wizards that walk you through this setup. We will not explore them in this post as the instructions in the MP guide are very detailed. I just want to point out that you can use either Azure Active Directory (AAD) or SQL Authentication to connect to the instances. You can use a connection with sysadmin rights or there is an option to use the least amount of privileges to monitor the product just like SQL Server MP. This is also explained in detail in the MP guide.

Monitoring

Once you complete all the steps in the wizard, you will be able to see the instances in the SCOM dashboard under Monitoring. Under Microsoft SQL Server folder, you’ll see a subfolder for Microsoft Azure SQL Managed Instance.

Navigate to Microsoft Azure SQL Managed Instance -> Managed Instances -> Database Engines

You can see the instances listed with some details. You can select which columns to display here by right-clicking and selecting Personalize View from the list. If you are using geo-replication, you will see that Geo-Replication Replica Role is listed in Detailed View. It can be Primary or Secondary (or blank if this instance is not participating in geo-replication).

As usual, you can drill down by double-clicking on the instance rows. This will take you to the list of individual monitors and their status. Most of these are the monitors you are familiar with from SQL Server MP.

There is a brand new monitor called Instance Free Storage Space Left. It uses percentages and is set to warn if it goes below 20% but, of course, it is customizable just like all the other monitors. It also shows the actual data in MB.

Navigate to Microsoft Azure SQL Managed Instance -> Managed Instances -> Databases

If you double-click on a geo-replicated database, you will see the new monitor, Geo-Replication Status, under Availability. All the information is on the right hand side. As you can see it can be in one of three states:

• CATCH_UP: This is the healthy state.
• SEEDING: Seeding is happening but until it competes, you can’t connect to the secondary database.
• PENDING: Not in an active continuous-copy relationship. Usually indicates bandwidth related issues.

Also on the Databases view, right-click on a geo-replicated database and select Open->Performance View. You will find the new counter Geo-Replication Lag (sec) along with other counters. This rule collects performance metric for the primary database only and shows the time difference between transactions committed on primary database and persisted on secondary database.

Other Views

You can also explore Managed Instance Agents and Memory-Optimized Data folders for monitors specific to those areas. We will not explore those in this post.

Summary Dashboard under Microsoft Azure SQL Managed Instance provides the usual status-at-a-glance view of everything. Just like other SQL MPs, you can drill down to get to specific monitors/rules and customize it by adding or removing tiles to fit your needs.

Active Alerts view under Microsoft Azure SQL Managed Instance lets you view all your active alerts in a single place just like other SQL MPs.

Summary

Azure SQL Managed Instance Management Pack is very similar to other SQL MPs but also introduces product specific monitors and rules.