InfoSec with SQL Azure
I've been using SQL Azure since 2011 and it's been a journey. One of the big problems I used to have was passing security audits from some of our clients; We deal with a lot of data that is highly sensitive so are under a lot of scrutiny to make sure it is protected.
A lot of what you have to comply with when you start going into the areas of ISO27001 and PCI compliance is not just about technical security in the way we normally think about it, it’s just as much about people and processes. In my experience from filling in dozens of security questionnaires, I believe that SQL Azure can tick all the boxes, as long as you switch on the right features. Just to be clear, I'm not required to comply with PCI so can't vouch for that, but I do have to comply with a number of ISO27001 and Data Protection requirements.
Incidentally, Azure also recently enabled you to do something similar for Blob storage - again mainly so you can tick the box.
This is a useful feature to potentially detect both external and internal attacks (see below).
You have nothing to lose by switching it on.
You may well trust everyone in your organisation to not want to deliberately betray you and to be sensible about not having their laptops hacked - but an auditor won't, so you may as well buckle up and do the right thing. SQL Azure makes it surprisingly easy, though there are number of manual steps that could be made a lot easier with some better tooling, especially if you have many databases.
You can create individual users in SQL Azure to give to the people who need access and I urge you to do so. You may want to give users read-only access while you are it. The tooling is lacking, in that you have to create the users using T-SQL from something like SQL Server Management Studio or similar; you can’t manage the users in the Azure Portal, which is quite an oversight IMO.
If you use Azure AD for authentication you can add Azure AD users to the SQL Databases. This has the benefit that user access will be automatically revoked from all databases when the user's AD Account is disabled - probably when they leave the organisation. It also means that, as of August 2016, you can use 2 Factor Authentication with the SQL logins - just download the latest version of SQL Server Management Studio 2016 and choose the Universal authentication method.
At NewOrbit, all user access to Azure and SQL Azure is controlled through Azure AD, through our Office 365 subscription and all users are required to have 2FA enabled.
Do understand that this feature is not completely tamper proof; you can still search on the underlying data, so with patience you can triangulate your way to the real data - but then that will show up in the audit logs.
A lot of what you have to comply with when you start going into the areas of ISO27001 and PCI compliance is not just about technical security in the way we normally think about it, it’s just as much about people and processes. In my experience from filling in dozens of security questionnaires, I believe that SQL Azure can tick all the boxes, as long as you switch on the right features. Just to be clear, I'm not required to comply with PCI so can't vouch for that, but I do have to comply with a number of ISO27001 and Data Protection requirements.
Box ticking
Most security audits will ask you if your data is "encrypted at rest". If you understand how Azure really works and where that requirement originally comes from, you'll know that this is a meaningless requirement when you're in Azure. But, just try to convince a security auditor of that. So go ahead and switch on Transparent Data Encryption and you can tick that box on the questionnaire.Incidentally, Azure also recently enabled you to do something similar for Blob storage - again mainly so you can tick the box.
Intrusion detection
SQL Azure has a neat Threat Detection facility to monitor the use of your database and alert you to anomalous behaviour. I've seen it detect potential SQL injection attacks and allegedly it will detect "unusual" behaviour, though I have yet to actually see that (thankfully :)).This is a useful feature to potentially detect both external and internal attacks (see below).
You have nothing to lose by switching it on.
Internal attacks
As a developer it's easy to think that once you pass penetration testing, your job is done. But, for the organisation, that's only half the battle. A proper security audit will look just as much at how you are preventing and detecting what they sometimes call "Internal Data Leakage". Basically, they are worried that people inside your organisation may access the data and leak it. This may be deliberately, it may be a result of social engineering or it may be that an external agency decided to hack a person who works for you. It's the modern equivalent of sneaking in through the kitchen entrance instead of taking a battering ram to the front gates. It's hardly surprising there is a lot of focus on this, given that most of the big attacks recently seems to have come from some kind of inside job.You may well trust everyone in your organisation to not want to deliberately betray you and to be sensible about not having their laptops hacked - but an auditor won't, so you may as well buckle up and do the right thing. SQL Azure makes it surprisingly easy, though there are number of manual steps that could be made a lot easier with some better tooling, especially if you have many databases.
Step 1 - Give users individual logins
Your application probably uses a master username and password to log in to SQL. It's tempting to just give that to the people who need to go and look stuff up in the database, including developers, devops, support etc. But this means you have no way of knowing which actual person did what. Worse, when someone leaves the organisation you may not find it that easy to change the application's SQL Password.You can create individual users in SQL Azure to give to the people who need access and I urge you to do so. You may want to give users read-only access while you are it. The tooling is lacking, in that you have to create the users using T-SQL from something like SQL Server Management Studio or similar; you can’t manage the users in the Azure Portal, which is quite an oversight IMO.
If you use Azure AD for authentication you can add Azure AD users to the SQL Databases. This has the benefit that user access will be automatically revoked from all databases when the user's AD Account is disabled - probably when they leave the organisation. It also means that, as of August 2016, you can use 2 Factor Authentication with the SQL logins - just download the latest version of SQL Server Management Studio 2016 and choose the Universal authentication method.
At NewOrbit, all user access to Azure and SQL Azure is controlled through Azure AD, through our Office 365 subscription and all users are required to have 2FA enabled.
Step 2 - hide the sign-in details for the application
Once you have given users individual logins, you'll need to change the password the application uses to login to SQL. And you need to ensure it's not available to developers or others. You can do this by specifying things in the portal, if you use web apps. However, there are two other methods you may want to consider;- You can let your Application itself authenticate to SQL with Azure AD using a certificate - see the same link as above about using Azure AD with SQL Azure. This way there literally is no password and as long as you delete the certificate after uploading it, there is no way for anyone to log in to SQL as the application.
- You could store the SQL password in Azure Key Vault.
Step 3 - audit all the things
SQL Azure has an Auditing facility whereby you can audit all queries made against the database. Given that you have assigned an individual user name to each of your people, you can (and should) audit exactly which queries each of your people have run - and Azure will even flag up queries that return an unusually large number of records. This is gold dust for complying with InfoSec requirements about preventing internal data leakage.Step 4 - use data masking
SQL Azure has a facility to "mask" certain columns, such as email addresses, phone numbers and so on. I think the original design of this feature was as an aide to applications. But actually it's really useful in complying with InfoSec requirements about limiting access to sensitive data; You can set it up so your Application has normal access to all the data, but the logins your users have can automatically mask sensitive data. This means your users can write queries as normal and even see if data exists, but sensitive details that they don't need for troubleshooting will be masked in the output.Do understand that this feature is not completely tamper proof; you can still search on the underlying data, so with patience you can triangulate your way to the real data - but then that will show up in the audit logs.