As I understand it, there are some peculiar differences with CLR Integration and the nature of safe vs unsafe assemblies between older version of SQL Server up to the latest and greatest versions. For example, SQL Server 2017+ has a feature called CLR Strict Security, which my 2014 instance does not. What is the default permission set to for assemblies on SQL Server 2014?
No real differences in how assemblies behave. Yes, SQL Server 2017 introduced both the
CLR strict security server config option, and the related Trusted Assemblies feature: the former merely forcing all assemblies to be signed (even
SAFE assemblies), and the latter allowing assemblies to bypass the new requirement to be signed (without resorting to enabling
TRUSTWORTHY). Both allow for all assemblies to be set to
UNSAFE (whereas previously, or by disabling
CLR strict security, assemblies could be restricted to either
There is potential for the CLR host environment, in which these assemblies operate, to allow for some unsafe code to execute based on the underlying security model changing (I believe in .NET 4.5). However, I'm not aware of this supposed bypass of security ever being proven, and believe these most recent changes (and the reason for them) to be an embarrassing misunderstanding of how CLR works within SQL Server. Either way, this is the only potential difference.
The primary goal is to employ SSIS Catalog and be sure to follow proper procedure to reduce security risk. Are assemblies solely for the deployment of projects to SSIS Catalog marked as safe? Are there any assemblies for SSIS Catalog I have to keep an eye on which will be unsigned and, thus, not necessarily trusted?
While it certainly is commendable to be security conscious, given that you are inquiring about software provided by Microsoft, if you are concerned about the SQLCLR component of SSIS Catalog, are you also concerned about the rest of SSIS, or SQL Server in general? Point being, while there have been some security missteps by Microsoft in the past (e.g. blank/empty
sa password), I think it's safe to give them the benefit of the doubt (in spite of possible misunderstanding leading to
CLR strict security, and definite misunderstanding that lead to Trusted Assemblies). Or, to put it another way, I see no reason to be suspicious of installing SSIS Catalog as compromising security.
But the main thing is, before I make use of SSIS Catalog on a SQL Server 2014 instance, I just want to understand and be aware of any security pitfalls before I embark on it and enable CLR Integration.
The only real security pitfall is if you load assemblies from sources you don't trust and/or allow others to load assemblies without needing to check with you first. By default, a user cannot load assemblies without being:
dbo or in the
db_owner fixed database role, their login being in the
sysadmin fixed server role, or being granted the
CREATE ASSEMBLY permission. So, don't grant that permission to, or place into
sysadmin roles, folks that you don't trust.
In the end, if you are truly concerned about security for this particular application, then there is nothing stopping you from setting up a separate (i.e. isolated) instance of SQL Server to house just SSIS Catalog and nothing else, right?