xp_cmdshell Not A Security Risk

Kevin Hill makes a great point:

A stored procedure that, out of the box, is disabled and has no explicit rights granted (or denied) is locked down to everyone but those in the sysadmin server role.

If someone exploits your SQL Server via xp_cmdshell, its because you LET them, either by granting permissions or by putting someone in sysadmin that clearly should not have been there.

For this in more detail, check out Sean McCown’s post from 2015.

TDE And Encrypted Backups

Arun Sirpal shows that Transparent Data Encryption will also lead to encrypted backups:

Ok great let’s check this backup file using a cool tool (XVI32). I really want the contact number of a guy called SQLDOUBLEG because I need his help tuning my SQL Servers so I go looking for a text string, hopefully the phone number will be close by.

As Arun points out, you don’t need TDE to get encrypted backups, but it does the job.

Replication And TDE

Drew Furgiuele looks at how replication interacts with Transparent Data Encryption:

But what happens if we set up a transactional replication publication on this database and do a snapshot? Remember that when you create a publication, your distributor and subscriber(s) need to know which network share (or FTP server) to drop all the data and schema definitions to so they can be read in by the distribution agent and recreated. In my example, I’m dropping them to a network share. Once the snapshot completes, let’s go check out our subscriber database…

Uh oh. The same query returned zero results at the subscriber. Which means no encryption! Replication won’t replicate encryption, at all. So if you have a requirement to encrypt your data at the source, you’ll need to do it on your subscribers too.

Drew points out a couple important gotchas which might lead to you exposing information you didn’t intend to make available.

Always sa

James Anderson rails on inappropriate sa account usage:

I can already hear managers saying:

If you don’t trust your employees, why employ them in the first place?

Well there is the whole accidental damage thing. I guess you could cover that by having a good backup\restore process (if your RTO and RPO permitted the downtime) but don’t expect to pass any security audits coming your way. Hint: your clients wont like this.

Plus, supposing everybody knows the sa account, there’s no way to know who accidentally(?) dropped the customer database.

Everyone’s A Sysadmin

Raul Gonzalez has a script to see if you’re in a terrible security situation:

I’m not sure what it’d scare me more, if there are many [sysadmin] or just [sa] because the first one is scary, but the second involves to find out who knows the [sa] password and knowing who did what, can be a real pain in the neck.

One way or another, as I said, I want to know the different people and level of access to my server[s], so back in the day I created this stored procedure which now I want to share with you guys.

We can find all that info using DMV’s and in my case I use sys.server_principals, sys.server_role_members and sys.server_permissions and some stored procedure which I bet it’s not that well known, sys.xp_logininfo which help to get more granular picture from Windows AD Groups.

Click through for the script.

Knox With Active Directory

Jon Morisi shows how to configure Knox to work with Active Directory:

I’ve recently been doing some work with Hadoop using the Hortonworks distribution.  Most recently I configured Knox to integrate with Active Directory.  The end goal was to be able to authenticate with Active Directory via Knox (a REST API Gateway) and then on to other services like Hive.  I also configured Knox to point to Zookeeper (HA service discovery) vs. Hive directly, but that’s really more detail than we need for integrating Knox with AD.

The Knox documentation is really good and very helpful:

Worth the read if you’re putting together a Hadoop cluster.

Power BI Security Updates

Ginger Grant notes that there have been some big changes to Power BI security:

People who have been granted Power BI administrator rights will also notice a modification to the Admin screen. The March 2017 update to Power BI provides a major change to the security model in Power BI. Previously all the security settings were set at the Tenant Level, meaning that all the privileges were granted to all users. If I wanted to allow one group within the organization to be able to publish reports to the web, but I did not want to allow everyone to publish reports to the web, there was no way that this could be accomplished. All that has changed. It is now possible to include or exclude groups of users from having rights in Power BI. Users can be classified into security groups in Azure Active Directory, either through the Office 365 Admin Center or via the Azure AD Admin Center. Once created the security groups can be used in Power BI. Security Groups are not the same thing as the groups created in Power BI when a new work group is created.

Read the whole thing.

Auditing Login Attempts

Cedric Charlier shows how to use server audits to track failed and successful logins (and logouts):

The core issue is that we have many users and logins on our databases and we have huge doubt their respective needs. The root cause is identified: sometimes, for a short period of time, we’re making exceptions to our own rules and let a few other friend projects access to our DEV database. On some other cases, we’re connecting our own solution in DEV environnement to the QA environnement of another solution. Why … planning, data quality issue, … we’ve valid reasons to do it … but these exceptions should be removed as soon as possible. And you know what? People forget. Nowadays, on our largest solution, we have 20 users but only 7 of them are expected and documented … other should be removed. But before executing this cleanup, we’d like to be sure that these users are not effectively used by other solutions. If it’s the case, we’ll need to update first the configuration of the corresponding solution.

Click through for a few scripts to show how to set this up as well as how to query the audit log.

Table And Column Deny Precedence

Shane O’Neill has a bit of fun:

Paranoia Kicks In:

He can’t have updated it…right?

I mean, he doesn’t have db_datawriter permission; I’ve denied him update permission on my table. I’m safe!…right?

This is a fun article which highlights just how hard getting correct, granular permissions can be.

Securing A Data Driven Application

K Brian Kelley has started a series on securing applications which connect to SQL Server:

In conjunction with the webinar I gave last month for MSSQLTips, I’ve started an article series on application database security design.

Read Part 1 – Authentication for SQL Server

The issue with a one hour webcast is one can’t cover a broad topic like application database security design in any depth. However, these webcasts are useful because they point out, at a high level, what to look for. It was my intent all along to do the webinar and follow up with a series of articles that cover each topic in detail. I’m not sure how many articles I’ll end up writing, as I want to make sure I cover each topic in the depth it needs while still keeping the article length manageable.

This first post is all about comparing and contrasting credentials options and authentication methods.


March 2017
« Feb