security – sqlsunday.com

An alternative to data masking

Dynamic data masking is a neat new feature in recent SQL Server versions that allows you to protect sensitive information from non-privileged users by masking it. But using a brute-force guessing attack, even a non-privileged user can guess the contents of a masked column. And if you’re on SQL Server 2014 or earlier, you won’t have the option of using data masking at all.

Read on to see how you can bypass dynamic data masking, and for an alternative approach that uses SQL Server column-level security instead.

Can your client or employer read your HTTPS traffic?

So you’re working for a client or employer who doesn’t let you bring your own device for security reasons. This is quite common and makes a lot of sense in several ways. But could they really read your HTTPS browser traffic?

Effective permissions on SQL Server

SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database.

Please don’t feed auditors and lawyers

control

Remember that time when you accidentally truncated a table in production? Or when you forgot the WHERE clause in your UPDATE statement? You’re not really a seasoned professional if you haven’t. There’s even a very apt name for that moment in time when the realization hits you: The oh-no second.

But what if there was some type of control to prevent this from happening? Like more restrictive controls, perhaps some type of peer-review process before you clicked “go”? Or even…

Start Management Studio with alternate Windows credentials

If you’re a consultant connecting to remote client servers, or if you have a heterogenous network environment with different Active Directory forests without established trust relationships, you’ll have a few extra challenges connecting to SQL Server using Windows authentication, and SQL Server authentication may not be available.

The SQL Server security model, part 1: principals

There are a number of layers in the SQL Server security model, giving you a nearly infinite number of ways to set up access control on your server and databases. Security is a huge topic, and there are literally entire books on it, so this series of articles is designed to give you just a quick overview of the SQL Server security model to get you started.

In this first installment, I’ll go through the different types of security principals that are available, as well as how they connect to each other.

Instant file initialization

When you create (or grow) the size of a database file, SQL Server will initialize the allocated space on the disk, i.e. fill it with zeroes. If you’re adding a large amount of space to your database file, this operation can take quite some time to complete. But just like there’s a “quick format” in most operating systems, you can allocate large chunks of database file space without initializing it. This is called “Instant file initialization” in the world of SQL Server.

A short introduction to application roles

Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.

A short post on SQL injection.

Whenever you run dynamic SQL code from an application or in a stored procedure, make sure you clean (called “escaping” in developer-speak) all those apostrophes and semicolons, or you may find yourself on the business end of an SQL injection.