2

The major pre-requisite in utilizing SSIS Catalog is enabling CLR Integration. The instance in question is running SQL Server 2014. I'm looking for feedback and resources I can mine which specifically covers best practices and security considerations for enabling CLR Integration on SQL Server 2014.

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?

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?

Secondarily, I may want to push data via API calls from SSIS packages and that, too, would require CLR Integration and doing so gives me pause on how and where assemblies are set to safe/unsafe, trustworthy vs asymmetric key. It sounds like calling an API from a SSIS package would be considered unmanaged code and, thus, would have to be marked with unsafe or external_access and need a cert/key.

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.

Improve this question

2 Answers 2

Reset to default
3

The major pre-requisite in utilizing SSIS Catalog is enabling CLR Integration.

Correct, it's a requirement from that product as they use .net modules.

What is the default permission set to for assemblies on SQL Server 2014?

You tell it when you load the modules. Older versions of SQL Server used CAS. It was all about the level (ex: safe) and what you would allow in the instance. Doesn't mean it couldn't be used or would be stopped, per se, as an administrator could load whatever they wanted in. There's no default permissions in the actual terms of permissions. You can't say this specific assembly can't use DNS resolving in their code, for example.

Are assemblies solely for the deployment of projects to SSIS Catalog marked as safe?

AFAIK, unless it changed recently, no.

Are there any assemblies for SSIS Catalog I have to keep an eye on which will be unsigned and, thus, not necessarily trusted?

Since it's an MS product it should be signed. Signing a module doesn't necessarily mean it's safe.

Secondarily, I may want to push data via API calls from SSIS packages and that, too, would require CLR Integration and doing so gives me pause on how and where assemblies are set to safe/unsafe, trustworthy vs asymmetric key.

Not sure what you mean here. SSIS packages are executed in a different set of executables, whether or not whatever public API SSIS offers hits the database for a specific operation I do not know, nor I think is documented.

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 same pitfalls with any CLR integration. The main issue here is either you choose to use SSIS this way, in which case you have to use what is given in the way intended, or you don't use it at all.

Improve this answer
6
  • Thank you, @SeanGallardy, for your detailed response! True, a signed assembly does not imply it's not susceptible to vulnerabilities. I mean to say are there any unsafe assemblies used by MS, strictly as it relates to using and enabling SSIS Catalog? Might be a good question for MS Support, too.
    – user3621633
    Commented Feb 23, 2023 at 19:33
  • 1
    Yes, the SSIS assemblies are created as unsafe. This doesn't mean they are or are not safe. It means they used modules outside of the safe modules list.
    – Sean Gallardy
    Commented Feb 23, 2023 at 19:36
  • For the API question, I'd be making an outbound REST/JSON API call from the SSIS Package, i.e. Script Task to external system. Not consuming data. Not an inbound call to SQL Server. I'm sending data out to an external system's endpoint via their API library. Keep in mind the instance is SQL Server 2014 and JSON is not available innately. To fulfill this criteria, I'd undoubtedly need to import assemblies and, perhaps, mark them as unsafe or external_access. That's the security risk. I'm not even sure if it's best practice to make use of APIs in SSIS. Seems like a gray area to me.
    – user3621633
    Commented Feb 23, 2023 at 19:47
  • 1
    SSIS Tasks as part of an SSIS Package, run in the SSIS executable, which is not part of SQL Server nor is run in-process in SQL Server. Any modules you make and use shouldn't be loaded inside of SQL Server, in general, for SSIS usage.
    – Sean Gallardy
    Commented Feb 23, 2023 at 19:52
  • 1
    Are they unmanaged? No. Can they be marked as safe/external? No.
    – Sean Gallardy
    Commented Feb 23, 2023 at 19:54
3

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 SAFE or EXTERNAL_ACCESS).

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 db_owner / 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?

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.