Problems after upgrading SQL Server 2005 Server Project

Question

Hi folks,

I just upgraded a DataDude Server Project to the new Juneau project [have I got the code names right? :)], as well as a SQL CLR project.

In this server project, there was a reference to the SQL CLR project, an Asymmetric Key created from the CLR assembly, a Login from the Asymmetric key, and two permissions for the Login.

After the upgrade, the CLR reference was gone, so I just combined the SQL CLR project with the old Server project.

Creating the Asymmetric key from the upgraded project does not work however. I get the following build error:

SQL71501: Asymmetric Key: [My_Key] has an unresolved reference to Assembly [My.Assembly]

I have verified that the output assembly name for the upgraded project is My.Assembly.

Also, the two permissions that must be created for the Login fail to build:

SQL70018: Server level permissions are not allowed

The permissions required are:

GRANT EXTERNAL ACCESS ASSEMBLY TO [My_Login];
GO
GRANT UNSAFE ASSEMBLY TO [My_Login];
GO

Any ideas on how to work around this issue?

By the way, for some reason the SQLCLR tab in the project properties window does not allow you to have periods (.) in the Assembly name or Default namespace, which is a bug. Editing the .sqlproj file directly does bypass the validation error in the project properties window, however.

Answer 1

Hi, I will log the issue for not being able to enter periods in the SqlClr property page.

 

In order to be able to reference the assembly, make sure that the "Generate DDL" option is checked (it's at the bottom of the SqlClr property page).  And then build your project, the DDL generation is done at build time.

 

Unfortunately, Juneau doesn't support server level permissions, so you would need to move them to a post deployment script.

Thanks,

Gokhan

Answer 2

Hi Gokhan,

I looked at the SqlClr property page and the "Generate DDL" was indeed checked. While playing around with the upgraded project, I found out what the problem was. This project was an upgraded SQL 2005 Server Project, so by default there is no CLR stuff in it. The References node in Solution Explorer for the project was also empty. I added a new SQL CLR C# Stored Procedure, and that went and added references to System, System.Data and System.Xml. I then deleted the CLR stored proc that I just added and the References remained. Now there is a CLR assembly being generated and there is no more error about an unresolved reference.

In case you're wondering why I generate an empty CLR assembly, it is because I create the asymmetric key out of it (which includes the digital signature).

I moved the permissions to the post deploy script and now everything builds.

Thanks for the help!

Answer 3

I converted a dbproj, the conversion de-xml-ified the .sqlpermissions file into a permissions.sql.  I moved that convert-generated file into the post-deployment-files area, but still get the "server level permissions are not allowed".

When you say "move the to a post deployment script" (which i thought i had done by virtue of relocation) - what exactly do you mean ?

---

KevinB