Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts

Friday, March 30, 2012

Need Help Authentication loses HttpContext?

We have both a custom data extension and a custom authentication extension for Reporting Services 2000.

Reports with one dataset work great, but reports with more than one dataset fail because the Authentication extension call GetUserInfo no longer has an HttpContext when the second dataset calls into it.

I've seen several people post this same problem on other forums but no answers. Documentation of an msdn article states "the security extension will always be loaded in the Report Server app domain" so I don't understand how we could lose the context.

Additionally, ALL samples of an authentication extension I've looked at have GetUserInfo coded the same way:

Code Snippet

Public Sub GetUserInfo(ByRef userIdentity As System.Security.Principal.IIdentity, ByRef userId As System.IntPtr) Implements Microsoft.ReportingServices.Interfaces.IAuthenticationExtension.GetUserInfo

If Not System.Web.HttpContext.Current Is Nothing _

AndAlso Not System.Web.HttpContext.Current.User Is Nothing Then

userIdentity = System.Web.HttpContext.Current.User.Identity

Else

userIdentity = Nothing

End If

userId = IntPtr.Zero

End Sub

Any help or insights would be GREATLY appreciated - thanks, Ken.

OK, Wanted to share what I have found after opening a ticket with MS.

What we wanted to accomplish:
To dynamically change our connection string per userID so we could hit multiple instances of the same database structure. We did this by using the UserID stored in the HTTPContext. However, multiple datasets in a report caused Reportings Services to spawn new threads to access the data and RS did not persist the HTTPContext to these threads.

The GetUserInfo problem was a red herring- this did indeed always have access to the HTTPContext which was part of our Authentication extension. The Data extension was the real problem as we didn't have the HTTPContext during creation of our Connection object. What we found out from MS is that Reporting Services doesn't guarantee calls to be made on the asp.net worker thread and indeed in some instances even reports with one dataset failed.

Workaround:

Reports build with RS have access to a global variable UserID - this is actually retrieved from either WindowsIndentity or FormsIdentity based on authentication. Thus I added a report parameter UserID with a non-queried default value of =User!UserID to each report, and then created a parameter @.UserID for every dataset with the value of =Parameters!UserID.Value.

In the connection object if I don't have my HTTPContext, I default to a known database so the code doesn't fail. Then in the Command object's execute reader, I loop through the parameters until I find @.UserID. I now can recreate my connection object based on the UserID and set this connection as my Command's connection and return the datareader from the correct database. I make sure the connection is closed by always specifying the CloseConnection behavior during the datareader creation.

Whew!!! A bit cheesy, but it is allowing us to do what we need. Hopefully this will help anyone else that needs it.

Need Help Authentication loses HttpContext?

We have both a custom data extension and a custom authentication extension for Reporting Services 2000.

Reports with one dataset work great, but reports with more than one dataset fail because the Authentication extension call GetUserInfo no longer has an HttpContext when the second dataset calls into it.

I've seen several people post this same problem on other forums but no answers. Documentation of an msdn article states "the security extension will always be loaded in the Report Server app domain" so I don't understand how we could lose the context.

Additionally, ALL samples of an authentication extension I've looked at have GetUserInfo coded the same way:

Code Snippet

Public Sub GetUserInfo(ByRef userIdentity As System.Security.Principal.IIdentity, ByRef userId As System.IntPtr) Implements Microsoft.ReportingServices.Interfaces.IAuthenticationExtension.GetUserInfo

If Not System.Web.HttpContext.Current Is Nothing _

AndAlso Not System.Web.HttpContext.Current.User Is Nothing Then

userIdentity = System.Web.HttpContext.Current.User.Identity

Else

userIdentity = Nothing

End If

userId = IntPtr.Zero

End Sub

Any help or insights would be GREATLY appreciated - thanks, Ken.

OK, Wanted to share what I have found after opening a ticket with MS.

What we wanted to accomplish:
To dynamically change our connection string per userID so we could hit multiple instances of the same database structure. We did this by using the UserID stored in the HTTPContext. However, multiple datasets in a report caused Reportings Services to spawn new threads to access the data and RS did not persist the HTTPContext to these threads.

The GetUserInfo problem was a red herring- this did indeed always have access to the HTTPContext which was part of our Authentication extension. The Data extension was the real problem as we didn't have the HTTPContext during creation of our Connection object. What we found out from MS is that Reporting Services doesn't guarantee calls to be made on the asp.net worker thread and indeed in some instances even reports with one dataset failed.

Workaround:

Reports build with RS have access to a global variable UserID - this is actually retrieved from either WindowsIndentity or FormsIdentity based on authentication. Thus I added a report parameter UserID with a non-queried default value of =User!UserID to each report, and then created a parameter @.UserID for every dataset with the value of =Parameters!UserID.Value.

In the connection object if I don't have my HTTPContext, I default to a known database so the code doesn't fail. Then in the Command object's execute reader, I loop through the parameters until I find @.UserID. I now can recreate my connection object based on the UserID and set this connection as my Command's connection and return the datareader from the correct database. I make sure the connection is closed by always specifying the CloseConnection behavior during the datareader creation.

Whew!!! A bit cheesy, but it is allowing us to do what we need. Hopefully this will help anyone else that needs it.

Wednesday, March 21, 2012

Need child TABLE object to ref diff DATASET than parent LIST object.

Dang, I wish I had figured this out without having to ask.
Looked and looked for a similar thread - but no real "keyword" for
this issue.
Here's the situation; imagine you have customers and one database
table has demographic information while another database table has a
list of customer orders. Now you want a report that shows all that on
a document map-driven report.
Here's the page layout:
LIST OBJECT // DATASET(dsDemographics) GROUPBY(customer)
DOCMAP(customer)
>> TABLE OBJECT DATASET(dsDemographics) GROUPBY()
>> TABLE OBJECT DATASET(dsOrders) GROUPBY(orderdate)
>> TABLE OBJECT DATASET(dsTickets) GROUPBY(createdate)
Here's the problem: the second table containing orders will not
reference the dsOrders DATASET unless through aggregations - which is
obviously not what I am wanting to do. I want to list all the orders
for the current customer. Same problem with the third table - and
others.
Now, I see the problem is very clear. The orders DATASET does not know
how to filter itself on the current customer. That much I understand.
How do I do it?
Thanks in advance, JerrySounds like your needing to join the datasets. Easiest way would be to join
the data prior to pulling it into RS because, as you noted, RS can only join
data from one dataset to another using an aggregate. If one of your
datasets is SQL, you might use a linked server to pre-join your data.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410221151.4074f9f8@.posting.google.com...
> Dang, I wish I had figured this out without having to ask.
> Looked and looked for a similar thread - but no real "keyword" for
> this issue.
> Here's the situation; imagine you have customers and one database
> table has demographic information while another database table has a
> list of customer orders. Now you want a report that shows all that on
> a document map-driven report.
> Here's the page layout:
> LIST OBJECT // DATASET(dsDemographics) GROUPBY(customer)
> DOCMAP(customer)
>> TABLE OBJECT DATASET(dsDemographics) GROUPBY()
>> TABLE OBJECT DATASET(dsOrders) GROUPBY(orderdate)
>> TABLE OBJECT DATASET(dsTickets) GROUPBY(createdate)
> Here's the problem: the second table containing orders will not
> reference the dsOrders DATASET unless through aggregations - which is
> obviously not what I am wanting to do. I want to list all the orders
> for the current customer. Same problem with the third table - and
> others.
> Now, I see the problem is very clear. The orders DATASET does not know
> how to filter itself on the current customer. That much I understand.
> How do I do it?
> Thanks in advance, Jerry

Friday, March 9, 2012

Need Access-like security of SQL Server Express

Is there a way to implement Access-like password protection on a SQL Server Express dataset?

The database will be deployed on individual's PCs with no centralization of control. I want to restrict users from being able to see table definitions, stored procedures, etc. Access-like password protection is what I want, but I don't see any similar feature within SQL Server Express. Am I missing something?

hi,

at current time SQL Server does not provide this kind of "protection".. you can just encrypt stored procedures/views/user defined functions, you can even encrypt data, but you can not "hide" the database metaschema of the included tables..

regards

Wednesday, March 7, 2012

Need a query...

Hello,
I have a table T1:
Date | Value
--
Jan A
Feb A
Feb B
Mar A
Mar B
Apr A
May A
I need a query to get dataset like this for ANY VALUE with maximum
daterange:
Date | Value
--
Jan A
Feb A
Mar A
Apr A
May A
Thanks,
GBSomething like:
SELECT col1, MAX ( col2 )
FROM tbl
GROUP BY col1 ;
Anith|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. if you had follwoed basic netiquette, would you have
posted this?
CREATE TABLE Foobar
(month_name CHAR(3) NOT NULL,
foo_value CHAR(1) NOT NULL,
PRIMARY KEY (month_name, foo_value));
date range: <<
I am going to guess you mean a foo_value that appears in all the
months.
SELECT foo_value
FROM Foobar AS F1
GROUP BY foo_value
HAVING COUNT(*)
= (SELECT COUNT(DISTINCT month_name) FROM Foobar);
This depends my guess at the DDL you never posted.