Press "Enter" to skip to content

Category: Security

Reporting Services and SPNs

Greg Dodd shares a couple tips on creating SPNs for SQL Server Reporting Services:

Reporting Services often requires an SPN assigned to the account running the Reporting Services Service. You’ll know that you need to set this up when you try connecting to your Reporting Services instance from within the same domain and you are prompted for credentials. If SPN’s are setup correctly then your browser will work out the authentication for you and your users won’t need to login again.

Read on for an example, but also a pitfall and how to avoid it.

Comments closed

SQL Injection without Dynamic SQL

Erik Darling has a card trick for us:

I always try to impart on people that SQL injection isn’t necessarily about vandalizing or trashing data in some way.

Often it’s about getting data. One great way to figure out how difficult it might be to get that data is to figure out who you’re logged in as.

There’s a somewhat easy way to figure out if you’re logged in as sa.

Wanna see it?

Of course you do.

Comments closed

Why ALTER Implies DROP

Andy Mallon explains why granting ALTER operations to a user means that you’ve granted DROP permissions:

Dropping stuff is destructive. Anyone who’s ever dropped a plate or a car knows that. But dropping a table, procedure, or database makes it go away completely. The only way to un-drop something is to recover it from backup, or to re-deploy from source control (you do have all your code & schema in source control, right?). Getting back to the original question’s premise (it’s OK to change the object but not to completely make it disappear), I ask…does it even matter? What are you preventing? Is dropping an object worse than altering it?

Read on for Andy’s reasoning.

Comments closed

Auditing Database Backups

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.

Comments closed

When xp_logininfo Fails

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 /domain

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

Comments closed

SQL Server Agent Security

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.

Comments closed

Storing Passwords in the Database

Randolph West explains the problems with storing passwords in the database and explains the best alternative:

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.

Comments closed

Against Feature Restrictions

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.

Comments closed

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.

Comments closed

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.

Comments closed