Error: CREATE ASSEMBLY for assembly

I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard.

I import “System.IO” and have therefore set the permission levels to EXTERNAL_ACCESS.

I am receiving the same error message that many folks have received.

CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.

The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.

My CLR access is “on”

I have tried

1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [Builtin\Administrators].

2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to “My Windows Authentication ID”.

3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON

4) In Visual Studio .NET 2005 Set the permission levels to ‘external’

5) Tried Builtin\Administrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.

I can compile BUT NOT DEPLOY

Any help would be greatly appreciated.

Regards Steve

Who is the owner of the database ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Thanks for the reply.

I am running with Windows Authentication at the present moment , under the ID Administrator1 (running under windows Vista.(still would like to get reporting services to run ) . I did read the article on "Opening up the Administrator ID in Vista and installing SQL Server 2005 under that ID."

Re my current problem....

I managed to get it to deploy. I assigned Administrator1 as an "owner" of the database and gave the ID security administrator rights. I did the same with Builtin/Administrator.

I found something very interesting. I am able to deploy CLR stored precedures from the Visual Studio environment but not CLR functions. (EXTERNAL ACCESS) In order to deply functions I must compile the Dll and create SQL to CREATE the ASSEMBLYfrom within the SQL Server Environment. Any ideas why.?

Once again thanks for the help? Very much appreciated.

Steve

|||

OK Folks this took me about 3 hours to do BUT I have resolved the problem and it WORKS!!!

BTW I am using SQL Server 2005 Standard and Visual Studio.NET Enterprise

First create a Strong key as described in“The secret of strong naming”

DO NOT DODELAY SIGNING !!!!!!

http://www.ondotnet.com/pub/a/dotnet/2003/04/28/strongnaming.html

Sn – k MyKeyFile.snk

FIND OUT WHERE THE SNK FILE WAS COMPILED.

Go into VS2005 .NET and under project / properties in the signing page click the Sign the Assembly check box and browse to find MyKeyFile.snk

The database page

Mark the assembly for external access and leave the owner blank

Now we must go to the article

http://sqljunkies.com/WebLog/ktegels/archive/2006/08/14/safetydancelite.aspx

The text below comes from this article....

A kinder, lighter Safety Dance for the SQLCLR

A few months ago, I published a step-by-step procedure for using certificate to sign an assembly so an external access or unsafe assembly could be run without having to set the hosting database to trustworthy. The major problem with that process was its weight: a lot of steps and some still requirements in terms of certificate management. Turns out that yes, there is an easier way to make it work.

Part one -- preparing the master database

1. As needed, start a series of queries against the MASTER database.

2. Create the target database (meaning execute a CREATE DATABASE query).

3. Code and compile the assembly you want to deploy. Make sure you sign the assembly with a strong name key file. This can be done with a PFX file generated by Visual Studio.

4. Create an asymmetric key from from the compiled assembly using a statement like this: create asymmetric key <key_name> from executable file = '<path_to_dll_file>'

USE Master

GO

create asymmetric key imageskeyFile from executable file = 'C:\YEAR 2007\Images\Images\bin\Debug\Images.dll'

5. Create a login based on that asymmetric key using a statement like this: create login <login_name> from asymmetric key <key_name>

Use Master

Go

create login ImageMaker from asymmetric key imageskeyFile

6. Grant that login the right to create either or both an unsafe or external access assembly (as needed) using: grant unsafe assembly to <login_name>

Use Master

Go

grant EXTERNAL ACCESS assembly to ImageMaker

Part two -- preparing the hosting database

1. As needed, start a series of queries against the desired user database.

2. Create a user in that database mapped to the login created in part, step 5. (e.g. create user <db_user> from login <login_name>)

3. Give that user the right to catalog an assembly, e.g.: grant create assembly to <db_user>

4. Catalog the desired assembly using the now trusted asymmetric key with a statement like: create assembly <assembly_name> authorization <db_user> from '<path_to_dll_file>' with permission_set = unsafe or external access as shown below

SET QUOTED_IDENTIFIER OFF

USE YEAR2007

GO

CREATE ASSEMBLY Images

AUTHORIZATION ImageMaker

FROM "C:\YEAR 2007\Images\Images\bin\Debug\Images.dll"

WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

Error while trying to deploy SSIS Package

Hi,
I am trying to deploy a DTSX package to 32-bit SQL Server 2005 in a 64-bit machine from my installer.
I am getting the following error in the SaveToSQLServer method:
The SaveToSQLServer method has encountered OLE DB Error code 0x80004005 (Communication Link Failure). The SQL statement that was issued has failed.
However I am not facing this problem when I try to deploy the DTSX package to a 32-bit SQL Server 2005 in a 32-bit machine.
Can anyone tell me why this happens and how this could be solved?
Thanks,
Sandhya

It sounds like you're doing this through BIDS now, which is fine but as a debug step will Management Studio even let you import the package in? That will prove you have connectivity to the SSIS service. You'll need port 135 open to the server to your box if you haven't already done so. Another tactic is to create a deployment utility and deploy the entire project. You may already know that one but just in case, here's a video to walk you through that series of steps: http://www.jumpstarttv.com/Media.aspx?vid=28.

Brian

|||

Hi,

I tried importing the packages. I was able to do that successfully. Still when I tried to install, the packages were not gettign deployed. So I reinstalled SQL server 2005 and tried. It worked. But when I try installing my solution repeatedly, the same error occurs. Why does this happen?

Error while trying to deploy SSIS Package

Hi,
I am trying to deploy a DTSX package to 32-bit SQL Server 2005 in a 64-bit machine from my installer.
I am getting the following error in the SaveToSQLServer method:
The SaveToSQLServer method has encountered OLE DB Error code 0x80004005 (Communication Link Failure). The SQL statement that was issued has failed.
However I am not facing this problem when I try to deploy the DTSX package to a 32-bit SQL Server 2005 in a 32-bit machine.
Can anyone tell me why this happens and how this could be solved?
Thanks,
Sandhya

It sounds like you're doing this through BIDS now, which is fine but as a debug step will Management Studio even let you import the package in? That will prove you have connectivity to the SSIS service. You'll need port 135 open to the server to your box if you haven't already done so. Another tactic is to create a deployment utility and deploy the entire project. You may already know that one but just in case, here's a video to walk you through that series of steps: http://www.jumpstarttv.com/Media.aspx?vid=28.

Brian

|||

Hi,

I tried importing the packages. I was able to do that successfully. Still when I tried to install, the packages were not gettign deployed. So I reinstalled SQL server 2005 and tried. It worked. But when I try installing my solution repeatedly, the same error occurs. Why does this happen?

Error while running script on host server

I am trying to deploy my database-driven ASP.NET application to my web server. I have hosting through godaddy.com and they require that you run a script through their SQL admin Query Analyzer to create the database. After I attach the database in the Management Studio, I generate the script, copy and paste it into the QA and get the following error. Any help?

Error -2147217900

Line 18: Incorrect syntax near '('.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Students]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Students](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](20) NULL,
[LastName] [nvarchar](20) NULL,
[PhoneNumber1] [nvarchar](15) NULL,
[PhoneNumber2] [nvarchar](15) NULL,
[Instrument] [nchar](20) NULL,
[Interests] [nvarchar](50) NULL,
[Active] [bit] NULL,
[TimeID] [int] NULL,
[UserID] [nchar](20) NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lessons]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Lessons](
[LessonID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CurrentNotes] [nvarchar](250) NULL,
[FutureNotes] [nvarchar](250) NULL,
[DateTime] [datetime] NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Lessons] PRIMARY KEY CLUSTERED
(
[LessonID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IFNOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =OBJECT_ID(N'[dbo].[FK_Lessons_Students]') AND parent_object_id =OBJECT_ID(N'[dbo].[Lessons]'))
ALTER TABLE [dbo].[Lessons] WITH CHECK ADD CONSTRAINT [FK_Lessons_Students] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Lessons] CHECK CONSTRAINT [FK_Lessons_Students]

That's because you the script is generated for a SQL2005 table in Management Studio, so it contains some new elements in SQL2005 which do not exist in SQL2000. Then when you tried to execute it in Query Analyzer (which uses SQL2000 T-SQL rules), QA failed to parse some new T-SQL syntax (I see it is the WITH block following the CONSTRAINT definition here).

For more information about T-SQL enhancement in SQL2005, you can refer to:

http://msdn.microsoft.com/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp?

BTW, system schemas also have been changed in SQL2005, so if you try to refer to 'sys.objects' as in the script in SQL2000, an 'object not exist' error will be raised. For information, please refer to:

http://msdn2.microsoft.com/en-us/library/ms187997.aspx

Error While Depoloying Report Model Project

Hi,

I am trying to deploy my report model project to remote server which is our QA environment using Visual Studio. In my Project Property I have set the Traget Data source folder and model folder and URL. But I am getting this error any idea?

===================================

A connection could not be made to the report server "ReportServer URL for my remote server"

. (Microsoft Semantic Model Designer)

===================================

Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response. (Microsoft.ReportingServices.SemanticQueryDesign)

Program Location:

at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetItemType(String Item)
at Microsoft.ReportingServices.ModelDesigner.Project.ReportServiceClient.GetItemType(String item)
at Microsoft.ReportingServices.ModelDesigner.Project.ReportServiceClient.CheckAuthorized()
at Microsoft.ReportingServices.ModelDesigner.Project.ModelClientManager.GetCredentials(String url)
at Microsoft.ReportingServices.ModelDesigner.Project.ModelProjectDeployer.PrepareDeploy()

What kind of permission/role do I need to have inorder to deploy report model project on to the server. I am able to deploy same project on my local machine using visual studio.

Thanks!!!

You are not using the right address for the report server or the Report WebService is not working properly, you will have to provide the adress of the Report Server WebService address which is by default http://localhost/ReportServer (on the localhost). browse to this address in your IE to see if the address is working and Reporting Server is responding.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi,

I found the solution actually the issue is how the folders structure.

In our QA environment we have different folders for different projects.

This is how I was trying before:

Target DataSourceFolder: Data Sources

Target ModelsFolder: Models

TargetURL: http://servername/reportserver/CRAS1

Notice the folder name at the end of the URL.(thats the project folder I wanted this Model project to be in)

This is how its now and its working:

Target DataSourceFolder: CRAS1/Data Sources

Target ModelsFolder: CRAS1/Models

TargetURL: http://servername/reportserver

I hope this helps to someone like me who does this kind of stupid mistake.

Thanks

Ashwini

Error while deploying CLR Assembly.

Hi,
I am using Whidbey and Yukon. I am creating a CLR SP in whidbey and trying
to deploy. I am getting the following error,
Error: Failed to initialize the .NET Framework: 0x80004005(Unspecified
error). You need to restart server to access .NET Framework functionality.
Any one any ideas as whatz going wrong?
Sameer D.As both are in beta, there are dozens of reasons you could be having problem
s.
1. The builds of Yukon and Whidbey are incompatible
2. The version of the .NET Framework installed is incompatible with Yukon
3. You have a bad install (possible with beta builds, more possible with CTP
builds)
Without understanding more about what you have installed, I can only venture
a wild guess about the reason it is blowing up.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Sameer Dehalvi" wrote:

> Hi,
> I am using Whidbey and Yukon. I am creating a CLR SP in whidbey and trying
> to deploy. I am getting the following error,
> Error: Failed to initialize the .NET Framework: 0x80004005(Unspecified
> error). You need to restart server to access .NET Framework functionality.
> Any one any ideas as whatz going wrong?
> Sameer D.|||Please move this into the 2005 groups:
http://www.aspfaq.com/sql2005/show.asp?id=1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Sameer Dehalvi" <Sameer Dehalvi@.discussions.microsoft.com> wrote in message
news:C108E25F-A4CE-4532-86BB-A05DA35FD837@.microsoft.com...
> Hi,
> I am using Whidbey and Yukon. I am creating a CLR SP in whidbey and trying
> to deploy. I am getting the following error,
> Error: Failed to initialize the .NET Framework: 0x80004005(Unspecified
> error). You need to restart server to access .NET Framework functionality.
> Any one any ideas as whatz going wrong?
> Sameer D.|||Gregory,
I have a VPC with Whidbey and have installed Yukon on host machine. There is
no way that there may be any compatibility issue, as I was able to build and
deploy the CLR Stored Procs from Whidbey. I tried restarting the host and ma
y
that has caused problem. I tried restarting the Yukon, tried installing
framework 2.0, tried to execute CLR SPs in yukon no luck. However the simple
select statements (T-SQL) execute without any probs. If you have any ideas
suggestions are wel come.
-Sameer.
"Cowboy (Gregory A. Beamer) - MVP" wrote:
> As both are in beta, there are dozens of reasons you could be having probl
ems.
> 1. The builds of Yukon and Whidbey are incompatible
> 2. The version of the .NET Framework installed is incompatible with Yukon
> 3. You have a bad install (possible with beta builds, more possible with C
TP
> builds)
> Without understanding more about what you have installed, I can only ventu
re
> a wild guess about the reason it is blowing up.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "Sameer Dehalvi" wrote:
>