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

Database Access from Assembly

I have an assembly that I call to supply parameters to a report. Within the assembly, I make a connection to SQL server to supply the parameter.
The report runs fine from my local machine, but when I deploy to the report server, I get nothing back.
Is this a permission issue on the DLL, and if so, how do I get around it?
Thanks!
Dave
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Did you read this article and apply proper settings in the .config files of
report server?
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
You will also need to assert security permissions inside the custom
assembly. Every call that requires certain permissions (e.g. opening a
database connection) will fail on the server unless the required security
permissions for that call (documented in MSDN) have been asserted.
Example for opening a connection to a SQL Server:
...
SqlClientPermission permission = new
SqlClientPermission(PermissionState.Unrestricted);
try
{
permission.Assert(); // Assert security permission!
SqlConnection con = new SqlConnection("...");
con.Open();
...
}
You might also want to check out these links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_0so6.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconAssert.asp
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%23C2jfaQXEHA.4000@.TK2MSFTNGP09.phx.gbl...
> I have an assembly that I call to supply parameters to a report. Within
the assembly, I make a connection to SQL server to supply the parameter.
> The report runs fine from my local machine, but when I deploy to the
report server, I get nothing back.
> Is this a permission issue on the DLL, and if so, how do I get around it?
> Thanks!
> Dave
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.