Security in SQL Server 2005 – unsafe assemblies in Sept CTP

After writing about a lot of new security features that were added since we published our "First Look at SQL Server 2005 book" its nice to report on one that we had in there (at beta2), but never appeared in the product. Until now.

In the September CTP version on SQL Server 2005 (I think its probably the last CTP), you need special permissions to CREATE an ASSEMBLY with UNSAFE permission set. You must have either one of the following:

1. DBO has UNSAFE ASSEMBLY permission and database has TRUSTWORTHY property on.
or
2. ASSEMBLY is signed with an asymmetric key or cert that has a LOGIN with UNSAFE ASSEMBLY permission.

We'd written about the second choice. Here's one of the combinations that works:

1. Create a strong named key in c:\temp\assm.snk
2. Sign the assembly unsafe1.dll with this strong named key
3. Make a SQL Server LOGIN for the key.
4. Give LOGIN the appropriate permissions
5. Catalog the unsafe assembly

In code, it looks like this:

— master key in master database
USE master
go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword1'
go

— keyfile generated by VS or .NET command line utilities
CREATE ASYMMETRIC KEY assm FROM FILE='c:\temp\assm.snk'
go

CREATE LOGIN snk FROM ASYMMETRIC KEY assm
go

GRANT UNSAFE ASSEMBLY TO snk
GO

USE somedb
GO

CREATE ASSEMBLY unsafeassemblyex FROM 'c:\temp\unsafe1.dll'
  WITH permission_set = unsafe
GO

That's only one variation of it. You can also use the key stored in the assembly (CREATE ASYMMETRIC KEY FROM EXECUTABLE FILE=…) or an assembly already cataloged inside the database (CREATE ASYMMETRIC KEY FROM ASSEMBLY…). You can do the same thing with certificates.

So one of the SQLCLR security features we wrote about over a year ago has come to pass.

3 thoughts on “Security in SQL Server 2005 – unsafe assemblies in Sept CTP”

  1. CREATE LOGIN snk FROM ASYMMETRIC KEY assm

    Just curious – is this on par with a normal SQL login? It almost looks as though you’re giving the *code* its own personal login based on the publisher’s key.

    If you added a role to this login, would that role be bestowed to any assembly (hosted in SQL) signed with this key, in addition to whatever other rights are granted based on the *user’s* login?

    Curious minds want to know! 🙂

  2. I haven’t seen any good step by step documentation on how to do in a windows integrated security environment. Is there any more documentation available from MS as of yet?

  3. Hi Lyle,

    You can define non-Windows logins (like the one for the asymmetric key) in Windows integrated only environment. They just don’t work for logging in to the database. Or is that not what you’re asking?

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course