Help with security model for RS implementation needed

We're running Reporting Services (wSP1) on a Win 2003 server box. Presently
(for development), SQL Server, the web application and RS all run on the
same box. I've configured an app pool in IIS under which Reports,
ReportServer and the web application run. I'm also collecting credentials
via forms auth which I pass as the credentials to RS during web service
calls. We are using URL access to access rendered reports.
RS Windows Service is configured to run as NT AUTH\Network Service.
All datasources are set up using trusted security.
What I'd like to be able to do to ensure that we use connection pooling is
not impersonate the credentials passed in but instead connect to the OLAP
database as a single domain account.
Is this possible and if so, what security configuration changes should I
make to make this happen?
Thanks in advance.
-TimPlease disregard my original post. The absurd amounts of caffeine I've been
consuming lately have caused temporary memory loss. :)
-Tim
"Tim Ellison" <TimEllison@.direcway.com> wrote in message
news:Oajl$kstEHA.1400@.TK2MSFTNGP11.phx.gbl...
> We're running Reporting Services (wSP1) on a Win 2003 server box.
Presently
> (for development), SQL Server, the web application and RS all run on the
> same box. I've configured an app pool in IIS under which Reports,
> ReportServer and the web application run. I'm also collecting credentials
> via forms auth which I pass as the credentials to RS during web service
> calls. We are using URL access to access rendered reports.
> RS Windows Service is configured to run as NT AUTH\Network Service.
> All datasources are set up using trusted security.
> What I'd like to be able to do to ensure that we use connection pooling is
> not impersonate the credentials passed in but instead connect to the OLAP
> database as a single domain account.
> Is this possible and if so, what security configuration changes should I
> make to make this happen?
> Thanks in advance.
> -Tim
>

Help with Security Model

Hi all,

I need some sugestions from all of you about setting up security model in
our SQL2000 box.

The server was setup using Mixed mode. However, all the applications
(web and MS access) access the server using "sa" userid.

There are several databases in our server. Ex: (DB1,DB2,DB3,DB4 and DB5)

Application 1: need read/write access to DB1,DB2 and DB3
Application 2: need read/write access to DB5
Application 3: need read/write access to DB4 and DB3

Should I set up three userids and give them the dbo access to those
database that they need to use?

Does that make any sense to you?

Thank you for all your suggestionThe 'sa' account should NEVER be used by ANY application or user (other than the DBA, and then carefully.)

Use three different UID/Pwd for the applications and GRANT permissions that way.|||In addition, unless they really NEED dbo, just GRANT the minimum they need.|||I'm a firm believer in the "principle of least privlege", meaning you create as many accounts as you need and only give each one the privleges that it needs to do its job. Check out the predefined database roles (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_5omd.asp) to see if your accounts even need dbo, or if a combination of db_datareader and db_datawriter would do.

-PatP|||I take it a bit further than data_reader/writer and grant Select/Insert/Update/Delete permissions as required. For instance, a financial application allows Inserts but NO updates. Once a record has been inserted, that's the way it stays. Corrections are made by inserting another record to adjust the transaction (along with an explanation.) Auditors seem to prefer this for following the money trail. Good thing I'm sa... :)|||Thank you for all your suggestions.

Help With Query and/or Design

I am currently trying to model a database and have great difficulty in
determining the correct structure.
General Subject - Aircraft Flights.
Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
AirportDepartID = FK, AirportArrivalID = FK, etc...)
Table - Airports ( AiportID = PK , AirportName, City , Country
etc...) ,
PK = Primary Key , FK = Foreign Key .
The ForeignKeys require a double liaison between FlightLegs and
Airports. ( Is this a bad idea ?).
----
1 : Each flight may have one or more flightlegs.
2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
----
For a given FlightNumber I wish to be able to display a list of all
flightlegs and the appropriate AirportNames.
I am having difficulty trying to create queries where I can get both
"AirportNames" for a given flightleg. I can easily retrieve either the
Departure or Arrival name but cannot seem to get both in one query.
I have simplified my examples for clarity.
No problem here
SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportDepartID FROM Airports
Problem here
SELECT AirportName as Departure,AirportName as Arrival from Airports
WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
I realise that the above can never work due to the AND condition.
I know that the following works but I don't like this solution.
SELECT
( SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportDepartID FROM Airports) Departure,
(SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportArrivalID FROM Airports) Arrival
FROM Airports
what would be a more elegant solution, is my model incorrect.On Apr 30, 2:11 pm, r.whiteh...@.gyrolan.com wrote:
> I am currently trying to model a database and have great difficulty in
> determining the correct structure.
> General Subject - Aircraft Flights.
> Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
> Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
> AirportDepartID = FK, AirportArrivalID = FK, etc...)
> Table - Airports ( AiportID = PK , AirportName, City , Country
> etc...) ,
> PK = Primary Key , FK = Foreign Key .
> The ForeignKeys require a double liaison between FlightLegs and
> Airports. ( Is this a bad idea ?).
> ----
-
> 1 : Each flight may have one or more flightlegs.
> 2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
> ----
> For a given FlightNumber I wish to be able to display a list of all
> flightlegs and the appropriate AirportNames.
> I am having difficulty trying to create queries where I can get both
> "AirportNames" for a given flightleg. I can easily retrieve either the
> Departure or Arrival name but cannot seem to get both in one query.
> I have simplified my examples for clarity.
> No problem here
> SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportDepartID FROM Airports
> Problem here
> SELECT AirportName as Departure,AirportName as Arrival from Airports
> WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
> Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
> I realise that the above can never work due to the AND condition.
> I know that the following works but I don't like this solution.
> SELECT
> ( SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportDepartID FROM Airports) Departure,
> (SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportArrivalID FROM Airports) Arrival
> FROM Airports
> what would be a more elegant solution, is my model incorrect.
SELECT FlightLegID,
(SELECT AirportName FROM Airports WHERE Airports.AirportID
=FlightLeg.AirportDepartID ) as AirportDepart,
(SELECT AirportName FROM Airports WHERE Airports.AirportID
=FlightLeg.AirportArrivalID ) as AirportArrival
FROM FlightLegs|||Here is another alternative.
SELECT
fl.FlightLegID,
dep.AirportName DepartureAirport,
arr.AirportName ArrivalAirport
FROM
FlightLegs fl
JOIN Airports dep ON dep.AirportID = fl.AirportDepartID
JOIN Airports arr ON arr.AirportID = fl.AirportArrivalID
On Apr 30, 7:33 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Apr 30, 2:11 pm, r.whiteh...@.gyrolan.com wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> SELECT FlightLegID,
> (SELECT AirportName FROM Airports WHERE Airports.AirportID
> =FlightLeg.AirportDepartID ) as AirportDepart,
> (SELECT AirportName FROM Airports WHERE Airports.AirportID
> =FlightLeg.AirportArrivalID ) as AirportArrival
> FROM FlightLegs- Hide quoted text -
> - Show quoted text -

Help With Query and/or Design

I am currently trying to model a database and have great difficulty in
determining the correct structure.
General Subject - Aircraft Flights.
Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
AirportDepartID = FK, AirportArrivalID = FK, etc...)
Table - Airports ( AiportID = PK , AirportName, City , Country
etc...) ,
PK = Primary Key , FK = Foreign Key .
The ForeignKeys require a double liaison between FlightLegs and
Airports. ( Is this a bad idea ?).
1 : Each flight may have one or more flightlegs.
2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
For a given FlightNumber I wish to be able to display a list of all
flightlegs and the appropriate AirportNames.
I am having difficulty trying to create queries where I can get both
"AirportNames" for a given flightleg. I can easily retrieve either the
Departure or Arrival name but cannot seem to get both in one query.
I have simplified my examples for clarity.
No problem here
SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportDepartID FROM Airports
Problem here
SELECT AirportName as Departure,AirportName as Arrival from Airports
WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
I realise that the above can never work due to the AND condition.
I know that the following works but I don't like this solution.
SELECT
( SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportDepartID FROM Airports) Departure,
(SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportArrivalID FROM Airports) Arrival
FROM Airports
what would be a more elegant solution, is my model incorrect.
On Apr 30, 2:11 pm, r.whiteh...@.gyrolan.com wrote:
> I am currently trying to model a database and have great difficulty in
> determining the correct structure.
> General Subject - Aircraft Flights.
> Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
> Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
> AirportDepartID = FK, AirportArrivalID = FK, etc...)
> Table - Airports ( AiportID = PK , AirportName, City , Country
> etc...) ,
> PK = Primary Key , FK = Foreign Key .
> The ForeignKeys require a double liaison between FlightLegs and
> Airports. ( Is this a bad idea ?).
> ----
> 1 : Each flight may have one or more flightlegs.
> 2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
> ----
> For a given FlightNumber I wish to be able to display a list of all
> flightlegs and the appropriate AirportNames.
> I am having difficulty trying to create queries where I can get both
> "AirportNames" for a given flightleg. I can easily retrieve either the
> Departure or Arrival name but cannot seem to get both in one query.
> I have simplified my examples for clarity.
> No problem here
> SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportDepartID FROM Airports
> Problem here
> SELECT AirportName as Departure,AirportName as Arrival from Airports
> WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
> Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
> I realise that the above can never work due to the AND condition.
> I know that the following works but I don't like this solution.
> SELECT
> ( SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportDepartID FROM Airports) Departure,
> (SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportArrivalID FROM Airports) Arrival
> FROM Airports
> what would be a more elegant solution, is my model incorrect.
SELECT FlightLegID,
(SELECT AirportName FROM Airports WHERE Airports.AirportID
=FlightLeg.AirportDepartID ) as AirportDepart,
(SELECT AirportName FROM Airports WHERE Airports.AirportID
=FlightLeg.AirportArrivalID ) as AirportArrival
FROM FlightLegs
|||Here is another alternative.
SELECT
fl.FlightLegID,
dep.AirportName DepartureAirport,
arr.AirportName ArrivalAirport
FROM
FlightLegs fl
JOIN Airports dep ON dep.AirportID = fl.AirportDepartID
JOIN Airports arr ON arr.AirportID = fl.AirportArrivalID
On Apr 30, 7:33 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Apr 30, 2:11 pm, r.whiteh...@.gyrolan.com wrote:
>
>
>
>
>
>
>
>
>
>
> SELECT FlightLegID,
> (SELECT AirportName FROM Airports WHERE Airports.AirportID
> =FlightLeg.AirportDepartID ) as AirportDepart,
> (SELECT AirportName FROM Airports WHERE Airports.AirportID
> =FlightLeg.AirportArrivalID ) as AirportArrival
> FROM FlightLegs- Hide quoted text -
> - Show quoted text -

Help With Query and/or Design

I am currently trying to model a database and have great difficulty in
determining the correct structure.
General Subject - Aircraft Flights.
Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
AirportDepartID = FK, AirportArrivalID = FK, etc...)
Table - Airports ( AiportID = PK , AirportName, City , Country
etc...) ,
PK = Primary Key , FK = Foreign Key .
The ForeignKeys require a double liaison between FlightLegs and
Airports. ( Is this a bad idea ?).
----
1 : Each flight may have one or more flightlegs.
2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
----
For a given FlightNumber I wish to be able to display a list of all
flightlegs and the appropriate AirportNames.
I am having difficulty trying to create queries where I can get both
"AirportNames" for a given flightleg. I can easily retrieve either the
Departure or Arrival name but cannot seem to get both in one query.
I have simplified my examples for clarity.
No problem here
SELECT AirportName from Airports WHERE Airports.AirportID = FlightLeg.AirportDepartID FROM Airports
Problem here
SELECT AirportName as Departure,AirportName as Arrival from Airports
WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
I realise that the above can never work due to the AND condition.
I know that the following works but I don't like this solution.
SELECT
( SELECT AirportName from Airports WHERE Airports.AirportID = FlightLeg.AirportDepartID FROM Airports) Departure,
(SELECT AirportName from Airports WHERE Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports) Arrival
FROM Airports
what would be a more elegant solution, is my model incorrect.On Apr 30, 2:11 pm, r.whiteh...@.gyrolan.com wrote:
> I am currently trying to model a database and have great difficulty in
> determining the correct structure.
> General Subject - Aircraft Flights.
> Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
> Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
> AirportDepartID = FK, AirportArrivalID = FK, etc...)
> Table - Airports ( AiportID = PK , AirportName, City , Country
> etc...) ,
> PK = Primary Key , FK = Foreign Key .
> The ForeignKeys require a double liaison between FlightLegs and
> Airports. ( Is this a bad idea ?).
> ----
> 1 : Each flight may have one or more flightlegs.
> 2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
> ----
> For a given FlightNumber I wish to be able to display a list of all
> flightlegs and the appropriate AirportNames.
> I am having difficulty trying to create queries where I can get both
> "AirportNames" for a given flightleg. I can easily retrieve either the
> Departure or Arrival name but cannot seem to get both in one query.
> I have simplified my examples for clarity.
> No problem here
> SELECT AirportName from Airports WHERE Airports.AirportID => FlightLeg.AirportDepartID FROM Airports
> Problem here
> SELECT AirportName as Departure,AirportName as Arrival from Airports
> WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
> Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
> I realise that the above can never work due to the AND condition.
> I know that the following works but I don't like this solution.
> SELECT
> ( SELECT AirportName from Airports WHERE Airports.AirportID => FlightLeg.AirportDepartID FROM Airports) Departure,
> (SELECT AirportName from Airports WHERE Airports.AirportID => FlightLeg.AirportArrivalID FROM Airports) Arrival
> FROM Airports
> what would be a more elegant solution, is my model incorrect.
SELECT FlightLegID,
(SELECT AirportName FROM Airports WHERE Airports.AirportID
=FlightLeg.AirportDepartID ) as AirportDepart,
(SELECT AirportName FROM Airports WHERE Airports.AirportID
=FlightLeg.AirportArrivalID ) as AirportArrival
FROM FlightLegs|||Here is another alternative.
SELECT
fl.FlightLegID,
dep.AirportName DepartureAirport,
arr.AirportName ArrivalAirport
FROM
FlightLegs fl
JOIN Airports dep ON dep.AirportID = fl.AirportDepartID
JOIN Airports arr ON arr.AirportID = fl.AirportArrivalID
On Apr 30, 7:33 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Apr 30, 2:11 pm, r.whiteh...@.gyrolan.com wrote:
>
>
> > I am currently trying to model a database and have great difficulty in
> > determining the correct structure.
> > General Subject - Aircraft Flights.
> > Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
> > Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
> > AirportDepartID = FK, AirportArrivalID = FK, etc...)
> > Table - Airports ( AiportID = PK , AirportName, City , Country
> > etc...) ,
> > PK = Primary Key , FK = Foreign Key .
> > The ForeignKeys require a double liaison between FlightLegs and
> > Airports. ( Is this a bad idea ?).
> > ----
> > 1 : Each flight may have one or more flightlegs.
> > 2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
> > ----
> > For a given FlightNumber I wish to be able to display a list of all
> > flightlegs and the appropriate AirportNames.
> > I am having difficulty trying to create queries where I can get both
> > "AirportNames" for a given flightleg. I can easily retrieve either the
> > Departure or Arrival name but cannot seem to get both in one query.
> > I have simplified my examples for clarity.
> > No problem here
> > SELECT AirportName from Airports WHERE Airports.AirportID => > FlightLeg.AirportDepartID FROM Airports
> > Problem here
> > SELECT AirportName as Departure,AirportName as Arrival from Airports
> > WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
> > Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
> > I realise that the above can never work due to the AND condition.
> > I know that the following works but I don't like this solution.
> > SELECT
> > ( SELECT AirportName from Airports WHERE Airports.AirportID => > FlightLeg.AirportDepartID FROM Airports) Departure,
> > (SELECT AirportName from Airports WHERE Airports.AirportID => > FlightLeg.AirportArrivalID FROM Airports) Arrival
> > FROM Airports
> > what would be a more elegant solution, is my model incorrect.
> SELECT FlightLegID,
> (SELECT AirportName FROM Airports WHERE Airports.AirportID
> =FlightLeg.AirportDepartID ) as AirportDepart,
> (SELECT AirportName FROM Airports WHERE Airports.AirportID
> =FlightLeg.AirportArrivalID ) as AirportArrival
> FROM FlightLegs- Hide quoted text -
> - Show quoted text -

Help With Query

Hello,
I am selecting DISTINCT records from a table:
"SELECT DISTINCT Model, Description FROM Warranty"
This works great, but I would like to concatinate some other data in the
returned records (possibly with a JOIN)
From the same table I would like the returned records to return "Family" and
"Price".
So the returned records would be:
Model Description Family Price (with the Model and Description being
Distrinct).
NOTE: There is a Primary Key Called "ID".
Maybe something like:
SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
Any help is greatly appreciated,
Thanks,
Chuck
Hi Charles
How about
SELECT Model, Description, MAX(Family) AS [Family], Max(Price) AS [Price]
FROM Warranty
GROUP BY Model, Description
John

>
"Charles A. Lackman" wrote:

> Hello,
> I am selecting DISTINCT records from a table:
> "SELECT DISTINCT Model, Description FROM Warranty"
> This works great, but I would like to concatinate some other data in the
> returned records (possibly with a JOIN)
> From the same table I would like the returned records to return "Family" and
> "Price".
> So the returned records would be:
> Model Description Family Price (with the Model and Description being
> Distrinct).
> NOTE: There is a Primary Key Called "ID".
> Maybe something like:
> SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
> C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
> Any help is greatly appreciated,
> Thanks,
> Chuck
>
>

Help With Query

Hello,
I am selecting DISTINCT records from a table:
"SELECT DISTINCT Model, Description FROM Warranty"
This works great, but I would like to concatinate some other data in the
returned records (possibly with a JOIN)
From the same table I would like the returned records to return "Family" and
"Price".
So the returned records would be:
Model Description Family Price (with the Model and Description being
Distrinct).
NOTE: There is a Primary Key Called "ID".
Maybe something like:
SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
Any help is greatly appreciated,
Thanks,
ChuckHi Charles
How about
SELECT Model, Description, MAX(Family) AS [Family], Max(Price) AS [P
rice]
FROM Warranty
GROUP BY Model, Description
John

>
"Charles A. Lackman" wrote:

> Hello,
> I am selecting DISTINCT records from a table:
> "SELECT DISTINCT Model, Description FROM Warranty"
> This works great, but I would like to concatinate some other data in the
> returned records (possibly with a JOIN)
> From the same table I would like the returned records to return "Family" a
nd
> "Price".
> So the returned records would be:
> Model Description Family Price (with the Model and Description being
> Distrinct).
> NOTE: There is a Primary Key Called "ID".
> Maybe something like:
> SELECT DISCTINCT C1.Model, C1.Description FROM Warranty AS C1, Warranty AS
> C2 (Select C2.Family, C2.Price FROM C2 ON C1.ID = C2.ID)
> Any help is greatly appreciated,
> Thanks,
> Chuck
>
>