Do you know how many different types of users there are in SQL Server?

The answer to this question is not 2, happy and unhappy users. This time we’re discussing security, not the end user experience of your perfectly tuned databases.

I was recently having a discussion around SQL Server security, more specifically about the logins and user. The question I was asked after going through couple examples was, that how many different types of users there actually are. While this seems like a trivial thing to answer, I have to admit that even after thinking about this for a little while, and still got the answer off by 2. As it turns out there’s bit more complexity to this than might be obvious at the first glance.

Without checking from anywhere, can you name all the different types of users? Read onward to find out.

(more…)

How to give everyone in Azure access to your database, the easy way

Sometimes I run into things in cloud that really just blow my mind away. Not that long ago I learned how you can give everyone in Azure, no matter what subscription or region they are in, an access to your database. And it was super easy too. It’s just one click to allow whole (Azure) world to start accessing your data.

Is this something I wanted to do, or would I recommend anyone to do it? No, not really. Also the documentation around this particular setting was less than great, so I decided to share what I learned.

(more…)

SQL Server 2019 security improvements: Feature Restrictions

Tilted Stop sign vector image
Restricted!

Very recently I was working on a customer databases, when I more or less stumbled on a something I had not noticed before. Apparently at some point the latest version of SQL Server (I was working with Azure SQL DB) had a new security enhancement added into it called Feature Restrictions. As this was something I had not heard about before, I figured this would be a good opportunity to dig in and learn more about it.

Note: As I was finishing up this post to add links and such, I noticed that the official documentation from Microsoft regarding Feature Restrictions has completely vanished.

(more…)

Hiding (mostly) in plain sight: Dynamic Data Masking

Masks on!

One of the more recent additions to SQL Server security features is the Dynamic Data Masking (DDM), included with the 2016 version. Like the Transparent Data Encryption I blogged about recently, DDM is a feature that is relatively easy to implement, and doesn’t require a lot of changes to the application. And just like pretty much everything is easy in a real life, it too has some limitations.

(more…)

Protecting Data at Rest: Transparent Data Encryption

I recently read an article which stating that since the GDPR came in force, there has been 59,000 data breaches reported in the EU. I must admit, that while I did anticipate that we’d see a surge in these numbers, due to reporting requirements in the legislation. I really did not expect the numbers to look that terrifying.

From the point of view of a SQL Server DBA, there is a number of different ways to protect your data. Some of them are even quite easy to setup, such as Transparent Data Encryption (TDE). So let’s have a look at how to set that up!

(more…)

Undeprecated SQL Server features

The Feature Reaper!
The Feature Reaper!

As we all know there are many features in SQL Server that have been deprecated over the time by Microsoft for one reason or another. In fact, there is a long list of features that are deprecated in the latest SQL Server 2017 release.

It is far less often that any of these features make a comeback, however that can apparently happen, as I just witnessed last week.

(more…)

Windows Firewall and antivirus software configurations for SQL Server.

One of the more important duties of a DBA is to make sure that their databases and the data is secure. In this post we’ll be looking at two utilities to increase the security of your server, the Windows Firewall and an antivirus software. Like with about everything else related to servers, you can’t just switch these on (well, you could, but…) and forget about them to get the best possible experience. They need to be properly configured for servers running Microsoft SQL Server. If you’re a DBA you might not be doing the configuration yourself, but you still need to tell your Windows administrators what they need to do.

(more…)