Press "Enter" to skip to content

Category: Security

Gaining SQL Server Access Without A Login

Jason Brimhall shows how you can push your way onto a SQL Server instance without a login:

If you really cannot cause a service disruption to bounce the server into single-user mode, my friend Argenis Fernandez (b | t) has this pretty nifty trick that could help you. Truth be told, I have tested that method (even on SQLExpress) several times and it is a real gem. Is this the only alternative?

Let’s back it up just a step or two first. Not having access to SQL Server is in no way the same thing as not having access to the server. Many sysadmins have access to the windows server. Many DBAs also have access to the Windows server or can at least work with the sysadmins to get access to the Windows server in cases like this. If you have admin access to windows – then not much is really going to stop you from gaining access to SQL on that same box. It is a matter of how you approach the issue. Even to restart SQL Server in single-user mode, you need to have access to the Windows server. So, please keep that in mind as you read the article by Argenis as well as the following.

Beyond the requirement of having local access to the server, one of the things that may cause heartburn for some is the method of editing the registry as suggested by Argenis. Modifying the registry (in this case) is not actually terribly complex but it is another one of those changes  that must be put back the way it was. What if there was another way?

As luck would have it, there is an alternative (else there wouldn’t be this article). It just so happens, this alternative is slightly less involved (in my opinion).

If you’re counting, that’s three methods for the price of one.  It’s also an important reminder that if an attacker has administrative access to your Windows server, there’s not much you can do to prevent that attacker from gaining access to SQL Server.

Comments closed

HASHBYTES Performance In SQL Server

Joe Obbish takes a look at how HASHBYTES doesn’t scale well:

The purpose of the MAX aggregate is to limit the size of the result set. This is a cheap aggregate because it can be implemented as a stream aggregate. The operator can simply keep the maximum value that it’s found so far, compare the next value to the max, and update the maximum value when necessary. On my test server, the query takes about 20 seconds. If I run the query without the HASHBYTES call it takes about 3 seconds. That matches intuitively what I would expect. Reading 11 million rows from a small table out of the buffer pool should be less expensive than calculating 11 million hashes.

From my naive point of view, I would expect this query to scale well as the number of concurrent queries increases. It doesn’t seem like there should be contention over any shared resources, so as long as every query gets on its own scheduler I wouldn’t expect a large degradation in overall run time as the number of queries increases.

Joe’s research isn’t complete, but he does have a conjecture as to why HASHBYTES doesn’t scale well.  That said, the most interesting thing in the post to me was to see Microsoft potentially using bcrypt under the covers for HASHBYTES calculation—if that’s really the case, there actually is a chance that sometime in the future, we’d be able to generate cryptographically secure hashes within SQL Server rather than the MD5, SHA1, and SHA2 hashes we have today.

Comments closed

Securing Power BI Report Server

Steve Hughes gives some advice for securing a Power BI Report Server installation:

You have essentially three layers of access to the report file security in Power BI Report Server.

  1. The portal itself can be secured. You can and should limit access to the reports by only allowing specific users or groups access to the report portal.
  2. Folders can be used to provide more granular security over a group of assets in the report portal. In the image above, I created a folder called PBI Secure Reports. A specific AD group has access to this folder. If a user does not have permissions to the folder, the folder does not show up in the portal and they cannot access the folder or the assets, including Power BI reports, stored in this folder.
  3. Individual reports can be secured as well. I never recommend this option as it becomes administratively difficult to manage. However, the capability is there is a single asset needs to be secured in this fashion.

These options work for any asset stored in the Report Portal and are not limited to Power BI reports.

Power BI Report Server is a different animal from standard Power BI, so securing it will be a bit different as well.

Comments closed

What TDE Does To Query Performance

Matthew McGiffen has a few tests on using Transparent Data Encryption:

By the time it had been executed 5 times (with the memory flushed between each execution) each query read about 600,000 pages sized at 8kb each – just under 5GB. If it took 50 seconds on the decryption of those pages, then each page took about 1 twelfth of a milli-second to decrypt – or alternatively, TDE decrypted about 12 pages per millisecond. Or in terms of disk size, 100MB per second. These were tests on a server with magnetic spinning disks (not SSDs) and you can see from the above figures, the straight disk access took about 40 seconds on its own.

When TDE doesn’t read from disk it doesn’t add any overhead, but how do we quantify what the overhead to queries is when it does have to access the disk?

Matthew has some good advice here, and I’d be willing to say that his experience is within the norm for TDE and doesn’t directly contradict general guidelines by enough to shift priors.

Comments closed

Using The Public Role

Kenneth Fisher explains the public role in SQL Server:

A common misunderstanding is that the CONNECT permission lets you do more than just connect to a database. It doesn’t. Connection only. So how come there are some things that everyone can do once they are connected to a database? Well, it’s the public role. Everyone is a member and that can’t be changed. In fact, you can’t even disable it. Oh, and I should point out that every database has one.

So what does that mean? If you have a table that you want everyone to have read access to you could grant the permission in public.

I never use the public role for anything, and so it’s a benign role.  I strongly dislike database security tools which flag the public role as a risk, mostly because I made the mistake once of believing the tool and had to start granting things like CONNECT to each new login.

Comments closed

Multi-Layered Security With Docker Containers

Jessie Frazelle points out the advancements in security that Docker has made over the past couple of years:

Container runtimes have security layers defined by Seccomp, Apparmor, kernel namespaces, cgroups, capabilities, and an unprivileged Linux user. All the layers don’t perfectly overlap, but a few do.

Let’s go over some of the ones that do overlap. I could do them all, but I would be here all day. The mount syscall is prevented by the default Apparmor profile, default Seccomp profile, and CAP_SYS_ADMIN. This is a neat example as it is literally three layers. Wow.

Everyone’s favorite thing to complain about in containers or to prove that they know something is creating a fork bomb. Well this is actually easily preventable. With the PID cgroup you can set a max number of processes per container.

Interesting reading from an insider.

Comments closed

Storing An Encrypted Password In The Solr Configuration File

Jon Morisi shows us how to store an encrypted password in Solr’s configuration file, rather than storing the password in plaintext:

The config file has a lot of options, in short this is where you configure a database connection string and reference your jdbc jar file. Full details are here.  By default any of the examples that come with the Solr distribution use a plain text username and password.  This can be potentially viewed from the front end:
http://hostname:8983/solr/ > Select Collection from the drop-down > Click data Import > expand configuration
Obviously we do not want to store our username and password in plain text.  The config file includes an option to encrypt the password and then store the key in a separate file.

Storing passwords in plaintext is a classic mistake that I see far too often.  And then when someone checks in that config file to a public GitHub repo…

Comments closed

Installing OpenSSH Server: Windows 10 Edition

Anthony Nocentino shows us how to install OpenSSH server on Windows 10 update 1803:

So in yesterday’s post we learned that the OpenSSH client is included with the Windows 10, Update 1803!  Guess, what else is included in this server, an OpenSSH Server! Yes, that’s right…you can now run an OpenSSH server on your Windows 10 system and get a remote terminal! So in this post, let’s check out what we need to do to get OpenSSH Server up and running.

First, we’ll need to ensure we update the system to Windows 10, Update 1803. Do that using your normal update mechanisms.

With that installed, let’s check out the new Windows Capabilities (Features) available in this Update, we can use PowerShell to search through them.

Anthony goes through the steps for configuration, so check that out.

Comments closed

Issues With TDE And Backup Compression

Ned Otter describes the troubled history of the union of Transparent Data Encryption and backup compression:

The history of TDE and backup compression is that until SQL 2016, they were great features that didn’t play well together – if TDE was in play, backup compression didn’t work well, or at all.

However, with the release of SQL 2016, Microsoft aimed to have these two awesome features get along better (the blog post announcing this feature interoperability is here). Then there was this “you need to patch” post, due to edge cases that might cause your backup to not be restored. So if you haven’t patched in a while, now would be a good time to do so, because Microsoft says those issues have been resolved (although that seems to be disputed here).

My sympathies definitely lie toward backup compression over TDE if forced to choose between the two.

Comments closed

Reverting After Impersonation

Kenneth Fisher shows a couple common issues when executing as another user or login:

The problem is that sometimes I’ll forget to revert back.

1
EXECUTE AS LOGIN = 'Doc';

Msg 15406, Level 16, State 1, Line 8
Cannot execute as the server principal because the principal “Doc” does not exist, this type of principal cannot be impersonated, or you do not have permission.

Hey! I know I Doc exists, and I’m sysadmin. How is this not working? Simple enough, I forgot to REVERT. There are two simple options here. I either completely forgot to run it, or I was in the wrong database.

Read the whole thing.

Comments closed