code access security

[gerry]

vs2008 sp1
..net 3.5sp1

I am taking a 1st stab at SQLCLR and after years of .net development have
finally run into CAS.

Trying to access Environment.MachineName() generates the following exception
:

Request for the permission of type
'System.Security.Permissions.EnvironmentPermission, mscorlib,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Google turns up lots of hits for this but so far nothing that explains how
to get around it.

So now I am looking for sources on handling these types of security issues.

Gerry

 

[Alberto Poblacion]

vs2008 sp1
.net 3.5sp1

I am taking a 1st stab at SQLCLR and after years of .net development have
finally run into CAS.

Trying to access Environment.MachineName() generates the following
exception :

Request for the permission of type
'System.Security.Permissions.EnvironmentPermission, mscorlib,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Google turns up lots of hits for this but so far nothing that explains how
to get around it.

So now I am looking for sources on handling these types of security
issues.

Basically, Sql Server is creating an AppDomain in which it loads your
code. The permissions that it grants to the AppDomain are restricted by
default, and that's why your code doesn't have the EnvironmentPermission.

The way around it, in this case, is to request more permissions from Sql
Server when you install your assembly. When you execute the CREATE ASSEMBLY
statement to install your assembly into the database, add the "... WITH
PERMISSION_SET = EXTERNAL_ACCESS" clause. This will grant
EnvironmentPermission to this assembly.

 

[Mr. Arnold]

vs2008 sp1
.net 3.5sp1

I am taking a 1st stab at SQLCLR and after years of .net development have
finally run into CAS.

Trying to access Environment.MachineName() generates the following
exception :

Request for the permission of type
'System.Security.Permissions.EnvironmentPermission, mscorlib,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Google turns up lots of hits for this but so far nothing that explains how
to get around it.

So now I am looking for sources on handling these types of security
issues.

You might want to try this attribute in your SQLCLR code.
[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
Unrestricted = true]

But on the other hand, SQLCLR is hosted within the SQL Server Process thread
that's hosting it. And I was told that you can't directly access resources
outside of the hosting SQL Server process within SQL Server that's hosting
the SQLCLR code.

You can use a Classlib project's generated DLL that the SQLCLR code can
consume to access resources outside of the SQL Server processing thread
resources. That DLL has to be in a certain location so that SQL Server and
the SQLCLR code can see it.




__________ Information from ESET NOD32 Antivirus, version of virus signature database 4142 (20090609) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[gerry]

thanks alberto

I had already tried the ACCESS EXTERNAL route - but that gives me a compile
time error :

Error 1 CREATE ASSEMBLY for assembly 'GmsSqlClr' failed because assembly
'GmsSqlClr' 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. GmsSqlClr

The dbo ( me ) does have EXTERNAL ACCESS permissions and the db is set as
TRUSTWORTHY and the assembly is signed but still no sugar.
Although regarding signing, I am not sure about the "corresponding login"
bit - how is a login associated with a key and how do I find out who it is ?
I would assume that login would be me as I am the one created the key.

Since this is to be used in an isolated/controlled envirnment - is there any
way to just shut CAS off altogether ?

Gerry

 

[gerry]

You are right - the attribute didn't help.

I don't see how a classlib can be used - to reference it , the classlib also
has to be an sqlserver project and trying to load the dll dynamically leads
to a security exceptions.

vs2008 sp1
.net 3.5sp1

I am taking a 1st stab at SQLCLR and after years of .net development have
finally run into CAS.

Trying to access Environment.MachineName() generates the following
exception :

Request for the permission of type
'System.Security.Permissions.EnvironmentPermission, mscorlib,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
failed.

Google turns up lots of hits for this but so far nothing that explains
how to get around it.

So now I am looking for sources on handling these types of security
issues.

You might want to try this attribute in your SQLCLR code.
[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert,
Unrestricted = true]

But on the other hand, SQLCLR is hosted within the SQL Server Process
thread that's hosting it. And I was told that you can't directly access
resources outside of the hosting SQL Server process within SQL Server
that's hosting the SQLCLR code.

You can use a Classlib project's generated DLL that the SQLCLR code can
consume to access resources outside of the SQL Server processing thread
resources. That DLL has to be in a certain location so that SQL Server
and the SQLCLR code can see it.




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4142 (20090609) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Alberto Poblacion]

thanks alberto

I had already tried the ACCESS EXTERNAL route - but that gives me a
compile time error :
[...]
The dbo ( me ) does have EXTERNAL ACCESS permissions and the db is set as
TRUSTWORTHY and the assembly is signed but still no sugar.

There must be something else. I just tried it out and it worked.

I created an assembly with this code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString NumberOfProcessors()
{
return Environment.GetEnvironmentVariable("NUMBER_OF_PROCESSORS");
}
};

I deployed it from Visual Studio with the default configuration, which
uses the SAFE permission set. I tried to run "Select
dbo.NumberOfProcessors()", which (as expected) threw the error about the
missing EnvironmentPermission.

I then selected the Properties of the assembly from Sql Server
Management Studio and tried to set the permission_set to EXTERNAL ACCESS. I
got the same error as you did, mentioning that the database needed to be set
to Trustworthy. So I set it up as trustworthy by means of:

alter database AdventureWorks set trustworthy on

And then I retried setting the assembly permissions to external_access.
This time I got a message about the owner of the database being wrong, which
I fixed by means of the following:

alter authorization on database::AdventureWorks to [TheDomain\TheUser]

Then I tried once again to set the assembly permissions to
external_access. This time it worked without complaint.

Next, I executed a "Select dbo.NumberOfProcessors()", and it worked,
correctly returning the number of processors from the corresponding
environment variable.

 

[gerry]

I found an article on creating an asymetric key with an associated login in
sql server , that got things working.

Next step though was trying to call a web service , which seems to require
creating a proxy via wsdl.exe and setting the permissions to UNSAFE ( !!!
WTF !!! )
Now I am back to square one because the steps that I used to get EXTERNAL
working aren't working to get UNSAFE working.

What a &^%$&^%& PITA

thanks alberto

I had already tried the ACCESS EXTERNAL route - but that gives me a
compile time error :
[...]
The dbo ( me ) does have EXTERNAL ACCESS permissions and the db is set as
TRUSTWORTHY and the assembly is signed but still no sugar.

There must be something else. I just tried it out and it worked.

I created an assembly with this code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString NumberOfProcessors()
{
return Environment.GetEnvironmentVariable("NUMBER_OF_PROCESSORS");
}
};

I deployed it from Visual Studio with the default configuration, which
uses the SAFE permission set. I tried to run "Select
dbo.NumberOfProcessors()", which (as expected) threw the error about the
missing EnvironmentPermission.

I then selected the Properties of the assembly from Sql Server
Management Studio and tried to set the permission_set to EXTERNAL ACCESS.
I got the same error as you did, mentioning that the database needed to be
set to Trustworthy. So I set it up as trustworthy by means of:

alter database AdventureWorks set trustworthy on

And then I retried setting the assembly permissions to external_access.
This time I got a message about the owner of the database being wrong,
which I fixed by means of the following:

alter authorization on database::AdventureWorks to [TheDomain\TheUser]

Then I tried once again to set the assembly permissions to
external_access. This time it worked without complaint.

Next, I executed a "Select dbo.NumberOfProcessors()", and it worked,
correctly returning the number of processors from the corresponding
environment variable.

 

[gerry]

after much googling and futszing around I finally got this to work.
I set the permissions to EXTERNAL ( UNSAFE is not required as most google
results state ) and set GenerateSerialization Assembly to ON
Building the project generates the 2 required dll's
All the DB objects ( assemblies , sp, ... ) have to dropped and recreated
manually when changes are made because VS doesn't handle the serialization
assembly.
And of course when debugging you have to ignore the DROP ASSEMBLY deployment
error caused by the assembly dependencies
Other than that - everything is peachy

Now if I can find a way to get the project to handle the dropping/creating
of the serialization assembly this would be a snap.
..

I found an article on creating an asymetric key with an associated login in
sql server , that got things working.

Next step though was trying to call a web service , which seems to require
creating a proxy via wsdl.exe and setting the permissions to UNSAFE ( !!!
WTF !!! )
Now I am back to square one because the steps that I used to get EXTERNAL
working aren't working to get UNSAFE working.

What a &^%$&^%& PITA

thanks alberto

I had already tried the ACCESS EXTERNAL route - but that gives me a
compile time error :
[...]
The dbo ( me ) does have EXTERNAL ACCESS permissions and the db is set
as TRUSTWORTHY and the assembly is signed but still no sugar.

There must be something else. I just tried it out and it worked.

I created an assembly with this code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString NumberOfProcessors()
{
return Environment.GetEnvironmentVariable("NUMBER_OF_PROCESSORS");
}
};

I deployed it from Visual Studio with the default configuration, which
uses the SAFE permission set. I tried to run "Select
dbo.NumberOfProcessors()", which (as expected) threw the error about the
missing EnvironmentPermission.

I then selected the Properties of the assembly from Sql Server
Management Studio and tried to set the permission_set to EXTERNAL ACCESS.
I got the same error as you did, mentioning that the database needed to
be set to Trustworthy. So I set it up as trustworthy by means of:

alter database AdventureWorks set trustworthy on

And then I retried setting the assembly permissions to external_access.
This time I got a message about the owner of the database being wrong,
which I fixed by means of the following:

alter authorization on database::AdventureWorks to [TheDomain\TheUser]

Then I tried once again to set the assembly permissions to
external_access. This time it worked without complaint.

Next, I executed a "Select dbo.NumberOfProcessors()", and it worked,
correctly returning the number of processors from the corresponding
environment variable.