Can''t deploy "unsafe assembly", permission problem(?)

Hello,

I think I have some kind of permission problem. But first things first:

I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of

"

CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.

"

Of course I can not create "asymmetric key" for third-party-dll (Or can I?).

So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"

I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".

Login has server roles "sysadmin" and "securityadmin".

Login is mapped with DB User who has same name ( DOMAIN\UserName ) and has default schema "dbo".

Login has DB memberships "db_owner" and "db_securityadmin".

DB user owns schemas "db_owner" and "db_securityadmin".

Am I missing something?

Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.

Questions are:

- Is there other way to authorise third-party-dll than using trustworthy?

- Why deployment can be done in master-database?

And finally:

- Why deployment can not be done in other database?

So when you are deploying an assembly with the UNSAFE permission set (or EXTERNAL_ACCESS) and are using the TRUSTWORTHY settings (i.e. not assymetric keys etc), the login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY.

Niels
|||

Hello Niels,

" login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY."

As far as I know, permission _should_ be OK.

I checked following:

1. database -> properties (the database I want unsafe assembly to be created): owner is <DOMAIN\username>.

2. corresponding database user -> properties: Login name is same.

I have done "Grant UNSAFE ASSEMBLY to <DOMAIN\username>;" and "ALTER DATABASE <database> SET trustworthy on". Both are executed without errors.

<DOMAIN\username> is the Login name of owner of <database> and <database> is the db I want my assembly to be created.

Is there something still missing?

What I should check next?

- Jorma

|||Hmm, what is the exact error message you are getting? Is this database a new database, or is it restored from (perhaps) SQL 2000? What happens if you were to create a new database (with DOMAIN\username as owner), setting the database to TRUSTWORTHY and deply the assembly?

Niels
|||

Hello again,

Error message is:

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'MillCC' failed because assembly 'USP.Express.Pro.2.0' is not authorized for PERMISSION_SET = UNSAFE.

"USP.Express.Pro.2.0" is the third-party-dll my code is using. Previously message were same but assembly named in the message was my code... Interesting.

I created new database as you suggested. And assembly was created without errors. Why in the world I didn't try this before... So, problem is somehow related with the database I am using.

The database is restored from SQL Server 2005 Express Edition.

Is there something I should know when using restored database and CLR?

|||Ah, it is restored. Try this for me please:
1. Run ALTER AUTHORIZATION against the database and change it to [Domain\username] (I know that you said that was the owner, but do it once again, please.
2. Run ALTER DATABASE and reset TRUSTWORTHY to on
3. Try and deploy

Niels
|||

I tried

Alter authorization on DATABASE::<database> to <[DOMAIN\username]>

but there came out following error:

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Should I remove something before running ALTER AUTHORIZATION? I am quite confused about the permissions at this stage |||OK, drop the user in the database, which is the user for the login [Domain\username], then run the ALTER AUTHORIZATION again.

Alternatively, you can create a SQL login (just a login) and make that login the owner of the database and assign the login the necessary permissions.

Niels
|||

OK,

first I tried to drop my user but there came out errors because user owns schemas etc.

I was impatient to test this, so I created new login and made login owner of the database. After givin permissions to the login I was able to create assembly.

In other words, now it is working! Thank you very much Niels!

Can you explain to me what was wrong with the "original" owner? I quess there happens "something" when database is restored...

Can''t deploy "unsafe assembly", permission problem(?)

Hello,

I think I have some kind of permission problem. But first things first:

I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of

"

CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.

"

Of course I can not create "asymmetric key" for third-party-dll (Or can I?).

So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"

I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".

Login has server roles "sysadmin" and "securityadmin".

Login is mapped with DB User who has same name ( DOMAIN\UserName ) and has default schema "dbo".

Login has DB memberships "db_owner" and "db_securityadmin".

DB user owns schemas "db_owner" and "db_securityadmin".

Am I missing something?

Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.

Questions are:

- Is there other way to authorise third-party-dll than using trustworthy?

- Why deployment can be done in master-database?

And finally:

- Why deployment can not be done in other database?

So when you are deploying an assembly with the UNSAFE permission set (or EXTERNAL_ACCESS) and are using the TRUSTWORTHY settings (i.e. not assymetric keys etc), the login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY.

Niels
|||

Hello Niels,

" login of the owner of the database has to have the relevant permissions. In your case UNSAFE ASSEMBLY."

As far as I know, permission _should_ be OK.

I checked following:

1. database -> properties (the database I want unsafe assembly to be created): owner is <DOMAIN\username>.

2. corresponding database user -> properties: Login name is same.

I have done "Grant UNSAFE ASSEMBLY to <DOMAIN\username>;" and "ALTER DATABASE <database> SET trustworthy on". Both are executed without errors.

<DOMAIN\username> is the Login name of owner of <database> and <database> is the db I want my assembly to be created.

Is there something still missing?

What I should check next?

- Jorma

|||Hmm, what is the exact error message you are getting? Is this database a new database, or is it restored from (perhaps) SQL 2000? What happens if you were to create a new database (with DOMAIN\username as owner), setting the database to TRUSTWORTHY and deply the assembly?

Niels
|||

Hello again,

Error message is:

Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'MillCC' failed because assembly 'USP.Express.Pro.2.0' is not authorized for PERMISSION_SET = UNSAFE.

"USP.Express.Pro.2.0" is the third-party-dll my code is using. Previously message were same but assembly named in the message was my code... Interesting.

I created new database as you suggested. And assembly was created without errors. Why in the world I didn't try this before... So, problem is somehow related with the database I am using.

The database is restored from SQL Server 2005 Express Edition.

Is there something I should know when using restored database and CLR?

|||Ah, it is restored. Try this for me please:
1. Run ALTER AUTHORIZATION against the database and change it to [Domain\username] (I know that you said that was the owner, but do it once again, please.
2. Run ALTER DATABASE and reset TRUSTWORTHY to on
3. Try and deploy

Niels
|||

I tried

Alter authorization on DATABASE::<database> to <[DOMAIN\username]>

but there came out following error:

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Should I remove something before running ALTER AUTHORIZATION? I am quite confused about the permissions at this stage |||OK, drop the user in the database, which is the user for the login [Domain\username], then run the ALTER AUTHORIZATION again.

Alternatively, you can create a SQL login (just a login) and make that login the owner of the database and assign the login the necessary permissions.

Niels
|||

OK,

first I tried to drop my user but there came out errors because user owns schemas etc.

I was impatient to test this, so I created new login and made login owner of the database. After givin permissions to the login I was able to create assembly.

In other words, now it is working! Thank you very much Niels!

Can you explain to me what was wrong with the "original" owner? I quess there happens "something" when database is restored...

Cant create new database / CREATE DATABASE permission denied in database master (error 262

I am using SQL express and Visual web developer on windows Vista.

When I try to create a new database the following message appears.

CREATE DATABASE permission denied in database master (error 262)

I log on to my computer as an administrator.

Help appreciated

Prontonet

How do you login to sql server? Do you use sql authentication? If so, check the account you login with has appropriate permissions. Or are you using windows authentication?

|||

Can you login to database engine and read data anyway??

You need "CREATE DATABASE" permission to create new database. However, to make things easier, i would suggest you use sys_admin permission over your database.Open sql express and click "security"-->Logins, see if your login name is listed there. Rigth click your user name and select "properties", click "server roles" or "User Mappings" to see which role has been assigned to your account. If you are not a sys_admin/db_owner role, login agin using a dbo account(if you have one) and assign you account a sys_admin/dbo permission.

Hope my suggestion helps

Can't create http endpoint - don't have permission

I am trying to install an application that have following code in SQL Server
installation script
CREATE ENDPOINT RequirementsAuthoring
STATE = STARTED
AS HTTP (
SITE = '*',
PATH = '/sql/RequirementsAuthoring',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP (...
I get following error
The user 'SIATA\Shimon' does not have permission to register endpoint
'RequirementsAuthoring' on the specified URL. Please ensure the URL refers
to a namespace that is reserved for listening by SQL.
I logged as computer admin on Windows XP with IIS installed.
I tried to run
sp_reserve_http_namespace N'http://Siata:80/sql/RequirementsAuthoring'
That runs just fine. But I still get the same error when I run first script.
Any suggestions?
Thanks,
ShimonHello Shimon,
Although you are logged into Windows as an Admin, that doesn't automatically
make you a member of SA role. Can you do this:
use master
go
grant create endpoint to [your-domain\your-account]
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for fast replay.
No, I can't do it I got this
Cannot find the login 'Siata\Shimon', because it does not exist or you do
not have permission.
I don't get this but. When I am using Management Studio I get this ID as
logged in ID.
I am probably the member of BUILDIN/Administrators
Any solution?
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b7c58c89f11cfb03360@.news.microsoft.com...
> Hello Shimon,
> Although you are logged into Windows as an Admin, that doesn't
> automatically make you a member of SA role. Can you do this:
> use master
> go
> grant create endpoint to [your-domain\your-account]
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Well I created the log in and run your script. It worked. I still have the
same error with original script.
How can I confirm that the namespace 'is reserved for listening by SQL.'
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b7c58c89f11cfb03360@.news.microsoft.com...
> Hello Shimon,
> Although you are logged into Windows as an Admin, that doesn't
> automatically make you a member of SA role. Can you do this:
> use master
> go
> grant create endpoint to [your-domain\your-account]
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hello Shimon,
I believe HTTPCFG.EXE can do this, but I don't have it locally to work with.
You can download it from the MS website. See the following URL
http://www.microsoft.com/downloads/...&displaylang=en
(watch for wrapping, of course)
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks.
Will Try
Shimon
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b88e8c89f1e021134b0@.news.microsoft.com...
> Hello Shimon,
> I believe HTTPCFG.EXE can do this, but I don't have it locally to work
> with. You can download it from the MS website. See the following URL
> http://www.microsoft.com/downloads/...&displaylang=en
> (watch for wrapping, of course)
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Yes, I checked the namespace is reserved.
But I still keep getting this message
An error occurred while attempting to register the endpoint
'RequirementsAuthoring'. One or more of the ports specified in the CREATE
ENDPOINT statement may be bound to another process. Attempt the statement
again with a different port or use netstat to find the application currently
using the port and resolve the conflict.
I tried to run netstat but can't find anything listening to port 80 or 1433.
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b88e8c89f1e021134b0@.news.microsoft.com...
> Hello Shimon,
> I believe HTTPCFG.EXE can do this, but I don't have it locally to work
> with. You can download it from the MS website. See the following URL
> http://www.microsoft.com/downloads/...&displaylang=en
> (watch for wrapping, of course)
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>

Can't create http endpoint - don't have permission

I am trying to install an application that have following code in SQL Server
installation script
CREATE ENDPOINT RequirementsAuthoring
STATE = STARTED
AS HTTP (
SITE = '*',
PATH = '/sql/RequirementsAuthoring',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP (...
I get following error
The user 'SIATA\Shimon' does not have permission to register endpoint
'RequirementsAuthoring' on the specified URL. Please ensure the URL refers
to a namespace that is reserved for listening by SQL.
I logged as computer admin on Windows XP with IIS installed.
I tried to run
sp_reserve_http_namespace N'http://Siata:80/sql/RequirementsAuthoring'
That runs just fine. But I still get the same error when I run first script.
Any suggestions?
Thanks,
ShimonHello Shimon,
Although you are logged into Windows as an Admin, that doesn't automatically
make you a member of SA role. Can you do this:
use master
go
grant create endpoint to [your-domain\your-account]
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for fast replay.
No, I can't do it I got this
Cannot find the login 'Siata\Shimon', because it does not exist or you do
not have permission.
I don't get this but. When I am using Management Studio I get this ID as
logged in ID.
I am probably the member of BUILDIN/Administrators
Any solution?
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b7c58c89f11cfb03360@.news.microsoft.com...
> Hello Shimon,
> Although you are logged into Windows as an Admin, that doesn't
> automatically make you a member of SA role. Can you do this:
> use master
> go
> grant create endpoint to [your-domain\your-account]
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Well I created the log in and run your script. It worked. I still have the
same error with original script.
How can I confirm that the namespace 'is reserved for listening by SQL.'
Thanks
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741b7c58c89f11cfb03360@.news.microsoft.com...
> Hello Shimon,
> Although you are logged into Windows as an Admin, that doesn't
> automatically make you a member of SA role. Can you do this:
> use master
> go
> grant create endpoint to [your-domain\your-account]
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>