Hi,
My question kind of covers a couple of areas.
I have written some SQL 05 CLR code that produces a single DLL file. I have specified that the code is 'Safe' e.g. it will not be carrying out any external assembly access or other file accesss.
The code simply contains some stored procedures that will update/instert etc data from the database.
The question is, how do I actually deploy the code to a a remote server Do i simply take the DLL and use CREATE ASSEMBLY code to set it up
Furthermore, are there any security issues i need to consider when i deploy the assembly given that I have already set up the security to be 'Safe' e.g. is there anyway that a database can be locked down so that CLR code would not run irrespective of the safe setting.
As far as I understand it, SQL CLR code is ran under the SQL Server service account, so does this mean that if this user is locked down the code would not run
Any clarifications or extra info would be greatly appreciated.
Peds
Security levels of CLR code when deployed to another server.
Arturo_RX7
It's not possible to add any given permission to any of the SQL Server CAS permission levels. This means that you're left with the option of deploying to a higher level than you would like and rejecting any other permissions. To do this, simply add the following attribute to your assembly (usually in the AssemblyInfo.cs or AssemblyInfo.vb file):
[assembly: PermissionSet(SecurityAction.RequestOptional, Unrestricted = false)]
<Assembly: PermissionSet(SecurityAction.RequestOptional, Unrestricted:=False)>
The presence of any single RequestOptional will result in all other permissions being implicitly refused. Because of this, with the above attribute alone, your assembly will be granted only SecurityPermission\Execution and any identity permissions that correspond to its evidence. You'll need to add RequestMinimum or RequestOptional attributes for any other permissions that your assembly will need. They will be granted at runtime as long as CAS policy (including the SQL Server host policy) allows your assembly these permissions.
Marty01
In a production environment you probably want to create a script that does the CREATE ASSEMBLY and in the script you create the assembly from a byte stream instead of file location (that way you do not have to have file permissions for the location of the assembly).
Notice that the security is set during create assembly, so theoretically someone could change the permission set when the assembly is deployed. Apart from that, you need to be aware that the CLR is not enabled by default, so you could deploy the assembly, create the functions/procs but still fail when you try to execute the methods, because the CLR has not been enabled.
mssqlatoz
In addition to what Niels has already mentioned, you can constrain the permission grant within your assembly in order to avoid elevation of its permissions even if it is deployed at either the EXTERNAL_ACCESS or UNSAFE level.
To specify that your assembly only be granted execution permission and its identity permissions, add the following assembly-level permission attribute:
[assembly: PermissionSet(SecurityAction.RequestOptional, Unrestricted = false)]
If you also need to use the context connection, add the following attribute as well:
[assembly: SqlClientPermission(SecurityAction.RequestMinimum,
ConnectionString = "context connection=true",
KeyRestrictions = "Type System Version=;",
KeyRestrictionBehavior = KeyRestrictionBehavior.AllowOnly)]
GregA
Can you elaborate more on the permissions grant / revoke for a deployed assembly I have written a simple wrapper for the DataProtection Api (DpApi). However, when I deploy to my sqlserver 2005 I must do so with the UNSAFE level to get it to work. Otw, I get an exception stating that the assem does not have the DataProtect Permission. Is there a way to deploy as SAFE and then to GRANT the DataProtect Permission If so how And grant to what The assem The function A role or user
Or is the model as implied by your answer: deploy with UNSAFE and then have the assem explicitly reject permissions it doesn't need I don't know all the permissions or the best way to enumerate them. Would this be done with a permission set, or... I've spent 2 days trying to research this... your help would be greatly appreciated, thanks! And yes, I'm fishing for an explicit example. Thanks again.