Query Store Required Permissions

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.

Security Update for SQL Server

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.

Checking if an Account is Disabled

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.

ML Services and Injectable Code

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

What Public Permissions Get You

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.

Ownership Chaining and Temp Stored Procedures

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.

Making Dynamic SQL Safe

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 issues

I 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.

Wireshark and the SQL Server Browser Service

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.

Antivirus and SQL Server

Randolph West proffers advice should your IT team require installing antivirus software on a server with SQL Server running:

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.

Cosmos DB Role-Based Access Control

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.

Categories

September 2019
MTWTFSS
« Aug  
 1
2345678
9101112131415
16171819202122
23242526272829
30