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
Showing posts with label accessing. Show all posts
Showing posts with label accessing. Show all posts
Thursday, March 8, 2012
Database Access across servers or Firewall Issue
Hi All,
I am facing a strange issue with accessing data from SQL server 2000. We are using SQL Server authentication for connecting to SQL Server 2000 and ADO .Net SQL client in the application. The problem here is, we have databases installed in one of the servers in some other office location that is in different state but on same domain. We do development from two locations that is one from Virtual Machines hosted on servers where Database Servers are located and on our local desktops in India.
Now when I developed a test client in Virtual Machines which will call a web service to get datasetof some data and show them in DataGrid. When I run the test client in debug mode from Visual Studio 2003, then I am getting data in the DataGrid. Even when I just run the exe from Virtual Machine still I am getting data.
But when I copy debug folder of the test client to the local desktop in India and when I run the Test client I am not getting any data loaded on to the DataGrid. I am getting an empty dataset when I am running from the local desktop.
Also I have converted the VS2003 project to VS2005 and when I run the application from Local Desktop then I am getting the data loaded fine. I could not understand what is the problem, what is the difference between accessing data from VS2003 and VS2005 using webservice.
Is this a firewall issue or I am missing any thing here. The webservice method I am calling to get data is actually returning dataset when I am calling this method directly from the browser also and even through application in virtual machine.
I am still not able to come to terms with this problem. This is severely affecting our deployment. Please help us resolve this problem. Please let me know if you need more information.
Thanks,
Venkatesh
Hi Venkatesh,
From what I understand, you have 3 components in your application system. There's the SQL Server, some client code, and a web service.
The SQL Server is in the same domain as the Virtual Machines that you are developing in. Your local desktops in India are in the same domain or a different domain than the SQL Server machine?
Since you are using SQL Server user authentication, the cross domain connectivity should not be an issue. You can verify this by running osql.exe or Query Analyzer tool on your local desktop connecting to the SQL Server machine using the same SQL Server user credentials as your client application.
As for the test client not seeing any data from the web service, I think it would help if you can mention how the web service is connecting to SQL Server and how the ADO.Net application is retrieving data from the web service. I can see the web service using ADO.Net (SqlClient) to connect to the backend SQL Server. Then, some front end client application built using the web service's WSDL document.
I think it will help people understand your application setup and provide possible solutions, if you can specify:
What is the web service using to connect to SQL Server (SqlClient)? Is the web service running on the same machine as SQL Server? If the web service is running on a different machine, is there a connectivity issue between the web service and SQL Server? Is the web service connected to SQL Server using SQL User credentials or Integrated authentication? Does the client application require to send user credentials to connect to the web service?|||Sorry for the delay in response,
1. Web Service has Business Logic DLL reference which uses SQLClient ADO .Net commands to connect to database.
2. Web Service and SQL Server are on different servers but on the same domain.
3. There is no connectivity issue between the two machines because when I access same WebService through Web Browser, it works fine.
4. Web Service is connecting to SQL Server using SQL User credentials.
5. The User credentials are configured through WebService's Web.Config file.
I really have no clue about this issue. The issue just lies between the Windows Client and Web Service. Because when I access WebService directly through Web Browser then the Web Method is returning data, that means Web Service is getting the data fine but it is not able to give it to the Client.
FYI this issue occurs only on Windows 2003 Server if I install the same service on Windows 2000 server it works fine.
Anyhelp will be greatly appreciated.
Database Access across servers or Firewall Issue
Hi All,
I am facing a strange issue with accessing data from SQL server 2000. We are using SQL Server authentication for connecting to SQL Server 2000 and ADO .Net SQL client in the application. The problem here is, we have databases installed in one of the servers in some other office location that is in different state but on same domain. We do development from two locations that is one from Virtual Machines hosted on servers where Database Servers are located and on our local desktops in India.
Now when I developed a test client in Virtual Machines which will call a web service to get datasetof some data and show them in DataGrid. When I run the test client in debug mode from Visual Studio 2003, then I am getting data in the DataGrid. Even when I just run the exe from Virtual Machine still I am getting data.
But when I copy debug folder of the test client to the local desktop in India and when I run the Test client I am not getting any data loaded on to the DataGrid. I am getting an empty dataset when I am running from the local desktop.
Also I have converted the VS2003 project to VS2005 and when I run the application from Local Desktop then I am getting the data loaded fine. I could not understand what is the problem, what is the difference between accessing data from VS2003 and VS2005 using webservice.
Is this a firewall issue or I am missing any thing here. The webservice method I am calling to get data is actually returning dataset when I am calling this method directly from the browser also and even through application in virtual machine.
I am still not able to come to terms with this problem. This is severely affecting our deployment. Please help us resolve this problem. Please let me know if you need more information.
Thanks,
Venkatesh
Hi Venkatesh,
From what I understand, you have 3 components in your application system. There's the SQL Server, some client code, and a web service.
The SQL Server is in the same domain as the Virtual Machines that you are developing in. Your local desktops in India are in the same domain or a different domain than the SQL Server machine?
Since you are using SQL Server user authentication, the cross domain connectivity should not be an issue. You can verify this by running osql.exe or Query Analyzer tool on your local desktop connecting to the SQL Server machine using the same SQL Server user credentials as your client application.
As for the test client not seeing any data from the web service, I think it would help if you can mention how the web service is connecting to SQL Server and how the ADO.Net application is retrieving data from the web service. I can see the web service using ADO.Net (SqlClient) to connect to the backend SQL Server. Then, some front end client application built using the web service's WSDL document.
I think it will help people understand your application setup and provide possible solutions, if you can specify:
What is the web service using to connect to SQL Server (SqlClient)? Is the web service running on the same machine as SQL Server? If the web service is running on a different machine, is there a connectivity issue between the web service and SQL Server? Is the web service connected to SQL Server using SQL User credentials or Integrated authentication? Does the client application require to send user credentials to connect to the web service?|||Sorry for the delay in response,
1. Web Service has Business Logic DLL reference which uses SQLClient ADO .Net commands to connect to database.
2. Web Service and SQL Server are on different servers but on the same domain.
3. There is no connectivity issue between the two machines because when I access same WebService through Web Browser, it works fine.
4. Web Service is connecting to SQL Server using SQL User credentials.
5. The User credentials are configured through WebService's Web.Config file.
I really have no clue about this issue. The issue just lies between the Windows Client and Web Service. Because when I access WebService directly through Web Browser then the Web Method is returning data, that means Web Service is getting the data fine but it is not able to give it to the Client.
FYI this issue occurs only on Windows 2003 Server if I install the same service on Windows 2000 server it works fine.
Anyhelp will be greatly appreciated.
Subscribe to:
Posts (Atom)