\nAfter 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.\n<\/p>\n
\nIn 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:\n<\/p>\n
\n1. DBO has UNSAFE ASSEMBLY permission and database has TRUSTWORTHY property on.
\nor
\n2. ASSEMBLY is signed with an asymmetric key or cert that has a LOGIN with UNSAFE ASSEMBLY permission.\n<\/p>\n
\nWe'd written about the second choice. Here's one of the combinations that works:\n<\/p>\n
\n1. Create a strong named key in c:\\temp\\assm.snk
\n2. Sign the assembly unsafe1.dll with this strong named key
\n3. Make a SQL Server LOGIN for the key.
\n4. Give LOGIN the appropriate permissions
\n5. Catalog the unsafe assembly\n<\/p>\n
\nIn code, it looks like this:\n<\/p>\n
\n— master key in master database
\nUSE master
\ngo\n<\/p>\n
\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword1'
\ngo\n<\/p>\n
\n— keyfile generated by VS or .NET command line utilities
\nCREATE ASYMMETRIC KEY assm FROM FILE='c:\\temp\\assm.snk'
\ngo\n<\/p>\n
\nCREATE LOGIN snk FROM ASYMMETRIC KEY assm
\ngo\n<\/p>\n
\nGRANT UNSAFE ASSEMBLY TO snk
\nGO\n<\/p>\n
\nUSE somedb
\nGO\n<\/p>\n
\nCREATE ASSEMBLY unsafeassemblyex FROM 'c:\\temp\\unsafe1.dll'
\n WITH permission_set = unsafe
\nGO\n<\/p>\n
\nThat'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.\n<\/p>\n
\nSo one of the SQLCLR security features we wrote about over a year ago has come to pass.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,28,38],"tags":[],"class_list":["post-908","post","type-post","status-publish","format-standard","hentry","category-security","category-sql-server-2005","category-sqlclr"],"yoast_head":"\n