Controlling Security through Web Application

Hello all,

I have some questions hopefully you can help with regarding controlling access through ASP.NET.

We'd like to take advantage of reporting services functionality for reports, but we'd like to use our own security model. We have an extensive database-driven security model that exists independently of active directory and windows permissions. We use the NT Logon of the user, but that is all we use. Everything else is maintained within our database structure. We also have all of our web pages and reports in a large database with individual ID's that are permissioned against th

Currently we need a way to link users to reporting services using our own authentication, but this presents a problem. Currently we have a wrapper page (let's call it ReportAccess.aspx). That page authenticates the user and decides whether or not they have access to the report, and then should deliver the report.

Here is where I'm not sure what to do. Our original component simply threw the report URL into an IFRAME. So in order to make this work, we had to give ALL users permissions to all reports on reporting services, since the credentials get passed through. The problem here is that savvy people could look at the URLs and hack their way into reports they should not be seeing.

Ideally, we'd like to only give access to one account and have the ReportAccess.aspx page control that access, but I am not sure how to pull this off. Is this even possible?

The ugly alternative would be maintaining permissions in our system AND Reporting Services ... which would be a lot of work and juggling. There has to be a better way

Okay, I came across the ReportViewer control in ASP.NET and that may be exactly what I was looking for, but I am having a little bit of trouble getting it to work.

I have setup the component as follows:

<rsweb:ReportViewer ID="ReportViewer1" runat="server" ProcessingMode="Remote" >
<ServerReport ReportServerUrl="http://rs2k5/Reports/Pages/Folder.aspx" ReportPath="/MyReportFolder/MyReportName" DisplayName="Test Report" />
<LocalReport />
</rsweb:ReportViewer>

For the record, you can access the reports using this URL:
http://rs2k5/Reports/Pages/Report.aspx?ItemPath=%2fMyReportFolder%2fMyReportName

When I use [http://rs2k5/Reports/Pages/] as the ServerURL, I get a 404 file not found. Interestingly enough, when I use [http://rs2k5/Reports/Pages/Folder.aspx], I get something back, but it looks like this:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <script language="JScript" type="text/Javascript" ****snipped - very long HTML here****
|||To get entirely around security using the report viewer, you need to use an rdlc local to your project that does not rely on the report server all.|||

This is a pretty interesting approach. I had no idea the RDLC option existed. I'm going to test some with that.

Currently, I was able to get the ReportViewer working with Remote option. Turns out you can't point at your ReportServer, instead you have to point at your ReportManager. Using impersonation, I was able to successfully mimic a user's security, so that all users could access the reports using one single account (and then I can use my own validation on the ASP.NET page.

Contract and Queue confusion if named the same plus positioning issue.............

Hello,

The problem I was having regarding re-enable a queue was because it was in another database. I am guessing it did that because the contract and Queues have the same name. I double checked and nothing pointed to that database. I fixed it because I can re-enable the queue in the other database and it is fixed. My question is why did this happen? The other issue I am still having is regarding why the queue got disabled to begin with. I understand that it was because of positioning but do not understand how or why and how to provent it from being disabled.

Thanks,

Scott Allison...

You can have a contract and a queue inside one database with the same name without any conflict. You queue may be getting disabled due to poisoned message detection. You can read about this in Books Online. In short, if you receive from a queue and rollback 5 times consecutively, we detect that the queue probably has a bad message and disable it.

Rushi

Containstable problem

Hi,
I have a question regarding the contains and containstable.
I have a table with about 2.000.000 records.
A the begining we simply use a "select top 1000 * from tablex where
contains(columnx,'xxx')" to find records matching the keywords enter by
users.
But, we find some performance problems when performing queries on keywords
that appears a lot of time
in the table (like "men", "women", "car", ...). The query take more than a
minute.
So we change our query to use the containstable to limit the reseach and it
worked fine and it only take few seconds. (Perfect)
But now, we wanted to add a where clause in the select to limit the reseach
according to a value of a field (not full text)
So, the query was something like this :
SELECT TOP 1000 ID FROM tableX AS FT_TBL
INNER JOIN CONTAINSTABLE (tableX, FT_INDEX , 'men',1000) AS KEY_TBL ON
FT_TBL.ID = KEY_TBL.KEY
WHERE SOURCE = 'news'
But, I notice that the containstable limit the result the 1000 first
occurences of 'men' and after that the where source='news' is applied.
So, of the 1000 records I only have about 500 with a "men" in the ft_index
and source='news'. But in the DB I have more than 2000 records with "men"
and "news"
Is there any way to combine the where clause and benefit of the limit of the
containstable ?
My hope is to have a select distinct top 1000 it from tablex where
source='news' and contains(ft_index,'men',1000)
Thanks
Stephane
No, unfortunately not. When you have restictions in your where clause you
should if at all possible partition your data into different tables.
So you would have a table which would contain all the rows with a source
value of news.
The problem is you get 1000 or less rows returned from MSSearch and then you
trim based on the value of the source column.
So suppose you have 2000 rows which match your search condition, and the
first 1000 rows don't have a source value of news. Such a search would
return 0 results.
The other option is to increase the value of your top_n_by_rank to a value
which guarantees 1000 rows after being filtered by the source restiction.
Normally you don't have the luxury of knowing a value of top_n_by_rank to
guarantee this though.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Stdu" <stdu@.myrealbox.com> wrote in message
news:%233pOC93wEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a question regarding the contains and containstable.
> I have a table with about 2.000.000 records.
> A the begining we simply use a "select top 1000 * from tablex where
> contains(columnx,'xxx')" to find records matching the keywords enter by
> users.
> But, we find some performance problems when performing queries on keywords
> that appears a lot of time
> in the table (like "men", "women", "car", ...). The query take more than a
> minute.
> So we change our query to use the containstable to limit the reseach and
it
> worked fine and it only take few seconds. (Perfect)
> But now, we wanted to add a where clause in the select to limit the
reseach
> according to a value of a field (not full text)
> So, the query was something like this :
> SELECT TOP 1000 ID FROM tableX AS FT_TBL
> INNER JOIN CONTAINSTABLE (tableX, FT_INDEX , 'men',1000) AS KEY_TBL ON
> FT_TBL.ID = KEY_TBL.KEY
> WHERE SOURCE = 'news'
> But, I notice that the containstable limit the result the 1000 first
> occurences of 'men' and after that the where source='news' is applied.
> So, of the 1000 records I only have about 500 with a "men" in the ft_index
> and source='news'. But in the DB I have more than 2000 records with "men"
> and "news"
> Is there any way to combine the where clause and benefit of the limit of
the
> containstable ?
> My hope is to have a select distinct top 1000 it from tablex where
> source='news' and contains(ft_index,'men',1000)
> Thanks
> Stephane
>
>
|||> Is there any way to combine the where clause and benefit of the limit of the
> containstable ?
> My hope is to have a select distinct top 1000 it from tablex where
> source='news' and contains(ft_index,'men',1000)
In Sql2000 I had to do the following (using your example).
Create another column that appended the column(s) that contained the
searched text with a special word or two like "xxxSource_news" and
include this new keyword in the search.
I just posted a question myself... if what we're looking for is
available in Sql2005.
|||This is a nice solution. However it only works with equality. For instance
you can't use if for quantities, ie where you have a where quantity> 1000.
But a very nice solution none the less.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Brian Rice" <mail@.brianrice.com> wrote in message
news:dce86229.0411060843.2d432c70@.posting.google.c om...[vbcol=seagreen]
the
> In Sql2000 I had to do the following (using your example).
> Create another column that appended the column(s) that contained the
> searched text with a special word or two like "xxxSource_news" and
> include this new keyword in the search.
> I just posted a question myself... if what we're looking for is
> available in Sql2005.
|||I will try the Brian's solution as I already have a field where I store all
my keywords to avoid the problem of the contains on multiple fields
Great idea
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OZ0sgfDxEHA.824@.TK2MSFTNGP11.phx.gbl...
> This is a nice solution. However it only works with equality. For
> instance
> you can't use if for quantities, ie where you have a where quantity> 1000.
> But a very nice solution none the less.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Brian Rice" <mail@.brianrice.com> wrote in message
> news:dce86229.0411060843.2d432c70@.posting.google.c om...
> the
>

CONTAINS clause problem with single-quotes

Hi all,
This is cross posted to sqlserver.prgramming also...
I am confused by the MSDN help regarding the Contains clause for fulltext
search.
The Help states that the single-quote does not need to be escaped for
CONTAINS but must be escaped for FREETEXT.
Here is an example of a CONTAINS put together by my search program which
does not produce an error:
AND ( contains((summary),'("girl''s") and ("children''s" | "dog''s")')
OR contains((List_Name),'("girl''s") and ("children''s" | "dog''s")') )
order by list_Name
The fonts in my email are not correctly representing the clause. I have
replaced ' in the word girl's with 2 single quotes as in typical escaping
for strings...similar to a simple:
(Where list_name = 'girl''s'), because (Where list_name = 'girl's') of
course produces a syntax error.
The problem is that I expect only list_names that contain "girl's" to be
returned. However, with the CONTAINS clause using escape for single-quote
also returns any list_name that contains 'girl' as well.
When I don't double up the quotes, the CONTAINS clause returns an error "
syntax error near 's' ", not what is expected by the online Help.
Anybody have some help for me?
John,
SQL Server fulltext indexes do not support searching for punctuation. In
the string 'girl''s' the quote character is a word-breaker, so you wind up
with two words 'girl' and 's'. In most cases 's' is a noise word and gets
dropped altogether.
If you are looking for punctuation, you will have to combine a full text
query clause with a string search clause such as:
AND summary LIKE '%girl''s%'
RLF
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:eOOmzQCQIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> This is cross posted to sqlserver.prgramming also...
> I am confused by the MSDN help regarding the Contains clause for fulltext
> search.
> The Help states that the single-quote does not need to be escaped for
> CONTAINS but must be escaped for FREETEXT.
> Here is an example of a CONTAINS put together by my search program which
> does not produce an error:
> AND ( contains((summary),'("girl''s") and ("children''s" | "dog''s")')
> OR contains((List_Name),'("girl''s") and ("children''s" | "dog''s")') )
> order by list_Name
> The fonts in my email are not correctly representing the clause. I have
> replaced ' in the word girl's with 2 single quotes as in typical escaping
> for strings...similar to a simple:
> (Where list_name = 'girl''s'), because (Where list_name = 'girl's') of
> course produces a syntax error.
> The problem is that I expect only list_names that contain "girl's" to be
> returned. However, with the CONTAINS clause using escape for single-quote
> also returns any list_name that contains 'girl' as well.
> When I don't double up the quotes, the CONTAINS clause returns an error "
> syntax error near 's' ", not what is expected by the online Help.
> Anybody have some help for me?
>
|||Thanks Russell,
That makes perfectly good sense. I appreciate the help.
Happy holidays.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OAQrdmLQIHA.4912@.TK2MSFTNGP06.phx.gbl...
> John,
> SQL Server fulltext indexes do not support searching for punctuation. In
> the string 'girl''s' the quote character is a word-breaker, so you wind up
> with two words 'girl' and 's'. In most cases 's' is a noise word and gets
> dropped altogether.
> If you are looking for punctuation, you will have to combine a full text
> query clause with a string search clause such as:
> AND summary LIKE '%girl''s%'
> RLF
> "John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
> news:eOOmzQCQIHA.5980@.TK2MSFTNGP04.phx.gbl...
>

Constraints

Can any one tell me regarding the foreign key constraints like I wanna
findout which columns doesn't contain the foreign key constraint so that I
can create it...
Thanks in advance.
Hi Joh,
Why do you want create Foreign key constrain for all the columns in a table
, Probably i understood your question wrongly.
What you could do is all the constrains specificatins can be view in the
below information schema view. Have a look into it and analyze ur
requirement.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Can any one tell me regarding the foreign key constraints like I wanna
> findout which columns doesn't contain the foreign key constraint so that I
> can create it...
>
> Thanks in advance.
>
|||No you took my question wrongly... I want to make sure Primary Key &
Foreign Key constraints are there in my database... nothing would be missed
for instance...
Emp Dept
EmpID DeptID
DeptID DeptName
EmpName
According to the above situation I have to make sure in the emp table there
should be a foreign key constraint on DeptID column.. Is there any query
through which I can make sure about it... hope now you understand my
question.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi Joh,
> Why do you want create Foreign key constrain for all the columns in a
table[vbcol=seagreen]
> , Probably i understood your question wrongly.
> What you could do is all the constrains specificatins can be view in the
> below information schema view. Have a look into it and analyze ur
> requirement.
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
I
>
|||Hi,
I dont have a query for this with me. I personally recommend you to use MS
Visio / Erwin to do the logical / physical data model with all relationship.
But for validating you could use INFORMATION_SCHEMA.TABLE_CONSTRAINTS system
view.
Thanks
Hari
SQL Server MVP
can you please work with the system view
INFORMATION_SCHEMA.TABLE_CONSTRAINTS? This will help
"Joh" <joh@.mailcity.com> wrote in message
news:OAQTHygYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> No you took my question wrongly... I want to make sure Primary Key &
> Foreign Key constraints are there in my database... nothing would be
> missed
> for instance...
> Emp Dept
> EmpID DeptID
> DeptID DeptName
> EmpName
> According to the above situation I have to make sure in the emp table
> there
> should be a foreign key constraint on DeptID column.. Is there any query
> through which I can make sure about it... hope now you understand my
> question.
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> table
> I
>