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.
Our customers use the SQL platform to store volumes of high-valued data assets for their organization. With Always Encrypted, we want to deliver additional security while ensuring complete integrity of stored user data. To achieve this, in a regular workflow SQL Server/Azure SQL Database disallows an application to insert data directly into an encrypted column. If the application has not enabled Always Encrypted in the connection string, an insert statement targeting an encrypted column will fail. To insert a value into a column protected with Always Encrypted, the application must connect to the database with Always Encrypted enabled in the connection string and pass a plaintext value of the datatype configured for the target column. Subsequently, the SQL client driver encrypts the value and sends the ciphertext to the database. This ensures plaintext data is encrypted and stored appropriately.
Read the whole thing.
The preview ODBC drivers for Linux now supports Ubuntu, RedHat and SUSE. This is Microsoft’s first ODBC Driver for SQL Server release supporting Ubuntu. You can now enjoy enterprise level support while connecting to SQL Server from Ubuntu. It also updates the drivers to unixODBC driver manager 2.3.1 support.
Full interoperability with distributions of Linux is something I’ve waited a long, long time for. This is one tiny step closer.
Here in this post I will talk about the new Block Predicate option available in the CTP 3.0 release. With this option we can restrict write access for specific users.
Block Predicates block all write operations like:
– AFTER INSERT and AFTER UPDATE
– BEFORE UPDATE
– and BEFORE DELETE
I want this to perform well in a busy production environment. I really, really do.
This is the big difference of this new feature, that the operations to encrypt/decrypt happen on the client NOT on SQL Server. That means that if your SQL Server is compromised, the key pieces to reveal the data are NOT with the server. This means that even if your DBA wants to see the data, if they don’t have access to the CLIENT application then they won’t be able to see the values.
Always Encrypted strikes me as something that will be incredibly useful for 2-3% of the population, somewhat painful for 3-5% of the population, and completely ignored by the rest. I’m currently on the fence about whether, three years from now, I will consider “completely ignored by the rest” to be a shame.