May 13, 2021 at 4:47 pm
Hello,
I have 7 different SQL Managed Instances which were newly migrated over from SQL VM.
I also have 7 different Azure SQL Databases which were also newly created.
I want to make sure that I completely secure all my SQL MI and Azure SQL to eliminate any security vulnerabilities and also avoid any SQL Injection attacks.
I have already implemented the following -:
I was also doing some reading into some other best practices to secure the SQL Managed Instance and Azure SQL Databases and below are some of the things that I found -:
I would appreciate if you guys can advise if these are worth looking into and should they be implemented in the environments based on your experience.
Also if implementing any of these best practices degrades the performance.
Thank you
May 14, 2021 at 3:14 pm
My opinion - you should implement any security based things that you require. Sounds like a dumb response, but think about it from a hacker perspective. If a hacker gets into your system, what would they have access to and how would that hurt you as a data professional and your company?
If they get in as a sysadmin, they have access to everything, so any protection you have in place is lost BUT the auditing MAY still be helpful. Might not be helpful if the hacker dropped the audit tables, but if they were unaware of the auditing, or didn't think to check for auditing, you may get useful information from the audits.
Now, hopefully your sysadmin accounts are secure. What I mean is they are using secure passwords that are difficult to guess and impossible in a reasonable timeframe to brute force. But what about the average end user? Lets say the hacker gets in as a limited access user. If you don't have row level security, that hacker now has all the data in the table.
It is entirely based on how much risk you are willing to accept. Like if you are storing public information, then row level security may not be needed. BUT if you are storing HR data (for example), having a breach there MAY result in massive lawsuits and someone may be losing their job for setting up the security poorly (it may be you).
BUT, almost any restrictions you put in place will result in some performance hits. The ones you listed should be minor performance hits IF implemented properly.
Always Encrypted is something I would turn on as this protects your data at rest and the performance impact should be very minor. Dynamic Data Masking for example is great for masking data in a SELECT statement while still keeping the data accurate in the database. SSN/SIN numbers are good candidates for data masking.
Implementing security features like row level security or data masking are applied at the table level, so you don't need to apply these across the entire system; just on the data that needs to be protected.
Plus, your list of things you found (the 5 numbered items) I would also recommend for on-prem SQL Instances. They don't ALL need to be implemented, and some databases it may not make sense to implement them. Data Discovery and Classification I would do before doing data masking or row level security as it helps determine which stuff is PII or which tables contain confidential information. Now, on the other hand, if the information wouldn't be useful to hackers (like if you had a database that contained information on plants including name, species, flower colour, etc), then you likely don't need to worry about protecting the data from reads, BUT you would probably still want to restrict who can write to the database and will probably still want auditing in place.
On top of my above advice, if nobody is using the system yet, then nobody will notice the performance impact of turning any of those on!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 18, 2021 at 1:16 pm
Thank you very much for your reply Brian.
you made the case perfectly with an apt explanation for every scenario. Appreciate your reply.
Thank you again
May 18, 2021 at 4:17 pm
Happy to help.
I didn't cover EVERY scenario, I just covered some. For example, if you have an IP allowed list and ONLY a small set of IP addresses are allowed to connect to your Azure infrastructure, that reduces the attack landscape a LOT. Then, only people inside your company can access the SQL instances, and therefore if an external hacker wanted to get in, they either need to spoof the IP (which is tricky as they don't know what IP's you have allowed) OR get into your corporate network.
BUT, I still like to keep my data protected when it is in the cloud. On prem, I still like it protected where possible, but the cloud is a much more public facing target. I KNOW there are SQL instances hosted in Azure.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 18, 2021 at 8:07 pm
Thanks Brian.
Both Azure SQL and SQL MI are restricted by IP addresses in our Network Security Group but like you said it will still be a good practice to implement all the 5 points.
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply