Press "Enter" to skip to content

Category: Security

Tracking Security Changes with the Default Trace

Published 2019-10-09 by Kevin Feasel

Jack Vamvas shows how we can use the default trace with SQL Server to find security-related events:

Question: There were some recent user security changes on a SQL Server login user account. Is there a way to grab this information – via the default trace? I haven’t got a dedicated Extended events monitor set up – so I’m relying on the default trace . Management want to understand the details related to this security change. 
 
Answer: It is possible to report on security changes administered on SQL Server via the default trace. In fact – it’s excellent – except if the default trace configuration is set up to rollover the files.

Read on to see how.

Comments closed

Bulk Removing Reshare Permissions in Power BI

Published 2019-10-08 by Kevin Feasel

Gilbert Quevauvilliers takes us through bulk changing dataset permissions in Power BI:

Whilst this sometimes is what is required, I always suggest to people to remove this option and rather manage it by exception.

If you do leave this option enabled, it means if I had to share a report with Bob, Bob would then be able to reshare the report with someone else. This could potentially lead to other people gaining access to a report or data that they should not see.

Fortunately, I found a way to quickly remove the reshare option in the Power BI Service.

Click through to see how it’s done.

Comments closed

Mitigating SQL Injection with SQL Server 2019

Published 2019-10-08 by Kevin Feasel

Grant Fritchey is sick of SQL injection:

Instead, let’s talk about some of the common vectors of SQL Injection. Obviously, building and executing strings is the biggest issue. Appropriate use of parameters will do more to the fix the problem than almost any other step. However, it’s also enhanced by bad code on the front-end which doesn’t appropriately clean the data, inappropriate error handling, bad security, bad data isolation, and more.

The keys to the attack are to get back a few bits of information, usually in error messages in the case of a normal attack, or, through the use of the WAITFOR command in a blind attack (for more detail, I’m talking about this stuff at the PASS Summit). Getting error messages with information about the database makes it easier for me to hack your system (if I was evil). Knowing that I have a SQL Injection vector through the WAITFOR command helps me target appropriate systems (if I was evil).

For the most part, SQL injection isn’t a SQL problem—it’s an application problem (save for the case when you generate dynamic SQL and concatenate in input parameters). SQL Server-based solutions will only do a little bit; fixing the app code is the best answer.

Comments closed

Listing Windows Users with Powershell

Published 2019-09-27 by Kevin Feasel

Jack Vamvas shows us how we can use Powershell to list Windows users in an Active Directory group:

Question: How can I use Powershell to list out Windows users? Are there Powershell cmdlets which can report on Windows users ?
Answer: There are “out of the box” Powershell cmdlets which will support the requirement . How you apply the powershell cmdlets will depend on how much detail is required.

Jack has a few examples here as well.

Comments closed

Troubles with Dropping Logins

Published 2019-09-27 by Kevin Feasel

Pamela Mooney takes us through a scenario involving dropping a user and login, and some of the difficulties which might arise:

I had to obscure a lot, but the bottom query results correlate to the top results.  The first line of the bottom query results show the grantor of the permissions, and the bottom line is the grantee.  In this case, a login was explicitly denied impersonation on a server role.  I’m using this example because it is really quirky to fix.  Most often, you’ll just reverse the permissions, using pretty standard syntax. Even easier, right click on the login, go to the “Securables” tab, and remove the permissions.  However, if you are a fan of the TSQL approach, this one is not so straightforward, so it’s a good one to show.  

Click through for a demonstration.

Comments closed

Why Root Containers are Troublesome

Published 2019-09-19 by Kevin Feasel

Andrew Pruski explains to us why it can be bad to have a container user running as root:

Recently I noticed that Microsoft uploaded a new dockerfile to the mssql-docker repository on Github. This dockerfile was under the mssql-server-linux-non-root directory and (you guessed it) allows SQL Server containers to run as non-root.

But why is running a container as root bad? Let’s run through an example.

Just as with physical devices and VMs before them, Docker containers can do a lot of damage if you’re logged in as root.

Comments closed

Migrating to a New Hashing Algorithm

Published 2019-09-10 by Kevin Feasel

Randolph West has some ideas about how to remove some of the pain of changing your hashing algorithm for passwords:

Let’s assume that you have inherited a database which stores passwords in cleartext in a column called Password. This is not a good scenario. For one thing, it is a temptation to access information we wouldn’t normally have access to, by using that person’s credentials. For another, due to a human trait called “using a computer,” people reuse passwords all the time. I do it myself and I’m writing a post about security (out of 1,089 items in my 1Password vault, 82 items contain reused passwords).

Randolph has a few ideas, but there’s one which I put into place a long time ago: automatic migration. A system was MD5 (it was a while ago) and I changed it to a better password-hashing algorithm. If the existing user record had a binary the length of an MD5 hash, I’d perform the lookup (to ensure that this was the correct user) and then re-hash using my new algorithm, which had a longer binary length. Over time, people migrated to the new system without any issue. Eventually, when the set of remaining users was small enough, we took more active measures. It wasn’t quite as good as the ideal of forcing resets on everyone, but that was deemed as going overboard for our user base.

Comments closed

Reporting Services and SPNs

Published 2019-09-09 by Kevin Feasel

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

Published 2019-08-30 by Kevin Feasel

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