SQL Azure

Marko Zivkovic

What is SQL Data Sync

June 1, 2018 by

SQL Data Sync is a service that allows synchronizing data across multiple Azure SQL databases and on-premises SQL Server databases.

In this article, a base concept of how the SQL Data Sync service works will be explained as well as what the requirements and limitations are when want to create data synchronization by using SQL Data Sync

Read more »
Esat Erkec

Integrating Azure SQL database alerts into Slack

April 27, 2018 by

Database administrators have a lot of tasks and responsibilities but perhaps the most challenging task is performance tuning. Because performance tuning issues can cause huge problems in the business area, database administrators have to monitor performance metrics and have to get immediate, if not sooner, notifications when the performance values cross an optimal state. Generally, this notification comes by e-mail.

Read more »
Istvan Martinka

How to connect and perform a SQL Server database restore from Azure BLOB storage

April 18, 2018 by

Having things in the cloud should make life simpler but I have experienced it’s not that straightforward. Once all access / configuration is sorted out then yes, of course! But in the meantime it can be tedious (even frustrating) and the end result is something that could have been achieved with a different method.

I think a significant chunk of what Azure offers is easier to do with the more conventional methods but that separates us from the advanced Azure features that a company would like to utilize in the shorter / longer term future. So there are reasons to spend some time / effort in getting things right up there.

In my case the task I needed to accomplish sounded simple enough:

  • get a SQL Server database backup (.bak file) from Azure blob storage
  • copy it to our own environment and restore it to a SQL Server instance on an Azure Windows VM
  • or skip the copy step and restore straight from blob storage
Read more »
Esat Erkec

How to develop a K-Means model on Azure Machine Learning Studio

January 30, 2018 by

In this article, we will discuss the k-means algorithm and how can we develop a k-means model on Azure Machine Learning Studio.

Machine learning is an area of ​​artificial intelligence that helps us develop relationships between data and predict the future. There are many secrets that are hidden in the data. To discover these secrets, we need Machine Learning algorithms. Machine learning plays the key role in leveraging existing data to exploit business opportunities.

Read more »
Esat Erkec

How to integrate SQL Server and Azure Machine Learning

January 11, 2018 by

Good, clean and reliable data is important for every company, because well-analyzed data will open new possibilities to success. When we look at successful companies, we see that they’ve analyzed customers, sales, and finance data very well. At this point, a game changer enters: machine learning.

Machine learning

Machine learning is a type of artificial intelligence which analyzes data in order to predict future and discover hidden patterns. The output helps us make decisions. Machine learning is widely used in finance, healthcare and marketing.

Read more »
Marko Zivkovic

How to create a linked server to an Azure SQL database

September 27, 2017 by

Linked servers allow to access data from another SQL Server or another data source (e.g. Excel) by using SQL Server Management Studio (SSMS) or Transact-SQL.

This article will explain how to create and configure a linked server to retrieve data from an Azure SQL database. Also, we will explain how to solve some common problems/issues during the process of creating a linked server to an Azure SQL database.

Read more »
Daniel Calbimonte

How to connect to the Azure Storage Account with SQL Server Management Studio (SSMS)

September 25, 2017 by

Introduction

In SQL Server Management Studio (SSMS), it is possible to connect to the Azure Storage. The Azure Storage Account is useful because it creates replicas automatically in the cloud. You only need to upload your file to the Azure Storage Account and the replication is automatic. You can have multiple replications in different regions and Datacenters with few clicks.

Read more »
Craig Porteous

How to connect to (and query) Power BI and Azure using PowerShell

September 13, 2017 by
Power BI “as a whole” is a bit of a black box. If you’re like me and used to using SQL Server & its components; SSRS, SSAS etc. you have access to installation directories, Event logs, trace logs, error logs, chocolate logs? You can see full instances & their contents in one go, whether that be databases, reports or cubes. It gives you the control over & responsibility for performance & maintenance. Read more »
Minette Steynberg

8 things to know about Azure Cosmos DB (formerly DocumentDB)

September 4, 2017 by

Introduction

Azure Cosmos DB is a low-latency, high throughput, globally distributed, a multi-model database which can scale within minutes and offers 5 consistency options to let you decide how to deal with the CAP theorem.

Azure Cosmos DB used to be known as Document DB, but since additional features were added it has now morphed into Azure Cosmos DB. The name was chosen to spark the innovation and imagination of developers around the globe.

Read more »
Daniel Calbimonte

Configure Multi-Factor Authentication in Azure SQL Database

August 30, 2017 by

Introduction

The new SSMS 17.2 allows users to authenticate using Active Directory with Multi-Factor Authentication (MFA). This is a secure method for authentication where you have more than one method to validate your authentication. That way, you have a more secure authentication and prevent your Azure Portal or applications to be easily attacked by hackers or other malicious users.

Read more »
Minette Steynberg

Introduction to Azure SQL Data Warehouse

August 29, 2017 by

Introduction

Azure SQL Data Warehouse is a new addition to the Azure Data Platform. When I first heard about it I wasn’t quite sure about what exactly it would be. As it turns out it is relational database for large amounts of database and really big queries as a service. This is essentially the equivalent of the APS (Analytics Platform System) in the cloud.

Read more »
Prashanth Jayaram

How to Configure TDE database with AlwaysOn using the Azure Key Vault in SQL 2016

July 28, 2017 by

One of the recent tasks I undertook on configuring Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with Always On opened a different dimension on securing data for me. Even though it seems slightly complex, if you have the key details, the steps are in fact, really straight forward.

I strongly recommend going through this MSDN article on SQL Server Transparent Data Encryption TDE using Azure Key Vault in order to understand Azure Key Vault configuration and Integration

Background

In order to encrypt the database encryption key with an asymmetric key, use an asymmetric key that resides on an Extensible Key Management Provider. Extensible Key Management (EKM) is another new feature in SQL Server that adds the ability to store the encryption keys used for TDE on hardware, specifically designed for key security and management. Such devices are called High-Security Modules (HSM), whose vendors are referred to as EKM providers. A good Key Management Vendor should supply you with software libraries that easily add and implement Key Management in SQL Server encryption.

In asymmetric encryption, two different keys are used: A public key for encrypting, and a private key for decrypting the data. This type of asymmetric encryption is referred to as Public Key Infrastructure (PKI)- or Public Key Cryptography-based encryption.

Azure Key Vault

Azure Key Vault is a secure key management feature that is essential to secure and protect data in the Azure cloud. We use Azure Key Vault to encrypt keys and small secrets like passwords that use keys stored in hardware high-security modules (HSMs). For added assurance, import or generate keys in HSMs, and let Microsoft process your keys in FIPS 140-2 Level 2 validated HSMs (hardware and firmware).

Introduction

SQL Server running on an Azure Virtual Machine can use an asymmetric key from the Key Vault. When running SQL Server in an Azure VM, SQL Server can use keys stored in the Azure Key Vault using EKM. This article covers the role of a DBA in setting up SQL 2016 TDE database on Azure VM with Always On, using the Azure Key Vault. It also outlines the prerequisites and required details for seamless implementation of protecting the data using an asymmetric key.

Let’s proceed, with the assumption that we have the necessary details from the Azure Key Manager such as:

  • KeyValutName
  • Active Directory Application Client ID
  • Active Directory Client Secret
  • Key Encryption Key Name (KEK)

Let’s also add another set of assumptions from the DBA side:

  • Always-On Availability Group site that is already configured

For example,

Name Description Value
ResourceGroupName Key Vault Resource Group Name az-prod-sql-001
VaultName Key Vault Name az-kv-sql-001
AADApp AAD Application Name az-aadapp-kv-001
AADObjectID Azure Active Directory Application Client ID 2db602bd-4x4x-4322-8xxf-d128c143c8a9
AADClientSecret Active Directory Client Secret FZCzXY3K8RpZoK12MxF/WFxxAw6aOxxPU2ix
xEkQBbc=
Secret The SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together 2db602bd4x4x43228xxfd128c143c8a9FZCz
XY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc=
Key Encryption
Key Name
Key Encryption Key Name (KEK) az-kek-sql-001

SQL Server Connector

The SQL Server Connector for Microsoft Azure Key Vault enables SQL Server encryption to use the Azure Key Vault service as an Extensible Key Management (EKM) provider to protect SQL Server encryption keys.

Download the SQL Server Connector from the Microsoft Download Center, and follow the steps below to complete the installation.

  • Browse the installation folder
  • Click Next
  • Click the agree terms and license agreement radio button
  • Select the location for libraries to install
  • Click Next and then, Finish

By default, the connector installs at C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault. This location can be changed during setup. (If changed, adjust the scripts below.) The Connector is the cryptographic EKM provider DLL that needs to be registered with SQL Server by using the CREATE CRYPTOGRAPHIC PROVIDER statement.

The SQL Server Connector installation also allows you to optionally download sample scripts to help with SQL Server encryption.

Configure SQL Server

In this section, we shall see how we could add a TDE-encrypted database to Always On Availability Group site that is already configured. Assume that we have configured the Always On Availability Group SQLAG2 that contains three replicas.

In the following screenshot, iServerReportingDB is going to be configure for TDE. Let’s see the steps to configure TDE and use of EKM on Always On setup.

On the Principal,

  • Setup EKM
  • Setup Credentials
  • Configure TDE
  • Add database to Always On availability group
  • Perform full database backup
  • Perform log backup

On the Secondary, the steps are a little different from the procedure above

  • Create the database
  • Setup EKM
  • Setup credentials
  • Configure TDE
  • Restore the database with replace option
  • Restore the log
  • Join the database to Always On availability group

Let’s look at all of the above steps in detail and configure TDE with asymmetric key on the Availability Group using Azure Vault

Check the database’s encryption configuration

This indicates that no databases are configured for TDE.

Step 1: EKM Setup

The below SQL query is used to create a cryptographic provider, using the SQL Server Connector which is an EKM provider for the Azure Key Vault. This example uses the name AzureKeyVault_EKM_Prov. Notice that the DLL location should be the taken from where you installed—this is the Connector path.


STEP 2: Setup Credentials

Create a credential from your Azure Active Directory Client ID and Secret that you can use to grant an SQL Server account access to your Azure key vault

  1. IDENTITY here is the name of your Azure key vault.

  2. SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together


Add the credentials to the SQL Server administrator’s domain login.


STEP 3: Create asymmetric Key and SQL Login

  • Use the EKM to open the asymmetric KEK
  • Key Encryption Key name

Step 4: Create SQL Server Login





Enable TDE on SQL Database by Using Transact-SQL

  • Connect to the master database

  • Execute the following SQL statement to encrypt the database


  • Monitor the progress of encryption,


STEP 5: Add Database to Always On Group

On the primary, add the database to the Availability Group using ALTER AVAILABILITY GROUP … ADD DATABASE


Backup the database (Full and Log backup)

  • Initiate full and log backup on the primary replica using the following SQL statements:



On the secondary,

  • Create the database iServerReportingDB
  • Move the database backup file to a location where Always On Replica #1 can restore the file
  • Run steps 1 through 4 from the last section
  • Repeat the process for each Always On Replica node
  • Restore the full database backup

  • Restore the log database backup

  • Join the database to the availability group using the ALTER AVAILABILITY GROUP T-SQL


The AlwaysOn Group status

The below screenshot shows that iServerReportingDB is configured successfully with EKM provider, Azure Key vault.

Conclusion

In an Always-On-with-Azure-Key-Vault scenario, enabling TDE on one or more secondary replicas is indeed a tedious task. One has to get the help of Azure Key Manager to get all the required key information.

This article articulates every step required to setup and configure asymmetric TDE with Always On using Azure Key Vault. If you are able to configure the setup on Primary then similar steps should be applied to Secondary, with a few steps being different (which are also covered in this article). You will be performing a backup and adding the database to Availability Group in case of the former, whereas you would restore the database and join the database to the Availability Group in the latter: the secondary nodes.

See more

Consider these Free tools and community editions for Azure SQL Database

References



Kaloyan Kosev

Performance tuning for Azure SQL Databases

July 21, 2017 by

With the latest versions of Azure SQL database, Microsoft has introduced a number of new mechanisms to help users and administrators better optimize their workload.

Automatic index management and Adaptive query processing provide us with the possibility to rely on the built-in intelligence mechanism that can automatically tune and improve the performance of our workload.

Read more »
Page 1 of 41234