Saturday, July 30, 2005 1:32 AM nielsb

SQLCLR and CAS in SQL Server 2005

By now, you who have created .NET assemblies for execution in SQL Server 2005, know about the three permission sets used when cataloguing an assembly:

  • SAFE

So, for example, if you need to access things outside of the database you assign the permission set EXTERNAL_ACCESS to the assembly. But what about when you do not want to allow an assembly to everything that is possible for that particular permission set? You do not want the assembly for example to be able to do File IO, but it should be allowed to do external data access.

For those scenarios you can use CAS policies, exactly as you’d do with a “normal” .NET assembly. What, – I can hear you ask - can you actually edit permissions in the SQLCLR permission sets? No you can not (AFAIK), but you can create your own CAS permission set and apply that to the assembly.

In the .NET 2.0 Configuration Wizard, create a new permission set under the, for example, Machine level, and give that permission set the permissions you want to allow. One necessary permission  is the Security permission; without it your assembly will not load. Having created the permission set, you create a new code group, choose the condition type (a Strong Name condition is good). Finally you assign the permission set to the code group. Re-start SQL Server, execute your code and see how your permission set works inside SQL Server. Oh, one last thing you need to do is at the properties dialog for the code group under the General tab, check the first check-box in the “If the membership condition is met:“ group.

If you change your permission set you need to re-start SQL Server for them to take effect as the permissions are parsed during start-up.

I wish I could say it was due to my brilliance (hah) that I found this out, but this was a thing that came up in class last week (guys – you were brilliant, I had so much fun). We spoke about the three levels of permissions in SQLCLR, and Bob – who taught the module in question – said something about being able to assign CLR permissions to SQLCLR assemblies. Neither me nor the students, really believed him (and he wasn’t sure himself) so we decided to try it out. Most of the hard work was done by one of the students, unfortunately I’ve forgotten his name but if you read this, please leave a comment – praise to who praise is due.


No Comments