I was working in a bank a few weeks back. They were planning an upgrade from SQL Server 2008 R2 to SQL Server 2017. One of the things that I raised with them is why there were extended stored procedures that had been added to the master database. And they told me they were the result of a penetration test.
OK, so this had me intrigued.
The logic that was being applied was that the penetration testers required the bank to have SQL CLR disabled. No-one in the bank could tell me why; just that it was required to be off.
I could almost have accepted that, even though with SQL Server 2017's whitelisting options for SQL CLR assemblies, I'd really like to see them revisit that decision.
Because the bank wanted to use a 3rd party monitoring and backup tool for SQL Server, they had now chosen the option to use an extended stored procedure based system, instead of a SQL CLR based one.
What???
Sorry, that's just poor advice. Extended stored procedures run directly in the memory space of SQL Server itself. You do not want to be writing code there. Let's get something clear:
The SQL CLR procedures that you write will be safer than any extended stored procedure.
There have been so many stability issues over the years, caused by extended stored procedures. Microsoft's own advice tells you to do the opposite of this. They say:
"This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR Integration instead."
An additional protection that appeared in SQL Server 2017 and later is that Code Access Security (CAS) is no longer used for SQL CLR procedures. Instead, you now should specifically whitelist individual assemblies that you need and trust.
If your penetration tester is happy with you replacing SQL CLR procedures with extended stored procedures, it's time for a new penetration tester.