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.
Kenneth Fisher wants to see how security for temporary stored procedures works:
With normal stored procedures there is something called ownership chaining. Without going into a lot of detail about what it means, let’s say that you run a stored procedure. SQL is going to check the permissions to see if the stored procedure can update that table right? Well, who’s permissions? Yours? Well, yes, if you have permissions you are fine. But you won’t always. If you don’t then SQL is going to check the owner of the stored procedure (dbo?) and see if they also own that table. If so then we’re golden, perform the update. That might seem scary but it’s pretty normal.
What was scary (at least to me) is the question “How is that handled for a temp stored procedure?”
Read on for the results of Kenneth’s tests.
Erik Darling explains patiently that if you use sp_executesql wrong, you don’t get the benefits of using it right:
The gripes I hear about fully fixing dynamic SQL are:
– The syntax is hard to remember (setting up and calling parameters)
– It might lead to parameter sniffing issuesI can sympathize with both. Trading one problem for another problem generally isn’t something people get excited about.
But there are good reasons fully to fix it, so read on.
Joy George Kunjikkur takes a look at the SQL Server Browser service using Wireshark:
Normally on development environment the 1434 port would be open and SQL Server Browser running in SQL Server machine. If developers code based on that assumption, it may fail in higher environments. Most of the developers know what might have happened. But some may not and ends up in complete darkness. The symptom of this issue will be connection failure from application to SQL Server database.
This post is mainly to demonstrate how to check network failures on SQL Server Browser operations. The prerequisite is the availability of Wireshark in the machine where application runs and basic knowledge how to run the same. The above link has some screenshots which would help to run Wireshark for UDP connections.
This is usually the first service I disable post-installation.
This is why it is documented that we should exclude SQL Server from any AV (anti-malware) detection products, so that it can get on with doing what it does best.
Yes, it’s formally documented. This is why we should read documentation when installing things. While it’s super-easy to click “Next,” “Next,” “Next,” that should not be the case with a complex product like SQL Server.
Read on for the list of exceptions you should add and processes to avoid scanning.
Hasan Savran takes us through role-based access control in Cosmos DB:
Role-based access control (RBAC) is available for Azure CosmosDB. By using RBAC, you can manage who has access to CosmosDB resources. You need to have a profile in Azure Active Directory to assign RBAC roles to users, groups or other targets. There are 4 built-in roles you can use. Microsoft announced the CosmosDB Operator
role recently.
Click through for that list.
Kevin Feasel
2019-07-31
Query Store, Security