Solomon Rutzky has started a series on CLR in SQL Server 2017 and lays down a gauntlet:
What all of that means is that, assuming clr strict security is “1” (i.e. enabled), and TRUSTWORTHY is “OFF” for the Database in which an Assembly is being created, then in order to create any Assembly you first need to:
- Sign the Assembly with a strong-name key or a certificate
- Create an Asymmetric Key or Certificate in master from whatever you signed the Assembly with
- Create a Login based on that Asymmetric Key or Certificate
- Grant that Login the UNSAFE ASSEMBLY permission
Is that really so bad? Aren’t many of us (hopefully!) already doing that?
Solomon’s not very happy with the way that CLR security works in 2017, but he does have solutions of his own in mind.
Paul Turley has a video showing how to use row-level security with Power BI:
The best method to implement row-level security in a published Power BI model or SSAS Tabular model consumed from the Power BI service will depend on will depend on your data and requirements. The method I demonstrate here is one of the most flexible approaches and one that I commonly use in my projects.
Click through to watch the video.
Louis Davidson wants to figure out how indexed views work when you have row-level security enabled:
I was giving my Row Level Security session last weekend for the Richmond User Group (@RichmondSQL http://rva.pass.org/) and a question came up (ok, so I technically asked the question). How does the feature work with indexed views?
Generally speaking, the answer turned out to be obvious. You can apply a RLS Security Policy to a view that has an index on it, but not to a table that is referenced by an indexed view.
Read on for Louis’s demonstration.
It is possible to mine data for hidden gems of information by looking at significant patterns of data. Unfortunately, this sometimes means that published datasets can reveal sensitive data when the publisher didn’t intend it, or even when they tried to prevent it by suppressing any part of the data that could enable individuals to be identified
Using creative querying, linking tables in ways that weren’t originally envisaged, as well as using well-known and documented analytical techniques, it’s often possible to infer the values of ‘suppressed’ data from the values provided in other, non-suppressed data. One man’s data mining is another man’s data inference attack.
Read the whole thing. One big problem with trying to anonymize data is that you don’t know how much the attacker knows. Especially with outliers or smaller samples, you might be able to glean interesting information with a series of queries. Even if the application only returns aggregated results for some N, you can often put together a set of queries where you slice the population different ways until you get hidden details on individual. Phil covers these types of inference attacks.
Niels Berglund walks through the process of whitelisting a CLR assembly in SQL Server 2017:
What Microsoft introduces in SQL Server 2017 RC1, is something I refer to as whitelisting. It is somewhat similar to the
TRUSTWORTHYsetting, where you indicate that a database is to be trusted. But instead of doing it on the database level, you do it per assembly.To whitelist in SQL Server 2017 RC1, you use the system stored procedure
sys.sp_add_trusted_assembly. As the name implies the procedure adds an assembly to a list of “trusted” assemblies. By marking an assembly as trusted, SQL Server will allow it to be loaded whenclr strict securityis on (on by default), even if:
the assembly is not signed, and
the database where you want to deploy it to is not
TRUSTWORTHY.
With the elimination of the CAS model finally hitting CLR, this is probably going to be one of the easier ways for DBAs to move forward with CLR in the future.
Brian Carrig shows how to create certificates from binary:
Sometimes it is necessary to copy a certificate from one database to another database. The most common method I have seen to do this is involves taking a backup of the certificate to disk from one database and then restoring the certificate to the other database.
There is however, a lesser known alternative option available, provided you are working with SQL Server 2012 and above. Sadly despite it being 2017, this is not as foregone a conclusion for SQL Server DBAs as it should be. This alternate option is known as CREATE CERTIFICATE FROM BINARY. There are a few caveats with this option. Chief among them is that you cannot use a variable for the binary value, so you will likely end up needing to use some dynamic SQL.
One of the nice aspects to this feature from an administration and a security perspective is that you do not need to worry about accidentally leaving a copy of your certificate on a disk somewhere or having to remember to delete it after you have imported it into your user database.
Read on to see it in action. Also, it’s about time that Brian started blogging.
Microsoft says that turning on TDE (Transparent Data Encryption) for a database will result in a 2-4% performance penalty, which is actually not too bad given the benefits of having your data more secure. There is even more of a performance hit when enabling cell level or column level encryption. When encrypting any of your databases, keep in mind that the tempdb database will also be encrypted. This could have a performance impact on your other non-encrypted databases on the same instance.
In a previous post I demonstrated how to add an encrypted database to an AlwaysOn group in SQL2016. In this article I will demonstrate the performance effects of having an encrypted database in your AlwaysOn Group compared to the same database not-encrypted.
The results aren’t surprising, though the magnitude of the results might be.
Arun Sirpal explains some of the security features exposed in Azure SQL Database:
You do not really have to use all of them, this is down to you and your requirements but at least you have decisions to make. I will mention TDE (Transparent Data Encryption), I know a lot of people will opt for TDE in Azure. The big advantage of TDE in Azure over the earthed flavour is that Microsoft does a lot of the work for you, especially around the key management side of things. Also assuming your database is in a geo-replication partnership it will be protected by a different key on each of the servers. Microsoft will also rotate your certificate at least every 90 days, doing this with a local based SQL Server can be quite manual and fiddly (well I think it is).
Read the whole thing if you’re thinking of moving forward with Azure SQL Database, or if you already have a database up in Azure and haven’t checked the latest offerings yet.
David Fowler has an interesting article on a simple cryptographic algorithm in SQL Server:
As this post was prompted by my post on bitwise logic, we’re going to base our algorithm around the XOR cypher. Basically this cypher works by taking a key which for simplicity sake will be a single byte and XOR-ing that against the message (or plain text) that we want to encrypt.
Let’s look at an example of how this is going to work.
Let’s say that we want to encrypt the plain text ‘SQLUndercover’. How are we going to do this? Firstly we need to remember that all text characters are represented by a single byte as an ASCII code. ‘SQLUndercover’ is represented by the following set of ASCII codes, 83 81 76 85 110 100 101 114 99 111 118 101 114.
I definitely recommend reading this article for two reasons: first, because it’s interesting; second, because it shows how easy it is to break amateur crypto. If you feel the need to roll your own cryptographic algorithm, follow my Official Crypto Flow Chart (patent pending): Do you have a PhD in mathematics with a specialty in cryptanalysis? If not, don’t do roll your own crypto; if so, maybe do it but even then probably not.
Duncan Greaves explains Privacy Impact Assessments as part of the GDPR:
The processes and practices implemented by organisations should reflect the methodology of using a Privacy by Design approach to business systems. Undertaking a PIA/DPIA is not a mandatory part of the GDPR, but in doing so, organisations can show that they are compliant with the Act.
Conducting a PIA is designed to accomplish three main goals:
Ensuring conformance with applicable legal, regulatory, and policy requirements for privacy.
Determining the risks and effects.
Evaluating protections and alternative processes to mitigate potential privacy risks.
Worth reading if you’re based in Europe or do business with European customers.
Kevin Feasel
2017-08-16
Security