Jovan Popovic shows how you can audit who is taking backups on an Azure SQL Managed Instance:
One mechanism to ensure that nobody can take the COPY_ONLY backup of your database is to use Transparent Data Encryption that automatically encrypts all backups. In that case you would need to use Customer-managed (BYOK) TDE where you will keep your encryption key in Azure Key-Vault. User-initiated COPY_ONLY backups are (currently) not allowed if you are using Service-managed TDE.
If you don’t use TDE on the database or there is a risk that someone can remove TDE from database and then take a backup, Managed Instance provides auditing mechanism that enables you to track who performed a backup and when. This way you can always track if some unauthorized action is happening.
Read on for the solution. Looking through it, it seems like conceptually it’d work equally well with on-prem/IaaS SQL Server as with Managed Instances.
Gianluca Sartori helps Future Gianluca (and present us in the meantime) troubleshoot issues with xp_logininfo:
The user does not exist
This is very easy to check: does the user exist in Windows? Did you misspell the name?You can check this from a cmd window, issuing this command:
net user SomeUser /domainIf you spelled the user correctly, the command will return information about it, like description, password settings, group membership and so on.
There are a few other potential causes, so click through for those.
Claudio Silva explains how you can provide secure access to manage SQL Agent jobs:
It is common having services accounts that are job owners so they can run within the proper context.
In my humble opinion, this starts to be a little strange when it comes to modifying the agent job. It means that the service account needs permissions on agent roles and someone will need to connect to SQL Server using the service account (run as) so they can manage the agent job. It works, but not practical.
It’s not trivial, but there are roles and you can add a bit of extra code to help.
If you are storing passwords in a database, you should stop doing that immediately. We, as software developers and data professionals, should never know what passwords our customers are using. The same goes for most sensitive data: we technical staff probably don’t need to know what’s in there. Some stuff should be hashed, and some stuff should be encrypted.
There’s good advice here.
Solomon Rutzky is not a fan of Feature Restrictions in SQL Server 2019 CTP 3.2:
However, nobody’s perfect and occasionally the SQL Server team makes a mistake. A new feature introduced in SQL Server 2019 CTP 3.2, “Feature Restrictions”, is an unfortunate example of such a mistake. It’s a tragically misguided attempt at improving security that not only increases the chances of SQL Injection, but it also prevented useful changes from being made. “Misguided” because it doesn’t even accomplish it’s stated goal, and “tragic” because it a) most likely increases the chances of SQL Injection, and b) used up the time that could have been spent on implementing useful changes.
Read on to see why. Some of it is the travails of blacklisting (for example, the product blacklists WAITFOR but there’s more than one way to force a wait), some is offsetting behavior (as you introduce risk-reducing products, people compensate by taking additional risks up to the point of the prior equilibrium), and some of it is additional information leakage.
Andreas Wolter takes us through exactly which permissions are required for Query Store to work:
Typically, there are 3 aspects to the work with the Query Store, which can be reflected in roles:
1) Configuration – turning Query Store on and off, clearing the contents, flushing its contents to disk and changing its settings.
2) Viewing the reports or using the DMVs to analyze the Query Store contents (queries, plans and wait statistics) to gain insights but not necessarily having the authority to change anything
3) Actively change Plans by forcing or un-forcing, based on the information obtained from (2)
This is a nice overview of the problem and a fair amount of the solution.
K. Brian Kelley notes a slew of patches for July:
CVE-2019-1068 | Microsoft SQL Server Remote Code Execution Vulnerability
It’s a remote code exploit, but the attacker has to be connected to SQL Server because the vulnerability can only be exploited using a specially crafted query. The code would execute in the context of the database engine service account (hopefully not configured to run with administrative rights on the server or elevated rights in Active Directory).
Check this out and get it patched.
Jack Vamvas has a script to see if the sa account is disabled:
Often organisations have a SQL server security policy dictating the ‘sa’ login is disabled. There is some sound reasoning behind this policy. The primary reason is to decreases the surface area available to attack – and is a common principle of security.
When this is a requirement – I’ll add some code to disable ‘sa’ as part of the build and certification process. But , from time to time – the ‘sa’ login will be re enabled, so it’s important to build an alerting or reporting mechanism identifying the exception.
Click through for the script.
Grant Fritchey looks at sp_execute_external_script for potential SQL injection vulnerabilities:
The sharp eyed will see that the data set is defined by SQL. So, does that suffer from injection attacks? Short answer is no. If there was more than one result set within the Python code, it’s going to error out. So you’re protected there.
This is important, because the data set query can be defined with parameters. You can pass values to those parameters, heck, you’re likely to pass values to those parameters, from the external query or procedure. So, is that an attack vector?
No.
Another factor is that you need explicitly to grant EXECUTE ANY EXTERNAL SCRIPT rights to non-sysadmin, non-db_owner users, meaning a non-privileged user can’t execute external scripts at all. You can also limit the executing service account
Jason Brimhall shows all you can do by default with the public role in SQL Server:
It is amazing what some people will do that just doesn’t make sense. Granting permissions to the public role is one of these cases. That behavior also explains why there are documents and procedures for hardening the public role (here and here).
If necessary, I recommend locking down your public role. It will make your job a little easier and give you better rest at night.
Read the whole thing.
Kevin Feasel
2019-08-20
Backups, Security
No Comment