Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Dynamic Connection Strings in Reporting Services 2005 Expand / Collapse
Author
Message
Posted Wednesday, May 09, 2007 11:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31

Debby,

I only use Stored procedures and it works perfectly fine with Reporting Services 2005 expression based connection string.

Any time you edit the dataset, and try to save or hit ok after editing, it changes the commantype to Text from Stored procedure. So you have to manually change it back to Storedprocedure and Save the Report and then preview it or deploy it and it should work.

Hope it works for you now.

Post #364616
Posted Wednesday, May 09, 2007 11:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31

Rosey,

it can definetly work for jump to reports. You just have to pass the serverName and database name to the target report that will be accepting these.

=Parameters!ServerName.Value

=Parameteres!DatabaseName.Value

Post #364618
Posted Wednesday, May 09, 2007 11:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31

Adriaan,

Dynamic connection string is a Great feature in RS2005. When you use shared datasources, you HARDCODE the Servername and Database name in there which you cannot change.

What if  you want to run the same report against a different Server and a different database? you have to create another dataset and tie that with the new report and re-deploy and now ure creating multiple reports for each server/database.

With Dynamic Connection string, You just need one report deployed on a WebServer.

you can run that report against any database on anyserver by simply passing in the servername and databasename.

That one report will generate different data each time you run the report against a different database.

What if you have 5 databases? you can create only one connection string in a dataset and thats hardcoded and cannot be changed when you deploy it. so that report will run against connectionstring/database you hardcoded.

With Dynamic connection string, nothing is hardCoded.. you simple pass it the Server and database names and it will show you the results.

Just One report against 100 databases!

 

 

Post #364620
Posted Wednesday, May 09, 2007 11:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31

Debby,

You can pass in the ServerName and Database in the URL like this:

You need to have ReportViewer first and set its ReportServerURL property first:

http://localhost/ReportServer

Then do this in your code. you need to put your own server Name and database name and the path.

ReportViewerS1.ReportPath = String.Format("/MyReportsFolder/ReportName&ServerName={0}&DatabaseName={1}", "Dev","Northwind")

Hope that helps.

Bilal

Post #364621
Posted Thursday, May 10, 2007 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 03, 2010 11:32 AM
Points: 5, Visits: 4

I do not understand: "You need to have ReportViewer first and set its ReportServerURL property first: http://blockedcontent/ReportServer"

I am passing the url from an asp page (not .net) is this my problem?  The url format is

 
This format works to render report w/o database & server variables.
 
Thank you,
Debby
 
Post #364845
Posted Thursday, May 10, 2007 10:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31

If you don't wanna embed Reports within the ReportViewer in a WebForm then you don't need to use the ReportViewer.

This format should work. https://RSServer/Reportserver?/ReportFolder/ReportName&rs:Command=Render&server=ServerName&database=DataBaseName

as long as your Report has ServerName and Database name parameters defined.

 

Bilal

Post #364981
Posted Thursday, May 10, 2007 11:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi Grasshopper,

Thanks for the reply. The reason why I don't use Custom Datasources is because of the security configuration required. We use shared datasources executing on a specific SQL login. If we need to change the password of the reports login, we only do it in the shared datasources.

We have often found that knowledgeable users exploit Windows Authentication on SQL server, which is another reason why I don't want to pass the Server Name and Database Name in a URL, it opens up the security even wider.

I was hoping that there is a way that I can manipulate the datasource through a class library, and have the report extract the data from a specified database (and Server), without having to pass security information in URLs...

Post #364989
Posted Friday, May 11, 2007 10:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31

Adriaan,

If we are concerned about Security, you can just hard code the username and password in the DataSource Credentials Tab.

As far as DatabaseName and ServerNamer are concerned, you don't have to pass in the full database ans servername in the url.

if your ServerName XYZDallas and DatabaseName is ABCDB

just pass in "Dallas" and "DB" and hard cord their prefixes in the connection string. so the user would not know what the actual database name is...

="data source=XYZ" & Parameters!Server.Value & ";initial catalog=ABC" & Parameters!Database.Value

If you want to maniplulate through a class library, you still are hardcoding server and database information in there. you will not have the luxury to pass in whatever u want.

Post #365213
Posted Sunday, May 13, 2007 11:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi Grasshopper,

I'm not shure how hardcoding the UserName and Password in the DataSource tab would improve the security, please explain.

If you have a large number of reports, and the username and password that you use on the reports leaks out, you will have to change it for every report, making it a nearly impossible task, where as if you use shared datasources, you would only change a few datasources. This in my opinion is a major security risk.

I am also very conservative with using patterns in database names, we typically try to stay away from it as it can become a limitation if your application scales out drastically...

Post #365473
Posted Monday, May 14, 2007 12:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:27 PM
Points: 16, Visits: 31

being on your secure  firewalled intranet is ok.

out in public on Internet, i don't want to send those in..

But my main concern is the flexibility on having 1 report run against million server/databases as long as u have access to those servers from where the report is deployed.

Post #365480
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse