4

I have to premit that i am a programmer, and i am trying to learn a bit about DBA even if i don't have the same background. I am trying to understand what security provide, and how to use, encryption of data at rest, on SQL Server.

My question is, how that encryption will protect data in case of unauthorized access of the machine that host the SQL Server instance? My guess is not at all.

I mean, assume SQL Server run on a virtual machine placed into a datacenter, and "connected" to internet. If an attacker will gain control of the virtual machine, then i think that data at rest protection can do very little.

In fact, the attacker can, gained admin privilege (as OS user), reset SQL Server credential and log in as 'sa', then even if the SQL Servr file are normally encrypted he can export them in plain text.

In such scenario is there any way to secure SQL Server or in case of breach at OS level the SQL Server is to be considered "lost"?

Improve this question

1 Answer 1

Reset to default
6

My question is, how that encryption will protect data in case of unauthorized access of the machine that host the SQL Server instance? My guess is not at all.

You are correct. Attack vectors which the bad actor can achieve control (varying amounts) of the system will render these technologies useless. For example, they don't even need to get to the VM in your hypothetical case, they just need to get access to SQL Server, so even less privileges required.

In such scenario is there any way to secure SQL Server or in case of breach at OS level the SQL Server is to be considered "lost"?

There are a few ways to accomplish this, and SQL Server does include one of these technologies "out of the box" which is called Always Encrypted (AE).

This, however, assumes a proper setup of AE and pushes the attack vector to endpoints rather than the central server itself. This means if someone would steal or gain access to your VM (same hypothetical situation you described) then they would not be able to get at the data, even as SA. Since the data is encrypted, it'll also be encrypted on disk and in memory (such as someone attaching a debugger, or doing remote process memory scanning, kernel memory scanning, etc.), backups, and in transit on the wire.

Always Encrypted was made for that very scenario, however it falls short on other scenarios. If you're worried about someone gaining access to the VM (I mean, aren't we all?) then it's a layered approach which I'm sure you know, but there are some technologies such as AE which can help stop certain attack vectors.

Improve this answer
6
  • mmm in the scenario i have pictured in my mind AE seems not suitable (i mean i discorvered it only now, so my understanding can be wrong). I mean almost every project i working on involve an application server (tipically IIS and a ASP.NET/MVC web application) and a SQL Server, Both tipically installed over the same VM. In that case AE will fail short, the certificate of the client (that teorethically protect me end-to-end) will be installed just near the SQL Server on the same VM.
    – Skary
    Commented Jan 5, 2023 at 15:13
  • 4
    @Skary, in my experience, SQL Server and applications are not usually installed on the same machine, at least in in production, where performance, scalability, and security is a concern.
    – Dan Guzman
    Commented Jan 5, 2023 at 16:18
  • In any environment, if it's not setup in a secure way, it won't be secure. No technology is going to change that. Putting the keys next to the lock, regardless of how that lock is implemented, it's going to be secure.
    – Sean Gallardy
    Commented Jan 5, 2023 at 17:07
  • @SeanGallardy yes you are right, i was not complaining about the tool but the constraints of my usual case of use, and pondering how beneficial the tool may be in such scenarioes
    – Skary
    Commented Jan 5, 2023 at 17:17
  • 1
    Quick comment regarding having the app and SQL on the same server... Unless you are using Express, you are paying big money for SQL licencing, per core probably. Using those highly costy cpu for running IIS when it could run on virtually free cpu is the reason why usually, the app is not on the same server as SQL. It is also a lot simplier to implement firewall rules to limit access to the DB
    – Dominique Boucher
    Commented Jan 6, 2023 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.