A Fine Slice Of SQL Server
Gilbert Quevauvilliers walks us through access control on Power BI pages:
With the update to the latest version in Power BI Desktop there is now the capability to control which users will see which pages in a report.
This is achieved using a combination of Row Level Security (RLS) and conditional formatting. This makes it a secure way of controlling access for specific users. I will demonstrate how this works below.
This is a clever solution to the problem of access control.
Comments closedWhat are the steps involved in a process like this?
Generally speaking, the quickest way I know is:
1. Get the permissions of the source login/user (For each database because we don’t want to miss any permission)
2. Save them to a .sql file
3. Open the file and replace ‘srcUser’ by ‘newUser’
4. Execute
Claudio shows how you can accomplish this quickly.
Comments closedRick Spurgeon and Nikoleta Verbeck give us some tips on securing Apache Kafka:
Confluent Platform provides Role-Based Access Control (RBAC), which addresses the gaps listed above. RBAC and ACLs perform similar functions but approach the problem differently and can be used independently or cooperatively. RBAC is powered by Confluent’s Metadata Service (MDS), which integrates with LDAP and acts as the central authority for authorization and authentication data. RBAC leverages role bindings to determine which users and groups can access specific resources and what actions can be performed within those resources (roles). RBAC is empowered on the Kafka cluster by way of Confluent Server. Confluent Server is a fully compatible Kafka broker which integrates commercial security features like RBAC.
The list of items is pretty straightforward with no major surprises.
Comments closedAnother recent change is that all newly created databases have “Encryption Enabled” set to True. This enables Transparent Data Encryption. By default, if you haven’t created your own key, it will use the service-managed key. A bug I’ve found in SSMS is that even though you set “Encryption Enabled” to false, it will still enable TDE. This is not the behavior when using T-SQL.
Read on to understand the ramifications of this change.
Comments closedNiko Neugebauer walks us through improvements in certificate management with SQL Server 2019:
If you have ever used them for connection encryption (TLS 1.2), you might have had some battles with the certificates, having to go into the registry to edit the thumbprint and if you doing a Failover Cluster or Availability Group installation – you would have to enjoy this operation on the multiple nodes. Not-so-very-user-friendly to say at least!
So many times, it would scare-off a non Server/Database Administrator or a junior Server/Database Administrator from trying those features.
Niko also mentions something very interesting about SQL Server Configuration Manager compatibility at the end of the post.
Comments closedAzure Databricks is a Unified Data Analytics Platform that is a part of the Microsoft Azure Cloud. Built upon the foundations of Delta Lake, MLflow, Koalas and Apache SparkTM, Azure Databricks is a first party PaaS on Microsoft Azure cloud that provides one-click setup, native integrations with other Azure cloud services, interactive workspace, and enterprise-grade security to power Data & AI use cases for small to large global customers. The platform enables true collaboration between different data personas in any enterprise, like Data Engineers, Data Scientists, Business Analysts and SecOps / Cloud Engineering.
In this article, we will share a list of cloud security features and capabilities that an enterprise data team could utilize to bake their Azure Databricks environment as per their governance policy.
Much of this is fairly straightforward, but it is nice to have it all in one place.
Comments closedK. Brian Kelley walks us through the concept of ownership chaining in SQL Server:
Ownership chaining is a security feature in SQL Server which occurs when all of the following conditions are true:
– A user (which could be an app through a login/service account) tries to access an object that makes a reference to another object. For instance, the user tries to execute a stored procedure that accesses other objects or a SELECT from a view that accesses other tables.
– The user has access to the first object, such as EXECUTE rights on the stored procedure or SELECT rights on the view.
– Both objects have the same owner.In this case, SQL Server will see the chain between the object the user called and the object being referenced. SQL Server will also determine that the owner for both objects is the same. When those conditions are met, SQL Server will create the ownership chain.
Read on for an in-depth example of ownership chaining and how it solves certain problems around managing database rights.
Comments closedJason Brimhall dumps a server-side trace in favor of extended events:
Some time ago, I wrote an article for SQL Server 2008 to help determine the use of the server since SQL Server 2008 was reaching End Of Life. In that article, I shared a reasonable use of server side trace to capture all of the logon events to the server. Afterall, you have to find out the source of connections and who is using the server if you need to migrate it to a newer SQL Server version. You can read that article here.
Soon after, from various sources, I received requests on how to perform a logon audit using the more preferred, robust, venerable, awesome tool called Extended Events (XEvents). In response, I would share a login audit session to each person. In this article, I will share my login audit solution and give a brief explanation. I use a solution like this on more than 90% of my client servers and I find it highly useful.
Click through to see how.
Comments closedMarc Lelijveld explains why you might not want to let users export to Excel:
Export to Excel is a feature in Excel which is available in Power BI for a very long time. It allows report users to export the data from a specific visual in the report to an editable Excel file. After exporting, they can do whatever they want. For example, sending the data to others via mail, transforming or manipulating the data, start building new reports based on the Excel file and many other things. The export option can be used by clicking the ellipsis on the right top of a visual (if the visual header is enabled).
If you have all export functionalities enabled, users can both export underlying data and summarized data. The difference is mainly raw data or only data as visible in the chart where you clicked the export button.
Read on to understand why this might not be an unalloyed good.
Comments closedDaniel Janik takes us through a change in SQL Server 2019:
Some time back I wrote about the new data classification features in Azure and SQL Server Management Studio. If you’ve done quite a bit of work classifying your data using the extended properties, guess what?
That’s right. It’s changed in SQL Server 2019 and for the better!
Read on to see how this has become simpler to do.
Comments closed