I have been working with Azure for a good number of years and felt that I wanted to share more of my experiences, since the migration to Azure (or to any cloud whatsoever) in the times of pandemic is growing very strongly and partially as a consequence and in part as a sign of the times, in the past 1-2 months, the number of attacks on the cloud infrastructure has been constantly on the rise, and a number of organizations are discovering that their cloud infrastructure have been some kind of exposed to the outside world.
Since forever there have been conversations on the importance of the security, but with the time one can not stop wondering about the real care of the customers. The only type of customers who would care is those who got their data stolen/corrupted and who found out in the worst possible moments.
With the appearance of GDPR, companies in Europe are now OBLIGED to report any personal data theft within a very short window of time, which definitely helped to improve focus in this area, but that would touch in practice only major companies.
I believe that together with GDPR, Cloud will change a lot of those perceptions, especially when the breaking/hacking tools will become more generalized on the black market and any bad-wishing individual/organization will be able to impact/extract other organization business data.
For me, it is not a question of whether IF but WHEN.
The things I won’t be focusing on in this blog post, but nevertheless ARE extremely important and there is so much good information on how to deal with those situations:
– SQL Server Permissions (Yeah, if somebody wishing you harm has got a SYSADMIN permission on your server – it is a “game over” anyway…)
– SQL Server Audit (Out of Scope, but a lot of Vulnerability Assessment and privileges escalation can be caught with it)
– Virtual Machine Permissions (An Admin can simply restart SQL Server in the minimal mode and take control of the instance as a SYSADMIN, can’t help you)
– Data Masking (That’s an application …security… feature)
– Always Encrypted (Wonderful feature, I just dread the requirements & setup for the Secure Enclaves and without them some sever limitation might apply for the indexes, which does not sound as a fun adventure – you do not want to scan large TB tables every time you look for a full name of a person. Out of scope of this blog post, but incredibly useful and a great security enabler)
– Cell-Level Encryption (love this feautre, but use it with care, especially in the big tables environment. Out of scope for this blog post, but should be applied whenever it makes sense to protect sensitive or personal data)
– and probably more that are simply not on my mind right now
I am not pretending on the completeness of my check-list, but I would be happy to hear on the improvement that should be made to it and I would definitely come back to this post, and so here we go:
- TLS – Secure Communcation
- Azure Disk Encryption
- Encryption at Rest (TDE)
- Backup Encryption
- Protect Your Backup Storage
- Protect Your Server with a Firewall
- SQL Vulnerability Assessment
TLS – Secure Communication
There is not much to say about the TLS, besides that the production workloads should be using (1.2 is the latest supported version at the moment of this blog post writing) and with the hopes of 1.3 implementation and posterior support in SQL Server 2012+ would expect to see in the SQL Server vNext (post SQL Server 2019).
I believe that the security should be imposed by design and as much as possible (yeah, there are always those old vendor-specific applications that won’t support TLS),
Those applications that do not support TLS, should be considered to be obsolete as soon as possible in the corporate environments – as they are some of the most popular entry points, especially since they (sometimes unpatched) security flaws are long known and publicly disclosed on the internet.
Minus (as in opposite of the plus), the functionality knowledge is probably limited or almost non-existent.
The SQL Server 2019 has made it a little bit easier the Certificate Management, as described in SQL Server 2019 – Improved Certificate Management.
Azure Disk Encryption
A lot should be written about the direct disk & data file protection, but in any case (after moving all system databases & logs away from the C drive), there is no doubt that any VM drive should by encrypted and Azure does it by default and if you are using older VMs & disks.
You can export the disk, you can make snapshots from it – so many operations that are not desired and introducing the encryption on your VM disks is a good option to prevent some of the attack vectors.
Right now I do not see any reasons for not encrypting them and so far I have not heard or read anyone loosing any performance because of the encryption.
Encryption at Rest (TDE)
I like the idea of the TDE and besides known crticism of the implementation (VM Administrator can do a lot of things) and especially the current possibility of the EKM with the help of Azure Key Vault (will go into details in one of the upcoming blog posts) or your own custom provider (there are a couple of them whitelisted). If you are not trusting of having a key within Azure Key Valut that you can not export and you expect/need to be able to migrate to a different Cloud Platform – you can use your own key. Also do not forget that you can eventually decrypt a database before migration if needed and with Resumable Transparent Data Encryption (TDE) Scan in SQL Server 2019, you can exercise precise control on the database level instead of using those old Trace Flags.
I have spoken around with a number of people and asked at the social networks and nobody admitted of seeing impacts beyond 5% of the CPU, while the most are seeing significantly less. In practice a lot of people won’t be able to measure the impact, but hey – I am not guaranteeing you here anything.
In the security field I guess the most exciting thing for me in SQL Server 2019 was the fact that Standard Edition now finally supports TDE.
Besides protecting from the data file copying and attachment, TDE will do a wonderful job of protecting your backups – since without the SMK you won’t be able to restore & decrypt your backup, and that’s a great bonus point in the terms of security.
Backup Encryption
You should keep your backups safe and protected from any unprivileged access and protecting your SQL Server Backup, because they represent a safe copy of your actual and historical data.
Stealing your backup of the day is the same as stealing your data from the server, but it can be more efficient and convenient, since they are most probably already compressed and striped – making the task of copying/restoring them
To protect your backups, you can use the Backup Encryption which is available since SQL Server 2014 in Enterprise, Standard & BI (does not exist anymore, yay!) Editions, where you can restore them to any other edition, including Express Edition – given that you are not using any non-Express Edition features and that means that compression, partitioning, etc are still off the chart, because they were democratized just in SQL Server 2016 Service Pack 1.
You have 2 options for Encrypting Backups –
- do the backups with certificate (SQL 2014+ Standard or better Edition is required as described in the previous paragraph)
- Use TDE that will give you backup encryption (any Enterprise Edition or 2019+ Standard Edition or better are required)
I would say that if you can do TDE, then it is better then Backup Encryption since it solves 2 problems with just 1 technology. :)
Protect Your Backup Storage
Maybe my Google-Fu is failing me, but in my opinion, not enough has been written about Backup protection.
In the case you are using Backup to URL, making sure that the storage accounts where you place your backups are inaccessible for anyone else besides the respective configured services. For on-premises work years ago, I have seen the inflexibility of the network engineers with a good degree of admiration – they made solutions & infrastructure significantly more secure.
First of all, make sure secure transfer is required, this is really a basic option to activate.
Setting up a Firewall and Virtual Networks option is essential in the attempt to limit – especially if you are (OUCH!) running DEV & PROD environments in the same network.
With the help of NSG (Network Security Groups), you can define more granular rules, allowing production VMs with SQL Server to access the respective account and forbidding everyone else (like some Jumper server) to do the same. Do not forget the archival service, that will do archiving of the old backups – just saying. :)
I love NSGs (Network Security Groups) and have them already used extensively, I prefer to put them to work and recommend to everyone else. The Private Endpoints have their advantages, since their configuration for the services and respective accesses can be wondrously easy and pleasant, especially if you are doing a small and easy architecture. Do not forget to protect the endpoints – just saying :)
I am intentionally skipping Logging & Metrics part, as they are out of scope of my blog post. Regarding the access from the internet or your on-premises networks to storage account with the backups is answer is straight and simple – NO.
Just a soft reminder that Network Security Group (NSG) rules and User Defined Routes do not apply to Private Endpoint, besides the little details that if you are creating Private Endpoint later, it might simply disable your NSG for the whole subnetwork – OUCH!
Also, if you are able – consider using Advanced Threat Protection for Azure Storage
Protect Your Server with a Firewall
This one must be really on everyone’s mind by default:
Use Windows Firewall or Azure NSG (Network Security Groups), or some other Firewall software (free/commercial), but please, use a good firewall to prevent unauthorized access, and if you log it (and you should), you might discover some interesting and nasty details that will serve as a prevention
I will recommend here John Martin’s multi-part series on Protecting your SQL Server with Windows Firewall (3 so far) series with part 1, part 2 & part 3.
Azure Security Center
If you can, use Azure Security Center, which even in it’s free incarnation will provide with some good value. If you are a big organization, running in the cloud, you must be using some kind of SOC – and I guess that Azure Security Center must be just one of your tools, but for the rest of us, mortals – it gives a good job.
Virtual Machine Vulnerability Assessment is a nice piece of software and I truly like where it is going. If you are not a top security expert, it will bring you some easy (or maybe not) tidbits to correct in your VMs.
Also consider using SQL Vulnerability Assessment if you are able to – I have already seen some very encouraging results that definitely made the developers think about the bottom line of the code they were developing.
Final Thoughts
This must be by far not the most exhaustive list, but it should give you a start in helping to get your SQL Server VMs on Azure a little bit safer.