July 2005 - Posts

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.

Recently there has been questions on some of the newsgroups about examples for SQL Server Service Broker. So, yesterday on a flight back to England I crofted up three different SQL Server projects as examples:

  • LocalSample
  • RemoteSampleServer1
  • RemoteSampleServer2

All three examples are jus your very basic “Hello World” example, but they show:

  • LocalSample – communication between two databases on the same SQL server instance. MasterKeys in a database and the database being TrustWorthy
  • RemoteSampleServerX – These two samples are meant to run on two different machines/instances and in the samples you set up both transport security and dialog security by using Certificates.

Download the zip file from here [0], unzip and read the README.txt file, and – Have Fun!!

[0] : http://staff.develop.com/nielsb/code/servicebrokerexamples.zip

Back in May I released the, then, latest version of SSB Admin; an administration tool for SQL Server Service Broker. Since then I have been travelling a lot (Portugal, Spain, India – to mention a few places) and not have had time to do much work with it. However, there has been some development and today I have uploaded a new version of SSB Admin. Some new stuff:

  • Added functionality to create users and logins. This was needed in order to create Certificates and Endpoints (see below).
  • Added functionality to create Service Broker endpoints.
  • Added functionality to create certificates.
  • Changed the behavior when  looking at a service. Previously you’d only see conversations for that service which were in a “conversing” state. It is now changed so you’ll see all conversations (including ended, errors etc.).
  • Added the functionality to be able to filter what conversations to see when looking at a service. You can filter on Source (Target or Initiator) and/or State.

Download it from here and unzip it. Then read the README.doc document.

As always, comments, flames etc. are very welcome.