So here we go, the first installment of my cloud blog series. From my experience this concern is a common one, especially when relating it to the database layer. Data “leaks” via security breaches have been getting some real negative press lately, what tools and techniques do you have to protect your Azure SQL Databases? The answer is – A LOT across different components and that is what I will cover in this blog post.

The Bigger Picture

It is probably best to understand the high level design of your SQL Database and its connectivity structure.

Source: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture

What Happens?

• Clients within Azure or outside of Azure connect to the SLB (Software Load Balancer), which has a public IP address and listens on port 1433.
• The SLB directs traffic to the Azure SQL Database gateway.
• The gateway redirects the traffic to the correct proxy middleware.
• The proxy middleware redirects the traffic to the appropriate Azure SQL database.

As you can see, it is not a case of a direct connection to the database, there are a few more layers to it.

Firewalls – You Shall NOT pass

Your connection to Azure SQL Database must pass through the firewall else it will fail. Try and connect to your SQL Database without correctly configuring it and you will get the below image (Not the Lord of Rings one).

There are two types in Azure, server level and database level. Microsoft recommends using database level firewall rules whenever possible to enhance security and to make your database more portable (more on this later). Use server-level firewall rules for administrators and when you have many databases that have the same access requirements.

As you can see, some serious design has gone into Azure SQL Database. You can have the option of enabling access to all other Azure services (below screen shot), but this is not recommended.

Personally I prefer using database level firewall rules. If you want to work with database level firewalls then you will need to use T-SQL, for example the below creates a firewall rule called Azure SAP Rule for a specific IP address 0.0.0.9

EXECUTE sp_set_database_firewall_rule N'Azure SAP Rule ','0.0.0.9','0.0.0.9'

Most likely you will be specifying an IP range.

Microsoft does recommend it. It definitely makes sense if you opt to use active geo-replication then you should use database firewalls because for geo-replicated databases you would want the rules to be replicated with the database to ensure all secondary databases have the same firewall rules as the primary.

If you do not use geo-replication and all databases on the server have the same access requirements, then I understand if you stick with server level firewall rules.

Your Connection String

There are two important parts to the connection string between your clients and SQL Database. First you should request an encrypted connection and second to not trust the server certificate (Both highlighted in bold). This establishes a connection using Transport Layer Security (TLS) and reduces the risk of man-in-the-middle attacks.

Server=tcp:Yourserver.database.windows.net,1433;Initial Catalog=SheepDB;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=false;Authentication=”Active Directory Password”;

A simple yet important concept.

Server Admin Account

Not many speak about this account. Technically there are two unrestricted accounts, one of them being the Server admin account and the other is an Active Directory Admin account which is not in use by default (Only if you setup AD authentication with your SQL database).

As stated by Microsoft “when you create an Azure SQL server, you must designate a Server admin login. SQL server creates that account as a login in the master database”.

While not sysadmin like, it is still a powerful account, it has the ability to create, alter, and drop databases, logins, users in master, and server-level firewall rules. Also it can add and remove members to the dbmanager and loginmanager roles (These are roles specific to Azure SQL Database). Why I have a section for this is because I really want people to make this password REALLY strong.

If you want to change it, then it’s easy via the main menu.

Also, as a tip do not select the “Remember Password” in SQL Server Management Studio, one day you might forget to lock your computer – I did this once, please learn from my error.

Keep the password long and strong and locked away and you may sleep a little easier.

Database Features

Now from a database perspective Microsoft have given you various technologies to help you ease your security concerns. These include:

• Auditing capability.
• Threat Detection.
• TDE – Transparent Data Encryption.
• Always Encrypted.
• Data masking.

I personally use TDE & Threat Detection

You do not really have to use all of them, this is down to you and your requirements but at least you have decisions to make. I will mention TDE (Transparent Data Encryption), I know a lot of people will opt for TDE in Azure. The big advantage of TDE in Azure over the earthed flavour is that Microsoft does a lot of the work for you, especially around the key management side of things. Also assuming your database is in a geo-replication partnership it will be protected by a different key on each of the servers. Microsoft will also rotate your certificate at least every 90 days, doing this with a local based SQL Server can be quite manual and fiddly (well I think it is).

Look how easy it is to enable TDE:

Threat detection too, which requires you enabling auditing to Blob storage. From an auditing perspective you can define a policy at the server level or database level. If you chose to go with the server level setting then the policy will be applied to existing and newly created SQL Databases.

Below shows detection types that you would like to capture for your threat detection policy.

Threat Detection It is NOT Free

“There’s no such thing as a free lunch”

Threat detection is not free, well I tell lie, it is free for the first 60 days then you will have to pay, so I guess you will be getting a free lunch for 60 days!

Getting alerted on potential SQL injection patterns is important especially if you think what financial implications a successful attack could have on your company.  You do not want to be part of an IT team like this company where in “2014  26,331 customer details could be accessed. The attacker used a common technique known as SQL injection to access the data”. http://www.businesscloud.co.uk/news/firm-hit-with-60k-fine-after-falling-victim-to-cyber-attack.

Obviously we hope for good coding standards and all that but if all else fails then you would want visibility of potential threats right?

Everything ties up into the Security Center within Azure, look at this as your Single view.

If you drill into Storage & Data, you will be able to view the status of your SQL Databases. Here you can see I need to address an issue.

It all ties in well.

However, I would like to see further improvements when analysing the audit logs. I find the filtering system and options could improve. I would like the ability to search for a specific event type. Auditing is very thorough, you may notice the logs starting to become over-whelmed with RPC completed events for a busy system hence why I would like to see improved filtering. Hopefully Microsoft will update this component.

AD Authentication – Finally!

You might be wondering if Azure SQL Database supports ONLY SQL logins? Not anymore, wherever possible I now try to use AD authentication over SQL authentication.

How you go about setting this up and creating logins is very different to the “on-prem” world, there is a slight learning curve but with great advantages. I have a guide showing you the step-by-step instructions where I follow the yellow path below.

https://blobeater.blog/2017/05/22/ad-authentication-and-azure-sql-database/

Compliance Offerings

Being specific to my needs I wanted to know what standards Azure meets. Everything I expected, making me even more comfortable. The big one being PCI DSS compliant.

My formula?

I know when I deploy an Azure SQL Database I do the following:

DB firewall rules + secure connection string + AD users + TDE + Threat Detection = <Image Below>

Are you more comfortable now? Hopefully after this high level overview on security you are. Next week we will move onto how my dodgy code crippled my Azure SQL Database and some other performance pitfalls.

Filed under: Azure, Cloud Blog Series Tagged: Azure, Cloud Busting, Security