Authentication issue with report called from VB.Net application

Hi,
I've written an application in VB.Net 2005. One of its requirements is to
display a report which is hosted on our report server, and to let people
subscribe to this report (to get a copy once a week by email, for example).
To do this I have created a windows form containing a web browser control,
and set the URL of that control to the URL of the report, which happens to
be:
http://10.9.200.18/Reports/Pages/Report.aspx?ItemPath=%2fBunker+Management+Reports%2fActive+Bunker+Schemes+Due+To+Expire
Whenever I open that form I get prompted for my windows credentials. Also,
if I go straight to that URL in a web browser, I also get prompted for my
credentials. However, if I start from
http://10.9.200.18/Reports I can navigate to the same report without being
prompted for my credentials.
What can I do to be able to jump straight to the report without being
prompted for credentials? I switched on the option to enable anonymous
access for the web site itself (this is an internal web server), which did
the trick, but then I couldn't access the report server via either
http://10.9.200.18/Reports - took me to the report server home page, but
didn't list any contents
or
http://10.9.200.18/ReportServer - told me that the anonymous user account
didn't have permission to access the resource.
Enabling anonymous access wasn't exactly ideal, anyway. For one thing it
applied to the whole site, not just the report, and for another the user are
all going to be authenticated users anyway, so...
Kev"Kevin O'Donovan" <kev1609@.community.nospam> wrote in message
news:uScqcSAsGHA.4208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I've written an application in VB.Net 2005. One of its requirements is to
> display a report which is hosted on our report server, and to let people
> subscribe to this report (to get a copy once a week by email, for
> example). To do this I have created a windows form containing a web
> browser control, and set the URL of that control to the URL of the report,
> which happens to be:
> http://10.9.200.18/Reports/Pages/Report.aspx?ItemPath=%2fBunker+Management+Reports%2fActive+Bunker+Schemes+Due+To+Expire
> Whenever I open that form I get prompted for my windows credentials. Also,
> if I go straight to that URL in a web browser, I also get prompted for my
> credentials. However, if I start from
> http://10.9.200.18/Reports I can navigate to the same report without being
> prompted for my credentials.
> What can I do to be able to jump straight to the report without being
> prompted for credentials? I switched on the option to enable anonymous
> access for the web site itself (this is an internal web server), which did
> the trick, but then I couldn't access the report server via either
> http://10.9.200.18/Reports - took me to the report server home page, but
> didn't list any contents
> or
> http://10.9.200.18/ReportServer - told me that the anonymous user
> account didn't have permission to access the resource.
> Enabling anonymous access wasn't exactly ideal, anyway. For one thing it
> applied to the whole site, not just the report, and for another the user
> are all going to be authenticated users anyway, so...
> Kev
Okay, solved this ourselves, though if anyone can explain why, I'd be
grateful. When I refer to the web server via its IP address I get prompted
for credentials. When I refer to it by its DNS name I don't get prompted. I
can resolve my immediate problem by using the name in the URL, and adding
that name to the remote user's site's DNS, but I'd like to understand why
the two URLs are treated differently?
Kevin

Auditing SP Execute.

To meet audit requirements, I have to maintain an audit log of when a
particular sp was execute and by whom. I am not willing to use the c2
option for just one stored procedure. There are 2 users who can run
the stored procedure from enterprise manager or query analyser (others
run it from an app). Any suggetions?
Thank You,
-pranayYou can run a trace that is filtered only on this sp. That is the easiest
and least expensive way. Or you would have to buy one of the 3rd party log
tools to view the contents of the log.
Andrew J. Kelly SQL MVP
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
> To meet audit requirements, I have to maintain an audit log of when a
> particular sp was execute and by whom. I am not willing to use the c2
> option for just one stored procedure. There are 2 users who can run
> the stored procedure from enterprise manager or query analyser (others
> run it from an app). Any suggetions?
> Thank You,
> -pranay
>|||I want to capture the user name and the time (using sql). i dont want
to run profiler for ever. When someone runs the sp may be i can get
the userid and insert it into a table.|||Without monitoring the activity with either trace or a 3rd party tool there
is no way to do this in Sql2000.
Andrew J. Kelly SQL MVP
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109187761.063534.20330@.z14g2000cwz.googlegroups.com...
>I want to capture the user name and the time (using sql). i dont want
> to run profiler for ever. When someone runs the sp may be i can get
> the userid and insert it into a table.
>|||As long as the users only have execute permissions on the proc i.e. they
can't change it, then just add some code at the start of the proc to log to
a table passing getdate() and suser_sname() to capture the time and user
executing the proc. This assumes that the app doesn't use a single service
account (in which case you won't be able to get the user name without
modifying the app to stuff it into context_info)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
> To meet audit requirements, I have to maintain an audit log of when a
> particular sp was execute and by whom. I am not willing to use the c2
> option for just one stored procedure. There are 2 users who can run
> the stored procedure from enterprise manager or query analyser (others
> run it from an app). Any suggetions?
> Thank You,
> -pranay
>|||Andrew J. Kelly wrote:
> Without monitoring the activity with either trace or a 3rd party tool ther
e
> is no way to do this in Sql2000.
>
Of course he can.
create table AuditLog (WhenItRan datetime, WhoRanIt sysname)
go
Add to beginning of SP:
insert into AuditLog values (getdate(), suser_sname())|||Ahh yes. My original reading was he was looking to trace the sp_executesql
sp but I think I was mistaken. If it is a user sp then sure.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OcM6uPfGFHA.3076@.tk2msftngp13.phx.gbl...
> As long as the users only have execute permissions on the proc i.e. they
> can't change it, then just add some code at the start of the proc to log
> to a table passing getdate() and suser_sname() to capture the time and
> user executing the proc. This assumes that the app doesn't use a single
> service account (in which case you won't be able to get the user name
> without modifying the app to stuff it into context_info)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Pranay Pandya" <ppandya@.gmail.com> wrote in message
> news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
>|||Thank you Every for the posts. I am going to create the audit table and
log it.
Andrew J. Kelly wrote:
> Ahh yes. My original reading was he was looking to trace the
sp_executesql[vbcol=seagreen]
> sp but I think I was mistaken. If it is a user sp then sure.
> --
> Andrew J. Kelly SQL MVP
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OcM6uPfGFHA.3076@.tk2msftngp13.phx.gbl...
they[vbcol=seagreen]
to log[vbcol=seagreen]
and[vbcol=seagreen]
single[vbcol=seagreen]
name[vbcol=seagreen]
when a[vbcol=seagreen]
the c2[vbcol=seagreen]
run[vbcol=seagreen]
(others[vbcol=seagreen]

Audit Records

One of the requirements for an application I helpping to develop is
providing audit records of add, updates and deletes including the user who
added, updated and deleted. This application is being sold commerically to
users who have different requirements including not needing audit records.
I was thinking that this could best be done using triggers, having first
requiring the the userid be updated each time. The trigger could simply
copy the row to an audit table for each insert and update. If the user does
not require auditing, don't install the triggers. If the user wishes audit
records only when certain columns have chanaged, then use the if
updated(column_name) logic.
It's the deletes I am having a problem with. I can certainly copy deleted
rows to the audit table, but how would I be able to include the userid of
the user initiating the delete?Inside your delete trigger, you can use USER_NAME() when you go to insert
the deleted row into your audit table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
news:eun5$ZYMFHA.2384@.tk2msftngp13.phx.gbl...
One of the requirements for an application I helpping to develop is
providing audit records of add, updates and deletes including the user who
added, updated and deleted. This application is being sold commerically to
users who have different requirements including not needing audit records.
I was thinking that this could best be done using triggers, having first
requiring the the userid be updated each time. The trigger could simply
copy the row to an audit table for each insert and update. If the user does
not require auditing, don't install the triggers. If the user wishes audit
records only when certain columns have chanaged, then use the if
updated(column_name) logic.
It's the deletes I am having a problem with. I can certainly copy deleted
rows to the audit table, but how would I be able to include the userid of
the user initiating the delete?|||Thanks for the reply. All of the actions are going through an ISAPI DLL
with using standard user id and password. The user's id (which is an
application id and not a MS SQL Server login/userid) is passed on to the
database server as just another data element.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uKURYdYMFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Inside your delete trigger, you can use USER_NAME() when you go to insert
> the deleted row into your audit table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
> news:eun5$ZYMFHA.2384@.tk2msftngp13.phx.gbl...
> One of the requirements for an application I helpping to develop is
> providing audit records of add, updates and deletes including the user who
> added, updated and deleted. This application is being sold commerically
> to
> users who have different requirements including not needing audit records.
> I was thinking that this could best be done using triggers, having first
> requiring the the userid be updated each time. The trigger could simply
> copy the row to an audit table for each insert and update. If the user
> does
> not require auditing, don't install the triggers. If the user wishes
> audit
> records only when certain columns have chanaged, then use the if
> updated(column_name) logic.
> It's the deletes I am having a problem with. I can certainly copy deleted
> rows to the audit table, but how would I be able to include the userid of
> the user initiating the delete?
>|||In that case, you'll have to manage that through a stored proc whereby you
feed the proc the user info.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
news:uuTXcoYMFHA.3336@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. All of the actions are going through an ISAPI DLL
with using standard user id and password. The user's id (which is an
application id and not a MS SQL Server login/userid) is passed on to the
database server as just another data element.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uKURYdYMFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Inside your delete trigger, you can use USER_NAME() when you go to insert
> the deleted row into your audit table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
> news:eun5$ZYMFHA.2384@.tk2msftngp13.phx.gbl...
> One of the requirements for an application I helpping to develop is
> providing audit records of add, updates and deletes including the user who
> added, updated and deleted. This application is being sold commerically
> to
> users who have different requirements including not needing audit records.
> I was thinking that this could best be done using triggers, having first
> requiring the the userid be updated each time. The trigger could simply
> copy the row to an audit table for each insert and update. If the user
> does
> not require auditing, don't install the triggers. If the user wishes
> audit
> records only when certain columns have chanaged, then use the if
> updated(column_name) logic.
> It's the deletes I am having a problem with. I can certainly copy deleted
> rows to the audit table, but how would I be able to include the userid of
> the user initiating the delete?
>|||That is along the lines that I was thinking. The stored procedure designed
to delete a row would first update the UpdatedBy column with the user id of
the user requesting the deletion. The proc would then delete the row. A
trigger could then copy the row to an audit table.
I was wanting to know if there was a better way and/or more standard way of
doing this.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eGGQuuYMFHA.2420@.TK2MSFTNGP10.phx.gbl...
> In that case, you'll have to manage that through a stored proc whereby you
> feed the proc the user info.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
> news:uuTXcoYMFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. All of the actions are going through an ISAPI DLL
> with using standard user id and password. The user's id (which is an
> application id and not a MS SQL Server login/userid) is passed on to the
> database server as just another data element.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uKURYdYMFHA.2680@.TK2MSFTNGP09.phx.gbl...
>