Voice of the DBA

Tag: security

SSMS 17.4 Vulnerability Assessment

SSMS 17.5 is out as of February 2018, but the the Vulnerability Assessment (VA) was released in SSMS 7.4

It seems that the Microsoft tools team is trying to build us better tools that come with the platform. There are good third parties that build tools, such as my employer, Redgate Software, but I am glad that Microsoft is also providing a little more value. This is especially welcome in the area of security.

In SSMS 17.4, the upgrade came with one goodie: the VA. This is an analysis that will help you determine if you potentially have issues with your instances and databases. This is an assessment of a database, but there are server implications as well.

Once you install or upgrade SSMS, you can right click on a database and choose Tasks, Vulnerability Assessment.

This is actually a menu of a couple items. You have the choice to run a scan or open an existing scan that you might have saved on your system.

When you run a scan, a new dialog opens that asks you where to save the scan. You can change the path, and once you click OK, the scan runs.

A new tab opens in the query window space with the results of your scan. This gives you the bad news first. Those items you failed. In my case, I had 5 items.

These items are listed as high, medium, or low risk. I haven’t dug into these too deeply, so I won’t comment on the appropriateness, but look for more information at SQLServerCentral soon.

I did better on the passing side, 49 items.

If I pick an item, I can mark this as approved as the baseline setting. For example, on this instance, I want Remote Admin connections.

If I click “Approve as Baseline”, I get a dialog. I’ll say yes.

This item now has a baseline marked, or rather, the absence of a baseline removed. I also get a note that there are changes near the top.

If I run a new scan, this issue doesn’t appear.

This is now an item in the Passed tab.

This is simple, and perhaps trivial, but having this built into a tool means that you can now start to see if things change. There is likely lots of opportunity to build on top of this and perhaps aggregate data or make it more consumable. Look for other companies to add to this, but for now, it’s nice that Microsoft is adding security help to SSMS.

You can read more about the Vulnerability Assessment on docs.microsoft.com.

March 5, 2018
Republish: Elevation of Privileges

It’s another day in the office in the UK and I’m republishing Elevation of Privileges while I’m stuck in meetings all day.

March 1, 2018
Classifying Data with SSMS 17.5

I upgraded SSMS to 17.5 recently and found an interesting addition. This version has incorporated the ability to classify data. With the GDPR coming for many of us, this is a welcome addition.

This is a quick look at this feature.

Classify a Database

If I select a database and right click it in SSMS, I get a few new items in the Tasks menu (as shown).

I’ll select “Classify Data”, and I get a new tab opened. I see there are some recommendations and also a list of classifications of data.

There’s a getting started link, which takes me to the SQL Server Security Blog. I suspect that’s an incorrect link. I think it should go here: SQL Data Discovery and Classification.

Below this, I see a list of the recommendations. This has grabbed tables that appear to continue to contain some data that might be sensitive and require classification. One of the tenets of the GDPR is that you know your data. You aren’t allowed to figure this out later, but rather you must proactively know what data you are collecting and processing.

Here we can see a few drop downs to the right. I’ll scroll and look at these. First is the Information Type. This is listed as a name, but I have other options I can set. The list is the types of that that might be sensitive information about a data subject (a human or entity) that I need to classify.

Beside this is the sensitivity label. My choices here are shown below. These range from public information, which removes some of my responsibility to highly confidential and applying to the GDPR.

If I’m happy with these recommendations, I can select them all (or a subset) on the left. I can click the “Accept” button to add them to the classifications I have for this database.

This doesn’t save them, but adds them to the list. At the top of this tab I can see the need to “Save” my changes.

Once I’ve done this, I could add more, or view a report. The report shows me this:

Implementation

My guess was that these are implemented as extended properties, which makes sense. That’s how many things could make SQL Server better, and I’m right. If I examine the EP for the firstname column in one table, I see this:

This was the column I changed to public information. The lastname column in the same table is marked as confidential.

Helpful?

Ultimately is this useful? Yes. I can see other products taking advantage of this, such as the new Data Masker from Redgate, which could let you know which columns are sensitive and not masked. I’d also expect that this is useful and important for ETL and other operations to carry this metadata to new columns that might contain transformations or movement of this data.

February 20, 2018
Enabling Guest in a Database–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The guest account exists in all your databases. This is installed by default, and guest is used to map a login that doesn’t otherwise have access to a database.

Sound scary?

It should. This would be bad if any login could connect to any database, potentially reading data using the guest account. Fortunately Microsoft has done two things. First, guest is disabled in all user databases. This is because it’s disabled in model, which is our template.

Second, guest is typically assigned no rights. It’s a member of the public role, which also has no rights by default.

Enabling Guest

If you want to allow anonymous access for logins through the guest account, it’s easy. Be wary and careful of doing this and be sure you understand what rights have been granted to public if you do this. In general, I’d expect auditors and any compliance/security officers to be against this, but you should check.

The user exists already, and just needs the CONNECT permission to get enabled. You can do this with this code:
```
GRANT CONNECT TO guest
```
If you want to remove permission, use
```
REVOKE CONNECT FROM guest
```
That’s it. Remember, by default this user can’t access any objects. I would recommend you not grant rights to guest, but use roles. Either one of the built in ones, or better yet, create your own role and choose limited permissions.

SQLNewBlogger

One of the ways you can showcase your knowledge, show you’re learning, and show you’re motivated to enhance your career is blogging. This post is an example of what you could write, in your own words, about something you’ve learned.

This one took my about 5 minutes after I’d spent a little time getting guest enabled for a test project.
February 15, 2018