database access with custom assembly

Hi,
I got a problem accessing the database from within the deployed report.
The report works perfectly well within my IDE (VS 2005 v8.0.5x) but when I
deploy it to the Report Server (SQL Server 2005, v 9.00.1399) no access to
the database can be established; the field containg the value shows '#ERROR'.
I adjusted the following things:
1) C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\rssrvpolicy.config
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="RSHelperCodeGroup"
Description="Code group for InfoFrame RS help functions">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\RSHelpFunction.dll" />
</CodeGroup>
This entry follows the following entry:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*" />
</CodeGroup>
2) function within dll:
public static string getData (string id)
{
private static string connstring = "Data Source=localhost;Network
Library=DBMSSOCN;Initial Catalog=TPP;User ID=userID;Password=password;";
SqlConnection connection = new SqlConnection(connstring);
string resultString = "";
try
{
connection.Open();
string select = "SELECT Text FROM tblXY where tblXY.ID = " + id;
SqlCommand comm = new SqlCommand(select, connection);
SqlDataReader reader = comm.ExecuteReader();
bool r = reader.Read();
//...
resultString = (string)reader.GetSqlString(0);
//some checks following
...
}
finally
{
if (connection != null)
connection.Close();
}
return resultString;
}
3) Assembly
I compiled the assembly (dll) with the following attribute:
[assembly: AllowPartiallyTrustedCallers]
4) Path for the dll
I copied the dll into the following folders:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\RSHelpFunction.dll
and
C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\RSHelpFunction.dll
It seems to me that this is a problem concerning the permission required for
executing the report on the server.
Any help is appreciated.
Thanks in advance.
HaraldHi,
the solution to my problem is, as I suspected, a security point.
I had to give special rights to the database access before opening the
connection to the database within the dll:
try {
SqlClientPermission perm = new
SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
perm.Assert();
connection.Open();
...
} ...
The rest of my coding and adjustments (i.e. CodeGroup,...) were ok.
Then everthing works fine even for the deployed report.
Regards
Harald
"Harald" wrote:
> Hi,
> I got a problem accessing the database from within the deployed report.
> The report works perfectly well within my IDE (VS 2005 v8.0.5x) but when I
> deploy it to the Report Server (SQL Server 2005, v 9.00.1399) no access to
> the database can be established; the field containg the value shows '#ERROR'.
> I adjusted the following things:
> 1) C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\rssrvpolicy.config
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="RSHelperCodeGroup"
> Description="Code group for InfoFrame RS help functions">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin\RSHelpFunction.dll" />
> </CodeGroup>
> This entry follows the following entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="$CodeGen$/*" />
> </CodeGroup>
> 2) function within dll:
> public static string getData (string id)
> {
> private static string connstring = "Data Source=localhost;Network
> Library=DBMSSOCN;Initial Catalog=TPP;User ID=userID;Password=password;";
> SqlConnection connection = new SqlConnection(connstring);
> string resultString = "";
> try
> {
> connection.Open();
> string select = "SELECT Text FROM tblXY where tblXY.ID = " + id;
> SqlCommand comm = new SqlCommand(select, connection);
> SqlDataReader reader = comm.ExecuteReader();
> bool r = reader.Read();
> //...
> resultString = (string)reader.GetSqlString(0);
> //some checks following
> ...
> }
> finally
> {
> if (connection != null)
> connection.Close();
> }
> return resultString;
> }
> 3) Assembly
> I compiled the assembly (dll) with the following attribute:
> [assembly: AllowPartiallyTrustedCallers]
> 4) Path for the dll
> I copied the dll into the following folders:
> C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin\RSHelpFunction.dll
> and
> C:\Program Files\Microsoft Visual Studio
> 8\Common7\IDE\PrivateAssemblies\RSHelpFunction.dll
> It seems to me that this is a problem concerning the permission required for
> executing the report on the server.
> Any help is appreciated.
> Thanks in advance.
> Harald