CODEWITHSUNDEEP
sqlsql-serversql-server-2014.net-assemblyassemblies
cdub
cdub

Reputation: 25701

Permissions needed to exceute an assembly in SQL Server 2014

I have assemblies that I cannot execute due to IT setting permissions too low. What do I need to have permissions set at to allow my assemblies to run? Does it have to be CONTROL?

Upvotes: 0

Views: 1938

Answers (1)

clifton_h
clifton_h

Reputation: 1298

As the comments specify, you do not execute any CLR assemblies. These are isolated from the file system and remain like any other type of object in SQL Server.

In fact, this is what makes CLR objects so ingenious. They exists as any other function, Procedure, table, you wish to manipulate.

Now, since you posted on this forum about permissions, please understand from the Database Administrstors perspective that CLR is custom and thus inherently unsafe. It is insafe precisely because at the end of the day, your expertise and depth of knowledge is a limiting factor and is outside of the normal expected operations of SQL Server.

The biggest factor for permissions for CLR depend on if it requires outside resources from SQL Server. from a security standpoint it is UNSAFE, period.

  • Three Factors of CLR security

Note that there are three policies that determine your security levels: MACHINE, USER, and HOST policies.

The security policy that determines the permissions granted to assemblies is defined in three different places:

Machine policy: This is the policy in effect for all managed code running in the machine on which SQL Server is installed.

User policy: This is the policy in effect for managed code hosted by a process. For SQL Server, the user policy is specific to the Windows account on which the SQL Server service is running.

Host policy: This is the policy set up by the host of the CLR (in this case, SQL Server) that is in effect for managed code running in that host.

CLR Integration Code Access Security

These are separate and one of more may be why you are denied the privilege.

  • CLR CAS IS NO LONGER SUPPORTED

CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary.

clr enabled configuration | Microsoft Docs

  • Options available

1) Request access either for your team anyways without making any changes and ignoring any warnings about how security is changing.

2) Request access after determining what risk the CLR actually poises to the system.

3) Change your CLR to operate under the proper level of security while CAS is still supported.

4) Request an alternative where you encapsulate the requirements of this object away from the regular, unqualified user so that the proper account runs this without your regular account to have access.

  • Explanations and likely results

The first alternative is what lazy people do. Don't be lazy. Do some research, learn, and understand from their perspective what you need. Perhaps this is unfeasible anyways.

Second option is basically this: read and learn about what the Docs say so that you can be more persuasive in your need. definitely this is a must for your development team that they read the docs. See end of post for the links.

Third option might not be feasible, but perhaps after step two/talking to the DBA, you might determine the CLR is overpowered anyways. Only problem is that going forward in SQL Server 2017, all CLR code will be considered UNSAFE as Microsoft is pulling support for the usual security. So plan accordingly if this does not pose a blocking problem.

Fourth option means you can get the DBA to use a service account/qualified user that is separate from the LOGIN/USER that actually runs the scripts. The advantage is encapsulation gets you what you want and still maintains the high standards of security you need. The two downsides depend on the CLR and your needs, plus how willing the DBA to help you develop the right code.

Whether you are a seasoned CLR veteran or a newcomer, the Docs are really your best way of understanding what you can or should not ask for.

SOURCES

MUST READ FOR DEVELOPERS

Upvotes: 2

Related Questions