Packaging reports as a product?

I've written an application that generates a couple of stored procs and
views, and installs them into a DB. I also have a series of reports in
Reporting Services that pull data from those views.
Its easy enough for me to distribute the .exe for the app that generates SPs
and views, but how do I distribute the reports without giving the installer
access to them? Basically, I dont want anyone to poke around through the
formulas, etc.
Is there a way to do this?Henry,
Even if you secure the installation process the administrator can always see
the report definition in the Report Manager. So, I would rather gravitate
toward moving the sensitive information in an external .NET assembly and
possibly obfuscating it.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in message
news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> I've written an application that generates a couple of stored procs and
> views, and installs them into a DB. I also have a series of reports in
> Reporting Services that pull data from those views.
> Its easy enough for me to distribute the .exe for the app that generates
SPs
> and views, but how do I distribute the reports without giving the
installer
> access to them? Basically, I dont want anyone to poke around through the
> formulas, etc.
> Is there a way to do this?|||Youre suggesting an assembly that spits out a dataset, and reporting on that?
Are there any examples of this around?
"Teo Lachev [MVP]" wrote:
> Henry,
> Even if you secure the installation process the administrator can always see
> the report definition in the Report Manager. So, I would rather gravitate
> toward moving the sensitive information in an external .NET assembly and
> possibly obfuscating it.
> --
> Hope this helps.
> ----
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in message
> news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> > I've written an application that generates a couple of stored procs and
> > views, and installs them into a DB. I also have a series of reports in
> > Reporting Services that pull data from those views.
> >
> > Its easy enough for me to distribute the .exe for the app that generates
> SPs
> > and views, but how do I distribute the reports without giving the
> installer
> > access to them? Basically, I dont want anyone to poke around through the
> > formulas, etc.
> >
> > Is there a way to do this?
>
>|||Well, depends on what you want to protect. You mentioned that you want to
protect the formulas so I imagined that you have some sensitive information
embedded in your formulas that you don't want to reveal.
What exactly do you want to protect?
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Henry Zektser" <HenryZektser@.discussions.microsoft.com> wrote in message
news:D95E5500-FB7D-4596-A92B-09E4A6F58981@.microsoft.com...
> Youre suggesting an assembly that spits out a dataset, and reporting on
that?
> Are there any examples of this around?
> "Teo Lachev [MVP]" wrote:
> > Henry,
> >
> > Even if you secure the installation process the administrator can always
see
> > the report definition in the Report Manager. So, I would rather
gravitate
> > toward moving the sensitive information in an external .NET assembly and
> > possibly obfuscating it.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in
message
> > news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> > > I've written an application that generates a couple of stored procs
and
> > > views, and installs them into a DB. I also have a series of reports in
> > > Reporting Services that pull data from those views.
> > >
> > > Its easy enough for me to distribute the .exe for the app that
generates
> > SPs
> > > and views, but how do I distribute the reports without giving the
> > installer
> > > access to them? Basically, I dont want anyone to poke around through
the
> > > formulas, etc.
> > >
> > > Is there a way to do this?
> >
> >
> >

packages and data source views do not work?

Hi,

doing my first steps in SSIS I wanted to copy data from 2 different SQL 2000 database servers to a SQL 2005 Data warehouse. For not having to deploy additional views and procedures to the individual systems I chose to create a Data Source View to create an abstract view on the different data sources. I found out that I can have named queries pointing to the two different data sources in the same view.

1 Project, 2 Data Sources, 1 Data Source View with 3 Named Queries

When I now add a Data Flow Task to the Control Flow how can I specify my DSV as Source for Transformations? I even added both OLEDB Connections to the Connection Manager but the Named Queries from my DSV do not appear at all. I even tried "SELECT from [myNamedQueryFromDSV]" but without success.

The description available at http://msdn2.microsoft.com/en-us/ms403395.aspx is bullshit. There is nothing to expand about the "Connection Manager" in the Data Flow Window. I can add a OLEDB Source as described in the above HOWTO and double click it. But the dropdown field for Connection Manager does offer only the two OLEDB Connections and nothing more. Among the items of the access mode "Tables and Views" the named queries not appear. It does not even work with a homogeneous Data Source View.

How can I make it work? Ain't there a better (working) HOWTO out there on how to enable DSV als Data Flow Task data sources? Do I have to wait for SP2 to solve the problem or is it not possible by intention?

Cheers,
Frank

You say you "added both OLEDB Connections to the Connection Manager." I am not sure exactly what you mean, but I wonder if you right-clicked in the Connection Managers tray and added an OLEDB connection. That would not help you use a Data Source View.

As the splendidly written and accurate article at http://msdn2.microsoft.com/en-us/ms403395.aspx describes through its links, you should in fact add a Connection from a Data Source: http://msdn2.microsoft.com/en-us/ms403395.aspx.

You can tell if you have a Connection Manager that enables access to Data Source Views by looking at the icon. If it looks like a Data Source icon, you're in business. If it looks like an OLEDB Connection Manager icon, then you will not be able to see any Data Source Views.

Donald

|||

Hi Donald,

thanks for replying. Your hint about the Icon that should be different led me to the right actions.

But regarding the quality and accurateness of the article I have to disagree.

Please look add the following quote taken directly from the article:
"After a data source and its data sources views are in the Integration Services project, you add must add a connection manager that references the data source to the package. When you add the connection manager based on a data source to a package, Integration Services adds an OLE DB type connection manager. Only sources, transformations, and destinations that can use an OLE DB connection manager can use data source views."

It tells that an OLE DB Type connection manager is added and that DSVs only work with OLE DB connection managers. There's no real usable hint, that I need to create some-kind-of-reference connection. From reading the article I thought I need to add the OLE DB connection that I used already for the DSV. "New Connection from Datasource" as an option is not quite clear in its meaning. Where's the difference to the normal OLEDB Connection? It looks to be linking to that OLE DB connection underneath anyway.

And one problem remains: As my DSV does have Named Queries pointing to both OLE DB datasource connections (2x datasource 1, 1x datasource 2) I only see the 2 first named queries using the connection manager but I never see the 3rd from the other datasource regardless if I also create a "New Connection from Datasource" from the second one and specify that one for the OLE DB Source. So heterogeneous DSVs are not supported?

Best regards, Frank

|||

The doc is indeed accurate, although maybe I should not overpraise its style. :-) The following extracts do seem very clear to me and even include a link to detailed info on how to add the data source.

... a connection manager that references the data source

... add the connection manager based on a data source

Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.

However, the reader is always right, and if you did not find the docs helpful, then that is fair enough. The information you needed was there, but you could not find it. It would be great if you could provide some detail on how you think they could have been better worded, or more clearly structured, to help us help others.

Meanwhile, you are indeed correct that you can only use one (the default) data source in a data source view with SSIS.

Donald

|||

Hello once again,

Donald Farmer wrote:

Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.

This is exactly one of the misleading statements. I shall add the data source on which the DSV was built. And for the DSV I have added two OLE DB Data Sources to the project. So my conclusion would be to reuse them. When I right-click the connection manager of the package I can choose between several types of connections to be added. And as the article also states that DSVs only work with OLE DB connections my first choice would be to select "New OLE DB Connection ...". Doing so pops up a dialog with the OLE DB Data Sources I have already defined in the project and used for the DSV.

Maybe this would be a better explanation:

Before you can use a data source view in a package, you must add a data source reference ("New Connection from Datasource") to the project data source object, on which the data source view is built, to the package. Your project data source object should be an OLE DB data source. For more information, ...

And for the future it would be nice if the SQL 2005 Development team could allow something like "New Connection to Data Source View" which would be (imho) very user friendly.

As for the restriction that only the Named Queries / Table Queries basing on the default data source in a DSV can be used in packages - a hint in the documentation would be very appreciated - and surely not only by me. Is that a restriction by intention or is it a bug going to solved in the future?

I highly appreciate your dealing with this topic

Kind regards, Frank

|||I agree that the documentation is not clear. I'm still struggling trying to get this setup.

ownership chains, users and views

We have a design using views to partition visibility of table rows by sql us
er. Say we have two salesmen Fred and Barney, we wanted to create 2 views:
CREATE VIEW Fred.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE SALE
S_PERSON='Fred'
and
CREATE VIEW Barney.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE SA
LES_PERSON='Barney'
Neither salesman should be able to select from ALL_SALES_LEADS and each, bei
ng amazingly SQL savvy salesmen, can log on to sql and execute SELECT * FROM
SALES_LEADS WHERE STATUS='New'.
Problem is that neither because neither Fred nor Barney owns the ALL_SALES_L
EADS table, they can't select off the view with any combination of GRANT/DEN
Y/REVOKE statements that we can find because the ownership chains are broken
.
Our best fallback is to name the views differently but leave them owned by d
bo. Not as neat and tidy. ie
CREATE VIEW dbo.Fred_SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Fred'
GRANT SELECT ON dbo.Fred_SALES_LEADS TO Fred
Is there any way around our original problem, SQL gurus? I think this would
be a very good use of user-owned views in a number of realistic scenarios.> Our best fallback is to name the views differently but leave them owned by
dbo.
The table and view need to have the same owner in order to use views as a
security mechanism. If each salesperson accesses the database with their
own userid, you might consider filtering using database userid instead of a
hard-coded constant. This way, you only need one view. For example:
CREATE VIEW dbo.SALES_LEADS AS
SELECT *
FROM dbo.ALL_SALES_LEADS
WHERE SALES_PERSON = CURRENT_USER
GO
GRANT SELECT ON dbo.SALES_LEADS TO SalesRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave Cattermole" <anonymous@.discussions.microsoft.com> wrote in message
news:726B0928-E93C-4233-A5FE-051E8ADA5684@.microsoft.com...
> We have a design using views to partition visibility of table rows by sql
user. Say we have two salesmen Fred and Barney, we wanted to create 2 views:
> CREATE VIEW Fred.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Fred'
> and
> CREATE VIEW Barney.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Barney'
> Neither salesman should be able to select from ALL_SALES_LEADS and each,
being amazingly SQL savvy salesmen, can log on to sql and execute SELECT *
FROM SALES_LEADS WHERE STATUS='New'.
> Problem is that neither because neither Fred nor Barney owns the
ALL_SALES_LEADS table, they can't select off the view with any combination
of GRANT/DENY/REVOKE statements that we can find because the ownership
chains are broken .
> Our best fallback is to name the views differently but leave them owned by
dbo. Not as neat and tidy. ie
> CREATE VIEW dbo.Fred_SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS
WHERE SALES_PERSON='Fred'
> GRANT SELECT ON dbo.Fred_SALES_LEADS TO Fred
> Is there any way around our original problem, SQL gurus? I think this
would be a very good use of user-owned views in a number of realistic
scenarios.

Owner

I have a few objects (views, tables, stored procedures) that are owned by a
user called 'appWebUser'. If I'm logged under another user, i.e.
(appTrainer), I have to qualify the owner.object to access it instead of
just the object name. Is there any way to just use the object name like the
dbo owner. For example.
Currently: logged in as appTrainer
select * from appWebUser.table1
What I Want: logged in as appTrainer
select * from table1Sorry, I should have included ...
If you want to get rid of the owner.object issue, change all of the object
owners to dbo.
"Morgan" <mfears@.spamcop.net> wrote in message
news:eCBjzulcDHA.3620@.TK2MSFTNGP11.phx.gbl...
> As long as all of the object names are unique for each type of object
> (table, view, etc) across the whole database, you can rename them using
> sp_changeobjectowner. However, as you'll see in BOL, any permissions on
the
> table must be reapplied once you change the owner. I would go about this
> very carefully.
>
> "Tim" <Tim@.NOSPAM.com> wrote in message
> news:#8dn8XlcDHA.2436@.TK2MSFTNGP12.phx.gbl...
> > I have a few objects (views, tables, stored procedures) that are owned
by
> a
> > user called 'appWebUser'. If I'm logged under another user, i.e.
> > (appTrainer), I have to qualify the owner.object to access it instead of
> > just the object name. Is there any way to just use the object name like
> the
> > dbo owner. For example.
> >
> > Currently: logged in as appTrainer
> > select * from appWebUser.table1
> >
> > What I Want: logged in as appTrainer
> > select * from table1
> >
> >
>