A Fine Slice Of SQL Server
Chris Bell has announced a free e-mail course for learning the basics of SQL Server security:
Today I am very excited to announce that I have (finally!) launched my email course covering the basics of SQL Server Security.
This has been a lot of work to get a new system in place to make the learning experience a little different. It is like a normal email course, but at the same time it isn’t.
I have been waiting for this for months ever since hearing Chris first talk about it.
Comments closedJoey D’Antoni is shaking his head about a CLR announcement:
With this is mind, Microsoft has made some big changes to CLR in SQL Server 2017. SQL CLR has always been an interesting area of the engine—it allows for the use of .NET code in stored procedures and user defined types. For certain tasks , it’s an extremely powerful tool—things like RegEx and geo functions can be much faster in native CLR than trying to do the equivalent operation in T-SQL. It’s always been a little bit of a security risk, since under certain configurations, CLR had access to resources outside of the context of the database engine. This was protected by boundaries defined in the CLR host policy. We had SAFE, EXTERNAL_ACCESS, and UNSAFE levels that we could set. SAFE simply limited access of the assembly to internal computation and local data access. For the purposes of this post, we will skip UNSAFE and EXTERNAL_ACCESS, but it is sufficed to say, these levels allow much deeper access to the rest of the server.
Code Access Security in .NET (which is used to managed these levels) has been marked obsolete. What does this mean? The boundaries that are marked SAFE, may not be guaranteed to provide security. So “SAFE” CLR may be able to access external resources, call unmanaged code, and acquire sysadmin privileges. This is really bad.
It’s not the end of the world for CLR, but this is a breaking change. Read on for more details.
Comments closedWe can try it out with a very familiar domain:
(converted <- to_homoglyph("google.com")) ## [1] "ƍ၀໐|.com"Now, that’s using all possible homoglyphs and it might not look like
google.comto you, but imagine whittling down the list to ones that are really close to Latin character set matches. Or, imagine you’re in a hurry and see that version of Google’s URL with a shiny, green lock icon from Let’s Encrypt. You might not really give it a second thought if the page looked fine (or were on a mobile browser without a location bar showing).
Click through for more details, as well as information on punycode.
Comments closedSteve Hughes has some resources on implementing row-level security in Power BI:
Row level security is the ability to filter content based on a users role. There are two primary ways to implement row level security in Power BI – through Power BI or using SSAS. Power BI has the ability in the desktop to create roles based on DAX filters which affect what users see in the various assets in Power BI.
In order for this to work, you will need to deploy to a Workspace where users only have read permissions. If the members of the group associated to the Workspace have edit permissions, row level security in Power BI will be ignored.
Read on for more details as well as a set of how-to links.
Comments closedSteve Hughes describes how Power BI data classification works:
Power BI Privacy Levels “specify an isolation level that defines the degree that one data source will be isolated from other data sources”. After working through some testing scenarios and trying to discover the real impact to data security, I was unable to effectively show how this might have any bearing on data security in Power BI. During one test was I shown a warning about using data from a website with data I had marked Organizational and Private. In all cases, I was able to merge the data in the query and in the relationships with no warning or filtering. All of the documentation makes the same statement and most bloggers are restating what is found in the Power BI documentation as were not helpful. My takeaway after reviewing this for a significant amount of time is to not consider these settings when evaluating data security in Power BI. I welcome comments or additional references which actually demonstrate how this isolation actually works in practice. In most cases, we are using organizational data within our Power BI solutions and will not be impacted by this setting and my find improved performance when disabling it.
As Steve notes, this is not really a security feature. Instead, it’s intended to be more a warning to users about which data is confidential and which is publicly-sharable .
Comments closedSteve Hughes shows how to set up a data gateway for Power BI:
First, I will not be discussing the personal gateway in this post. If you have chosen to use the personal gateway, you have limited functionality and should consider using the on-premises data gateway for corporate use.
The on-premises data gateway (referred to as gateway throughout this post) “acts as a bridge, providing quick and secure data transfer between on-premises data and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services.” (ref) Much of what is discussed here will apply to all of the services referenced above, but our primary concern is related to Power BI. Please refer to references at the end of this post for details about data sources supported within the gateway.
Click through for more information.
Comments closedSteve Hughes is starting a series on Power BI security:
Another way to compartmentalize or secure data is using Workspaces within Power BI. Every user, including free users, have access to My Workspace which is the default location for deploying Power BI and other BI assets. However, you also have the option to create additional workspaces as deployment targets. These Group Workspaces usually have functional and security separation associated with them.
This post is a good overview of methods available for data sharing.
Comments closedRob Sewell shows how to use the Export-SqlUser cmdlet in dbatools:
There are times when DBA’s are required to export database user permissions to a file. This may be for a number of reasons. Maybe for DR purposes, for auditing, for transfer to another database or instance. Sometimes we need to create a new user with the same permissions as another user or perhaps nearly the same permissions. I was having a conversation with my good friend and MVP Cláudio Silva and we were talking about how Export-SqlUser from dbatools could help in these situations and he suggested that I blogged about it so here it is.
The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present
Cláudio wrote Export-SqlUser to solve a problem.
Read on to see what this does and how to use it.
Comments closedIn this post you build encryption and decryption into sample Kinesis producer and consumer applications using the Amazon Kinesis Producer Library (KPL), the Amazon Kinesis Consumer Library (KCL), AWS KMS, and the aws-encryption-sdk. The methods and the techniques used in this post to encrypt and decrypt Kinesis records can be easily replicated into your architecture. Some constraints:
AWS charges for the use of KMS API requests for encryption and decryption, for more information see AWS KMS Pricing.
You cannot use Amazon Kinesis Analytics to query Amazon Kinesis Streams with records encrypted by clients in this sample application.
If your application requires low latency processing, note that there will be a slight hit in latency.
Check it out, especially if you’re thinking about streaming sensitive data.
Comments closedJana Sattainathan explains why the default login failure message is so unhelpful:
At first glance, it appears to be utterly useless. The only useful message seems to be “Login failed for user ‘myuser‘.” but what we really need to know is “why?”.
That information is somewhat buried in this section of the message:
Server Name: mysqlserverinstanceError Number: 18456Severity: 14State: 1Based on the documentation for this error, the severity and state actually mean something. I have copied and pasted the same from the link.
Read on to find out how to get the actual error.
Comments closed