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.
Louis 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).
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.
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…
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.
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.
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.
A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. SQL Server principals come in more than flavor. (This is where a lot of confusion gets introduced.) The different types of principals are database and server. A database principal is also called a database user (sometimes just user). A server principal is also called a server login, or login for short.
Server and database principals are quite a bit different in function and come with different permission sets. The two are sometimes used, in reference, interchangeably, but that is done incorrectly. Today I hope to unravel some of what each is and how to see permissions for each type of principal.
Read the whole thing.
The first concept to understand about SQL Server’s security model is the difference between authentication and authorization.
Authentication defines who is being given a right. SQL Server formally calls the authentication objects principals, but you’ll also see the older terms logins and users.
Authorization defines what rights are being given. Formally, these are called permissions. In modern versions of SQL Server, permissions are very granular and can be found on nearly every object in the instance. There’s also a vast hierarchy that interrelates all of the permissions. (We’ll cover permissions in a future post. For now, know that they’re there.)
Ed has started a series on security basics. Given that there are relatively few people who talk security (and even fewer who know security), I consider this a great thing.
I ran across a question on network protocols recently, which is something I rarely deal with. Often the default setup for SQL Server is fine, but there are certainly times you should add or remove network connectivity according to your environment.
Microsoft’s guidance on protocols pushes you toward TCP/IP and that’s a good default.
Brent Ozar walks through one way to reduce SA account usage.
In a perfect world, you’d create a minimally-privileged AD login that only has limited access to specific databases.
However, when you’ve got a toddler running with scissors and razors, sometimes you’re happy just to get the razors out of their hands first, and then you’ll work on the scissors next. One step at a time. Preferably not running.
For now, create another SQL account with DBO permissions on all of the databases involved with the application. (If you’re dealing with multiple different tenants on the same server, give them each their own SQL login.) Let them be complete owners of their databases for now.
Power User: “EVERYTHING IS DOWN! THE SA ACCOUNT PASSWORD ISN’T WORKING! DID YOU RESET IT?”
Me: “Of course not. You told me not to.”
Power User: “THEN WHO DID IT?”
Me: “Oh, I have no way of knowing. Anyone who uses the account can change the password with theALTER LOGIN command. And you said everyone has it, right?”
That’s a nice account you have; it’d be a shame if something…unfortunate…were to happen to it.