In the on-premises, boxed-product version of SQL Server, you can build your own CLR assemblies and call them from your T-SQL code.
For years, this feature was missing from Azure SQL DB – Microsoft’s platform-as-a-service database offering – and users voted that they wanted it.
In December 2014, Microsoft brought SQL CLR code to Azure SQL DB.
Today, Microsoft PM Julie Koesmarno tweeted (update Saturday 9AM – the tweet has been deleted, so here’s a screenshot):
Details are still coming in, but in the Reddit AMA for the Azure database teams (going on as we speak), it’s one of the users reports that they got an email that SQL CLR will be shut off in one week due to a security issue.
The cloud: at the end of the day, it’s just someone else’s server, and they can – and will – take tough actions to protect their product, their users, their security, and their profits.
Earlier this week, I was telling my SQLSaturday Israel pre-con class about HA/DR in the cloud, and I call the cloud a giant hamster wheel. Once you commit to getting on that wheel, you can never get off. You constantly have to watch what features the cloud vendor adds/changes/removes, and you have to be willing to jump into development and change your application right freakin’ now in order to compensate for their changes.
Have fun on the hamster wheel today, folks. Time to start coding your workarounds.
I never realized CLR might be a security issue in Azure but it clearly is. In-process security is very hard. The number of vulnerabilities in the CLR is numerous.
There are unresolved JIT problems right now that you can use to read and write arbitrary memory. You can find them through the CoreCLR GitHub commit logs. These security issues apply to on-premises as well. They require very skilled attackers so it’s usually not a realistic attack. Not so in the cloud.
I personally know of a way to corrupt memory using safe code and considered-safe .NET Framework APIs. The .NET Framework has a huge attack surface against code sitting in its memory space.
Many “.NET Security Updates” running through Windows Update are cases where someone could escape from the .NET sandbox using safe code. Very many such cases over the years.
The JVM is even worse. This is why every browser warns you before opening an applet.
Yeah, agreed – this is one of the reasons we don’t do security work for our clients, either. You’ve really gotta specialize in it in order to do it justice. You can’t do security as a part-time hobby – at least, not for long.
Brent, I have to disagree with you here. Unfortunately, there are plenty of folks in the security community treating it as a part-time hobby with regards to their commitment and making good money at it. Unfortunately, we see the results. It’s no different than any other branch of IT in this regard: you’ve got your jaw droppers (he/she can do what?!?), your elite, your rank and file, your newbies, and your vultures (FUD mongers).
With that said, kudos to MS for stepping up and announcing they’re turning off SQLCLR until they can secure it, despite the potential reputation hit from customers.
Hahaha, nicely done, sir.
Julie has seemingly deleted that tweet…
Chris – thanks for the heads up, replaced the link with a screenshot.
I guess it would be okay for the most dba’s / devs, when they would came up with an aggregate function to concate strings.
The FOR-XML-syntax works, but is very awkward to code / write and not very fast (particularly for bigger datasets / multiple concated columns)
I will miss tSQt, well just when unit testing was getting fun for us sql developers, sigh.
Isn’t this now supported? Or is it for SQL 2017 only?
Ron – this is about Azure SQL DB, not SQL Server.
Sorry. My mistake. I was wondering if it ever got added back to Azure.
I know this is an older post but what are the odds removing CLR was related to Meltdown and Spectre mitigations?
The meltdown/spectre guidance article for SQL Server is explicit about avoiding CLR where possible.
Surely Microsoft wasn’t aware of this all the way back in April of last year?
Mike – those are interesting questions! To learn more, Google for things like Meltdown Spectre timeline, and you’ll find posts like this:
Did they ever add this functionality back into Azure SQL Server?