A Fine Slice Of SQL Server
James Serra has a number of links to Azure SQL Database security features:
Life we be so much easier if we could just trust everyone, but since we can’t we need solid security for our databases. Azure SQL Database has many security features to make you sleep well at night:
Most of these are exactly the same as the on-premise product—at least the SQL Server 2016 version—but it goes to show that Azure SQL Database has grown up quite a bit.
Comments closedChris Bell has a new script to audit SQL Server permissions:
I wanted the script to do a few things. Tell me who is in a AD Group that was granted rights. IT is one thing to see the group name, but that doesn’t really tell me who has access. I also wanted the output to be a little more user readable, so I formatted the output some. There are other things I did too, but you can fun reading through the code and comparing the 2 sources.
Knowing who’s allowed to do what is key to having a successful security posture. This script won’t tell you object-level permissions, but at least gives you an idea about role and group membership.
Comments closedSteve 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 closedTom 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 closedKenneth 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 closedLouis Davidson has a two-part series on dynamic data masking in SQL Server 2016.
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.)
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 closedErik Darling ran into an issue with endpoint security while setting up mirroring:
This is the error text:
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://ORACLEDB.darling.com:5022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
SUPER SLEUTH
Alright, that’s silly. I used the GUI. Instead of going to bed I’ll spend some time checking all my VM network settings. BRB.
I’m back. They were all correct. I could ping and telnet and set up linked servers and RDP. What in the name of Shub-Niggurath is going on with this thing?
These things always happen right before bed, right before the big meeting, right before lunch. They never happen on a slow Tuesday afternoon, it seems…
Comments closedAaron 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.
Comments closedJens Vestergaard walks through permission scope in SQL Server Analysis Services:
What this post will not be about: The how to setup basic dimension security in SSAS nor How do you manage Security.
In this post, I will highlight the difference between standard NTFS permission scope and the way SSAS handles Allowed and Denied sets when dealing with multiple roles. So if you define multiple roles on your solution, you should be on the lookout, because SSAS has some surprises.
It’s interesting that allowed permissions take precedent over denied permissions, as that’s not the norm for either NTFS or the SQL Server database engine.
Comments closedRonit Reger introduces us to Dynamic Data Masking:
DDM can be used to hide or obfuscate sensitive data, by controlling how the data appears in the output of database queries. It is implemented within the database itself, so the logic is centralized and always applies when the sensitive data is queried. Best of all, it is incredibly simple to configure DDM rules on sensitive fields, which can be done on an existing database without affecting database operations or requiring changes in application code.
This looks like a nice way of getting some data masking on the cheap. It also looks like there are a couple of built-in functions for defining data types, as well as the UNMASK permission so that you don’t need to modify application code to call some type of unmasking function.
Comments closed