I wrote a stored procedure in c# that calls a class. It deployed fine but when I try to execute it, I get the error...
Msg 6522, Level 16, State 1, Procedure UpdateJobAdSearch, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UpdateJobAdSearch':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, 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.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at JobAd.WhaleJobAd.CreateSearchableRecord()
at JobAd.WhaleJobAd.CreateSearchableRecord(String JobId)
at StoredProcedures.UpdateJobAdSearch(String JobAdId)
I read an article on MSDN that didn't directly relate, but mentioned the same error message. It suggested using...
System.Data.SqlClient.SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
pSql.Assert();
I tried that, but I get the same error.
I finally found the answer to this. I needed to set the projects properties > database permission level to external. However, I don't understand why. I've scaled back my code, pulled it out the class so everything executes in the stored procedures method call and all it does it create 2 connections to the local database and pull info from 6 tables and put that info into 1. It's not trying to access anything outside the database, so why does it have to be set to external? In order to get that to work, I had to then alter the database and turn trustworthy on which is scary because of the security holes that opens up.|||
cakewalkr7:
I've scaled back my code, pulled it out the class so everything executes in the stored procedures method call and all it does it create 2 connections to the local database and pull info from 6 tables and put that info into 1.
I think maybe connections opened by the class are considered as access to external resource. Did you try to open a connection in this way?
SqlConnection conn = new SqlConnection("context connection = true");
sql