Category: Security

Steve Jones wants to find minimum required permissions for Red Gate’s DLM Dashboard:

The principle of least privilege should apply everywhere, certainly in production, but also in development. If you limit permissions in development, you might cause a few headaches, but you’ll understand the issues and solve them early on. More importantly, if you have security flaws, they aren’t in production systems where data is exposed.

SQL Server security isn’t that hard, but it can be cumbersome. Set it up properly in development, keep your scripts (even from the GUI), and then use those scripts for your production systems.

Red Gate’s usually pretty good about publishing minimum permission requirements; some vendors will simply say “you need sysadmin or db_owner.”  I’m not enthralled with vendors which take the lazy way out.

Comments closed

Tom LaRock shows how to exfiltrate data in a database protected by Transparent Data Encryption:

Enabling TDE does not protect your BACPAC files, just your database backups. If you are relying on TDE to protect your data at rest then allowing users to create BACPAC files will put you at risk. But no more risk than any other user choosing to run a SELECT statement and save the data somewhere (or perhapsjust use PowerBI to open a connection and import to Excel).

TDE has a single, specific purpose.  If you want something more stringent, SQL Server 2016 Always Encrypted might be an option.

Comments closed

Kenneth Fisher has a user which should have rights but is unable to access the database in question:

The other day I ran across an interesting problem. A user was logging in but didn’t have access to a database they were certain they used to access to. We checked and there they were. Not only was there a database principal (a user) but it was a member of db_owner. But still no go. The user could not connect. I went to the database and impersonated them and then checked sys.fn_my_permissions. They were definitely a member of db_owner. I tested and yes, I could read the tables they needed, and yes they could execute the stored procedures they needed to execute. So what was wrong?

Keep those principals in alignment.

Comments closed

Louis Davidson has a two-part series on dynamic data masking in SQL Server 2016.

Part 1:

An interesting feature that is being added to SQL Server 2016 is Dynamic Data Masking. What it does is, allow you to show a user a column, but instead of showing them the actual data, it masks it from their view. Like if you have a table that has email addresses, you might want to mask the data so most users can’t see the actual data when they are querying the data. It falls under the head of security features in Books Online (https://msdn.microsoft.com/en-us/library/mt130841.aspx), but as we will see, it doesn’t behave like classic security features, as you will be adding some code to the DDL of the table, and (as of this writing in CTP3.2, the ability to fine tune who can and cannot see unmasked data isn’t really there.)

Part 2:

The moral here is that need to be careful about how you use this feature. It is not as strict as column level security (or as Row Level Security will turn out to be, which is the next series of blogs to follow), so if a user has ad-hoc access to your db, they could figure out the data with some simple queries.

Louis’s second part is particularly interesting, as he delves into the various ways in which you can back into answers (some of which, like casting values to other types, have been fixed).

Comments closed

Aaron Bertrand has a great explanation of how to plan for TLS 1.2 support in SQL Server:

It seems straightforward, but as of today, not all builds will enable you to rush out and convert to TLS 1.2 exclusively. Here is what I suggest for each set of builds (in addition to patching .NET Framework, SQL Server Native Client, ODBC, and JDBC on all machines)

A protocol change seems like a small thing, but it suddenly gets to be a big thing when services stop working.

H/T Matt Slocum.

Comments closed