5 ways to secure your SQL data in Microsoft Azure

Data security in the cloud is of chief concern not only to healthcare and financial services, but anyone with sensitive data of any kind that should only be disclosed to authorized parties. No discussion of enterprise security would be complete without a look at data protection and governance.

For purposes of this discussion, data comes in two forms:

  • Structured. Structured data refers to kinds of data with a high level of organization, such as information stored in a relational database, as in Microsoft SQL Server.
  • Unstructured. Unstructured data refers to data that is not contained in a database or some other type of data structure. Examples include email messages, Word documents, PowerPoint presentations and instant messages.

Important considerations in data protection and governance include data classification and rights management, encryption at-rest and in-flight, as well as management and storage of encryption keys and other secrets related to securing data.

Securing Structured Data In-Flight & In Use

SQL Server 2016 (both SQL in VMs and Azure SQL) introduces some new capabilities to prevent unintentional leakage of data by misconfigured applications or security controls. Key highlights are listed below:

#1 Always Encrypted:

This is a client-side encryption capability, enabling the application to encrypt data so the SQL server (or service if using Azure SQL) can never see the data. This is particularly useful for protecting content such as SIN/SSN, Credit Card, and private health identifiers.

Always_Encrypted

#2 Row-Level Security:

This allows the organization to create policies which only return data rows appropriate for the user executing the query. For example, this allows a hospital to only return health information of patients directly related to a nurse, or a bank teller to only see rows returned which are relevant to their role. For more info, see https://msdn.microsoft.com/en-us/library/dn765131.aspx.

#3 Dynamic Data Masking:

This allows the organization to create policies to mask data in a particular field. For example, an agent at a call center may identify callers by the last few digits of their social security number or credit card number, but those pieces of information should not be fully exposed to the agent. Dynamic Data Masking can be configured on the SQL server to return the application query for the credit card numbers as XXXX-XXXX-XXXX-1234.

Dynamic_Data_Masking

These capabilities help prevent and mitigate accidental exposure of data while it is in-flight or in-use by a front-end application. For more info, see https://msdn.microsoft.com/en-us/library/mt130841.aspx.

Securing Structured Data At-Rest

Protection of SQL data at-rest is a feature that has been around for a long time now, which the SQL Server product team at Microsoft has enhanced in the 2016 release.

#4 SQL Transparent Data Encryption

In order to protect structured data at-rest, Microsoft first introduced SQL Transparent Data Encryption in SQL Server 2008. This technology protects data by performing I/O encryption for SQL database and log files. Traditionally a certificate that SQL Server manages (and is stored locally within the SQL master database) would protect this data encryption key (DEK). In June 2016, Microsoft made a significant enhancement to this capability by making generally available a SQL Server Connector for Azure Key Vault.

AKV

Image credit: Microsoft

This allows organizations to separate SQL and Security Administrator roles, enabling a SQL Administrator to leverage a key managed by the security operators in Azure Key Vault, with a full audit trail should the SQL administrator turn rogue. This connector can also be used for encrypting specific database columns and backups, and is backward compatible all the way back to SQL 2008.

More info at https://msdn.microsoft.com/en-us/library/dn198405.aspx

Detecting SQL Threats

In addition to securing SQL data, we also need to consider protecting data sources from the threats that would lead to breach.

#5 SQL Threat Detection

Running SQL in the cloud brings some additional benefits. For databases running on the Azure SQL service, the new SQL Threat Detection service monitors database activity and access, building profiles to identify anomalous behavior or access. If suspicious activity is detected, security personnel can get immediate notification about the activities as they occur. Each notification provides details of the suspicious activity and recommendations on remediating the threat.

SQL Threat Detection for Azure SQL Database can detect threats such as the following:

  • Potential Vulnerabilities: SQL Threat Detection will detect common misconfigurations in application connectivity to the SQL data, and provide recommendations to the administrators to harden the environment.
  • SQL Injection Attacks: One of the most common approaches to data extraction is to insert a SQL query into an unprotected web form, causing the form to return data that was unintended. SQL Threat Detection can identify if an attacker is attempting to leverage this mechanism to extract data.
  • Anomalous Database Access: If a compromised database administrator account starts to execute queries from an abnormal location, SQL Threat Detection can detect and alert on the potential insider threat or identity compromise, enabling the security personnel to update firewall rules or disable the account.

SQL Threat Detection for Azure SQL Database is a powerful new tool in detecting potential data leakage threats. For more info, see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-threat-detection.

I hope you’ve found this short read on some of Microsoft’s capabilities for protecting structured data valuable. Questions or comments? Feel free to leave your thoughts in the comments section at the end of this article.

Install SQL Server Mgmt Studio (SSMS) with PowerShell

Below for reference is a quick PowerShell sample for downloading and installing SQL Server 2016 Management Studio (SSMS). While this component used to be included in the SQL Server installer, it is now a separate download.

# Set file and folder path for SSMS installer .exe
$folderpath="c:\windows\temp"
$filepath="$folderpath\SSMS-Setup-ENU.exe"

#If SSMS not present, download
if (!(Test-Path $filepath)){
write-host "Downloading SQL Server 2016 SSMS..."
$URL = "https://download.microsoft.com/download/3/1/D/31D734E0-BFE8-4C33-A9DE-2392808ADEE6/SSMS-Setup-ENU.exe"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "SSMS installer download complete" -ForegroundColor Green

}
else {

write-host "Located the SQL SSMS Installer binaries, moving on to install..."
}

# start the SSMS installer
write-host "Beginning SSMS 2016 install..." -nonewline
$Parms = " /Install /Quiet /Norestart /Logs log.txt"
$Prms = $Parms.Split(" ")
& "$filepath" $Prms | Out-Null
Write-Host "SSMS installation complete" -ForegroundColor Green

Questions or comments? Use the comments section below.

How to Query Azure SQL Databases via PowerShell with SMO

In previous articles, I have demonstrated how to create Azure SQL Server and database instances via PowerShell in:

Certainly you could create both your Azure SQL Server instance and the database in the Azure Portal, so do as you prefer. In this post, I will ultimately give you the steps to connect to Azure SQL and work with a bit with the dataset returned.

Why SMO? We’re going to use Shared Management Objects (SMO) in this example, which is a convenient approach because really only requires that SMO (and its dependencies) are installed where you run the PowerShell script. You don’t need any specialized SQL Server PowerShell modules or management tools.

Prerequisites

In order to connect, you will need to ensure the following is true on the machine you are working from:

TIP: Make sure you load the CLR Types first, as SMO requires CLR in order to install successfully. Both of these components are on the same page on the MS site (http://www.microsoft.com/en-us/download/details.aspx?id=29065)

Firewall Rule

Another prerequisite is a firewall rule on your Azure SQL Server. Azure SQL blocks all remote connections by default, so you have to create a firewall rule. The Azure Portal will show you what it sees as your current IP, making it easy to add an exclusion for yourself.

To create an exclusion for all IPs, simply enter a range of 0.0.0.0 – 255.255.255.255. Obviously this is not the most secure option, so open up only to the source IPs that really require.

Azure SQL

How to create firewall rules for Azure SQL is also available at http://blogs.msdn.com/b/umits/archive/2012/11/20/windows-azure-sql-database-how-to-manage-your-firewall-rules-with-windows-azure-management-portal.aspx.

Sample Script

The sample code here will run whatever T-SQL SELECT, INSERT, UPDATE or DELETE query you assign to the $sql variable.

# Connect to SQL

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') ' tcp:mySQLServer.windows.net'

$s.ConnectionContext.LoginSecure = $false

$s.ConnectionContext.Login="myuser"

$s.ConnectionContext.Password="P@ssw0rd1!"

# Set DB

$db = $s.Databases["MySQLDB"]

# Build SQL Query (SELECT, INSERT, UPDATE or DELETE)

$sql = 'SELECT * FROM MySQLTable'

#Submit SQL Query (sample 1)

$ds = $db.ExecuteWithResults($sql).Tables[0]

At this point, your dataset is stored in the $ds variable.

If you wanted simply to dump your dataset to a spreadsheet, you could do change the line with $ds to the following:


#Submit SQL Query and write results to CSV (sample 2)
$OutputFile = 'c:\mydir\myfile.csv'
$ds = $db.ExecuteWithResults($sql).Tables[0]  | Export-Csv "$OutputFile" -NoTypeInformation

If you wanted to loop through a multi-row dataset and write to your console, flip the last section to this snippet:

</pre>
#Loop through results and write to console (sample 3)
Foreach ($t in $ds.Tables)
{
   Foreach ($r in $t.Rows)
   {
      Foreach ($c in $t.Columns)
      {
          Write-Host $c.ColumnName "=" $r.Item($c)
      }
   }
}

Hopefully this gives you a nice jump start. I did not mention stored procedures, which I’ll cover in a quick post in the near future.

How to Create an Azure SQL Database via PowerShell

As I mentioned yesterday in “How to Create an Azure SQL Instance via PowerShell“, Azure SQL provides some cost advantages over running SQL in Azure VMs for scenarios where all you need is a database. In yesterday’s article, we created an Azure SQL database instance, as well as a firewall rule on the instance to allow remote connections.

Now, we are ready to create a database in our new Azure SQL instance. Start by connecting to your Azure subscription in the standard manner (see resources in the article linked above if connecting to your Azure sub in PowerShell is not familiar).

Then, we create a credential to store the user and password we will use to authenticate to the Azure SQL instance.

$User = “pzerger”
$PWord = ConvertTo-SecureString –String ‘Pa$$w0rd!’ –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential `
–ArgumentList $User, $PWord
[/code]

Next, we will connect and authenticate to the instance.

$sqlContext = New-AzureSqlDatabaseServerContext `
-ServerName “xne7lwczta" -Credential $Credential

Then, we will select the level of service, which determines the max database size, connection and session count, and transaction rate. There are many levels of service across the Basic, Standard and Premium tiers. You can find a current list of available  https://msdn.microsoft.com/en-us/library/azure/dn741336.aspx.

In this example, I will specify S0, the lowest level of the Standard tier, and provide along with that the sqlContext object from the previous step, which stores the SQL instance name and credentials.

$S0 = Get-AzureSqlDatabaseServiceObjective `
-Context $sqlContext -ServiceObjectiveName S0

In the last step, we create the database. In this case, I am creating a database named “InsideTheMSCloud”.

New-AzureSqlDatabase -ConnectionContext $sqlContext `
-DatabaseName “InsideTheMSCloud" -Edition “Standard” -MaxSizeGB 1 `
-Collation “SQL_Latin1_General_CP1_CI_AS” -ServiceObjective $S0

Now we have a Azure (PaaS) SQL instance, a firewall rule to allow remote connections and a database. In the next post on this topic, we’ll do something with this database.

How to Create an Azure SQL Instance via PowerShell

I wanted to share a bit about Azure SQL (PaaS) in Azure, as I believe it will commonly used by IT Pros in the future, particularly as a logging and reference databasew when working with Azure Automation. One compelling reason to Azure SQL instance of SQL Server in Azure VMs is cost. If you just need a little database, you can have what you need in Azure SQL for as little as free or a few cents a month. SQL Server in Azure VMs is going to cost the price of the VM and the SQL license if you don’t bring your own. You can read about the differences between the two at http://azure.microsoft.com/en-us/documentation/articles/data-management-azure-sql-database-and-sql-server-iaas/.

To create an Azure SQL Server instance, start by connecting to your Azure subscription in PowerShell as you normally do. If you are unsure of the steps, see http://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/.

Next, we create an Azure SQL instance as follows. This creates a new Azure SQL Server instance, with a random name chosen by Azure.

#Create Azure SQL Server
$location = 'East US'
$login = 'pzerger'
$password = 'Pa$$w0rd!'
$newSqlServer = New-AzureSqlDatabaseServer -Location $location -AdministratorLogin $login `
-AdministratorLoginPassword $password -Version "2.0"

Get the name of the Azure SQL instance name like so:

$newSqlServer.ServerName

All connections to this SQL instance will be blocked by default, so you need to create a firewall rule to allow all connections. For this one, I just create a rule to allow connections from any IP address.

New-AzureSqlDatabaseServerFirewallRule -ServerName $newSQLServer.ServerName `
-RuleName "Allow all IP addresses to connect" `
-StartIpAddress 0.0.0.0 -EndIpAddress 255.255.255.255

Now we have an Azure SQL Server instance that allows connections from any IP Address. In a future post, we will connect and create a database and start making use of our resources.