Permissions for OleDb to an As400

I have a store procedure written in VB.NET to access an AS400 using System.Data.OleDb. I created the key for the procedure as a .pfx file and before adding the code to access the AS400, I tested it against a SQL Server database and it worked fine. When I added the code to access the AS400, I get the following error:

A .NET Framework error occurred during execution of user defined routine or aggregate 'ap_mapics_Data':

System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.OleDb.OleDbConnection.PermissionDemand()

at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.OleDb.OleDbConnection.Open()

at ProImage_Procedures_Test.StoredProcedures.ap_mapics_Data()

OleDbPermission is only available to assemblies deployed with an UNSAFE CAS permission set grant. Given that your code worked against what is presumably another SQL Server database, I'm guessing that your assembly is deployed with an EXTERNAL_ACCESS grant. If you want more information about the permissions granted at each level, see http://bordecal.mvps.org/Nicole/SqlClrCas/SqlClrCasSpeculations.htm.

Permissions

Hi:
Actually I have a store proc which pulls data from a table. I have given
execute permission to a user on this store proc. Since owner of store proc
and table are the same (dbo), I do not need to give select permission on the
table for this user.
But i am having problem when I use dynamic sql to build a query string and
execute this sql. It then gives an error that permission denied on this
table for this user. I think It starts a new session when you build a
querystring (Select * from Table), it checks for permission on that table
because the ownership chain breaks.
If you know of any workaround to this problem, please let me know becuase I
do not want to give select permission for this user. It should be executed
thru the stored proc.
ThanksYou will need to re-write the stored procedure to not use dynamic SQL,
which requires the user to have permissions on the base tables.
--Mary
On Thu, 12 Aug 2004 14:09:03 -0700, Sal
<Sal@.discussions.microsoft.com> wrote:

>Hi:
>Actually I have a store proc which pulls data from a table. I have given
>execute permission to a user on this store proc. Since owner of store proc
>and table are the same (dbo), I do not need to give select permission on th
e
>table for this user.
>But i am having problem when I use dynamic sql to build a query string and
>execute this sql. It then gives an error that permission denied on this
>table for this user. I think It starts a new session when you build a
>querystring (Select * from Table), it checks for permission on that table
>because the ownership chain breaks.
>If you know of any workaround to this problem, please let me know becuase I
>do not want to give select permission for this user. It should be executed
>thru the stored proc.
>Thanks|||Sal,
There is one work-around I have found. If you are trying to insert
data into a permanent table via dynamic sql, first create a temporary
table with the same structure as the destination permenant table.
Insert into the temporary table via the dynamic statement.
Then, with non-dynamic sql, insert from the temp table into the
permanent table.
Hope this helps.
Sal wrote:
> *Hi:
> Actually I have a store proc which pulls data from a table. I have
> given
> execute permission to a user on this store proc. Since owner of
> store proc
> and table are the same (dbo), I do not need to give select permission
> on the
> table for this user.
> But i am having problem when I use dynamic sql to build a query
> string and
> execute this sql. It then gives an error that permission denied on
> this
> table for this user. I think It starts a new session when you build
> a
> querystring (Select * from Table), it checks for permission on that
> table
> because the ownership chain breaks.
> If you know of any workaround to this problem, please let me know
> becuase I
> do not want to give select permission for this user. It should be
> executed
> thru the stored proc.
> Thanks *
lxstewart
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message952424.html