Authentication over the internet

I am building an asp.net app that will use reporting services to show reports within the application. Users login to the application and when they need to see a report I use web services to render the report. The asp.net app and reporting services are on the same windows 2003 server (not using active directory).
Because reporting services uses Windows authentication and does not allow anonymous access, I have created a windows account (called "RSUser") that has access to my reports. When the user runs a report, I pass in the credentials for this windows account like this...
rs.Credentials = New System.Net.NetworkCredential("RSUser", "password", "domain")
This all works, and the report renders using the permissions from RSUser. The problem is that all the reports use treeviews for drill-down (and some use drill-through). When you expand a drill down you are prompted for a windows login. I think this is because this postback is now coming from the client PC, instead if from the asp.net app (i.e. on the server), and so reporting services needs to anthenticate this new user.
The only solution that I have found for this is developing a security extension for reporting services...
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
... but this seems like overkill and a very complicated process, and Microsoft says in the article that this is not fully tested and should not be used in a production environment (but that where I need it for).
Does anyone have a solution ?
Craig HBJust a thought: Have you tried to setup a individual Application pool that works with your RSUser Account?
"Craig HB" wrote:
> I am building an asp.net app that will use reporting services to show reports within the application. Users login to the application and when they need to see a report I use web services to render the report. The asp.net app and reporting services are on the same windows 2003 server (not using active directory).
> Because reporting services uses Windows authentication and does not allow anonymous access, I have created a windows account (called "RSUser") that has access to my reports. When the user runs a report, I pass in the credentials for this windows account like this...
> rs.Credentials = New System.Net.NetworkCredential("RSUser", "password", "domain")
> This all works, and the report renders using the permissions from RSUser. The problem is that all the reports use treeviews for drill-down (and some use drill-through). When you expand a drill down you are prompted for a windows login. I think this is because this postback is now coming from the client PC, instead if from the asp.net app (i.e. on the server), and so reporting services needs to anthenticate this new user.
> The only solution that I have found for this is developing a security extension for reporting services...
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> ... but this seems like overkill and a very complicated process, and Microsoft says in the article that this is not fully tested and should not be used in a production environment (but that where I need it for).
> Does anyone have a solution ?
> Craig HB|||Craig,
You are right. You get prompted because the drilldown and drillthough
interactive features require URL acccess and request goes out on the client
side of the application.
In a nutshell, if your reports have interactive features you need to go for
URL access. For Internet-oriented apps this means writing a custom security
extension. It is not that involved to write and I have deployed an
application that uses a custom security extension in a production
environment. There are some gotchas to avoid but in general my experience
writing custom security extensions have been positive and you will learn a
lot about how RS handles authentication and authorization.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Gash" <Gash@.discussions.microsoft.com> wrote in message
news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> Just a thought: Have you tried to setup a individual Application pool that
works with your RSUser Account?
> "Craig HB" wrote:
> > I am building an asp.net app that will use reporting services to show
reports within the application. Users login to the application and when they
need to see a report I use web services to render the report. The asp.net
app and reporting services are on the same windows 2003 server (not using
active directory).
> >
> > Because reporting services uses Windows authentication and does not
allow anonymous access, I have created a windows account (called "RSUser")
that has access to my reports. When the user runs a report, I pass in the
credentials for this windows account like this...
> >
> > rs.Credentials = New System.Net.NetworkCredential("RSUser", "password",
"domain")
> >
> > This all works, and the report renders using the permissions from
RSUser. The problem is that all the reports use treeviews for drill-down
(and some use drill-through). When you expand a drill down you are prompted
for a windows login. I think this is because this postback is now coming
from the client PC, instead if from the asp.net app (i.e. on the server),
and so reporting services needs to anthenticate this new user.
> >
> > The only solution that I have found for this is developing a security
extension for reporting services...
> >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> >
> > ... but this seems like overkill and a very complicated process, and
Microsoft says in the article that this is not fully tested and should not
be used in a production environment (but that where I need it for).
> >
> > Does anyone have a solution ?
> >
> > Craig HB|||Start here
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
--
Hope this helps.
----
Teo Lachev, 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/
----
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> Is there any sample code for writing a custom security extension? I have
> already validated the user and I want to provide url access to the report
> server.
> "Teo" wrote:
> > Craig,
> >
> > You are right. You get prompted because the drilldown and drillthough
> > interactive features require URL acccess and request goes out on the
client
> > side of the application.
> >
> > In a nutshell, if your reports have interactive features you need to go
for
> > URL access. For Internet-oriented apps this means writing a custom
security
> > extension. It is not that involved to write and I have deployed an
> > application that uses a custom security extension in a production
> > environment. There are some gotchas to avoid but in general my
experience
> > writing custom security extensions have been positive and you will learn
a
> > lot about how RS handles authentication and authorization.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > Just a thought: Have you tried to setup a individual Application pool
that
> > works with your RSUser Account?
> > >
> > > "Craig HB" wrote:
> > >
> > > > I am building an asp.net app that will use reporting services to
show
> > reports within the application. Users login to the application and when
they
> > need to see a report I use web services to render the report. The
asp.net
> > app and reporting services are on the same windows 2003 server (not
using
> > active directory).
> > > >
> > > > Because reporting services uses Windows authentication and does not
> > allow anonymous access, I have created a windows account (called
"RSUser")
> > that has access to my reports. When the user runs a report, I pass in
the
> > credentials for this windows account like this...
> > > >
> > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
"password",
> > "domain")
> > > >
> > > > This all works, and the report renders using the permissions from
> > RSUser. The problem is that all the reports use treeviews for drill-down
> > (and some use drill-through). When you expand a drill down you are
prompted
> > for a windows login. I think this is because this postback is now coming
> > from the client PC, instead if from the asp.net app (i.e. on the
server),
> > and so reporting services needs to anthenticate this new user.
> > > >
> > > > The only solution that I have found for this is developing a
security
> > extension for reporting services...
> > > >
> > > >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > >
> > > > ... but this seems like overkill and a very complicated process, and
> > Microsoft says in the article that this is not fully tested and should
not
> > be used in a production environment (but that where I need it for).
> > > >
> > > > Does anyone have a solution ?
> > > >
> > > > Craig HB
> >
> >
> >|||I have seen this article and it is good if I wanted to build a standalone
application to allow access to the report server. However, i have an existing
application with forms authentication in which I want to embed url access to
the report server. I was hoping that there would be code samples or an
article for this particular issue. I don't need to present another form to
the user to capture credentials. Can i use my existing forms authentication
ticket or do I need to create a new one. Do I call the LogonUser webservice
to create a cookie for a user that has been created on the report manager. It
seems like there are a lot of people trying to solve the same problem, but
not too many examples.
"Teo Lachev" wrote:
> Start here
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> --
> Hope this helps.
> ----
> Teo Lachev, 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/
> ----
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> > Is there any sample code for writing a custom security extension? I have
> > already validated the user and I want to provide url access to the report
> > server.
> >
> > "Teo" wrote:
> >
> > > Craig,
> > >
> > > You are right. You get prompted because the drilldown and drillthough
> > > interactive features require URL acccess and request goes out on the
> client
> > > side of the application.
> > >
> > > In a nutshell, if your reports have interactive features you need to go
> for
> > > URL access. For Internet-oriented apps this means writing a custom
> security
> > > extension. It is not that involved to write and I have deployed an
> > > application that uses a custom security extension in a production
> > > environment. There are some gotchas to avoid but in general my
> experience
> > > writing custom security extensions have been positive and you will learn
> a
> > > lot about how RS handles authentication and authorization.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > http://www.prologika.com
> > >
> > >
> > > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > > Just a thought: Have you tried to setup a individual Application pool
> that
> > > works with your RSUser Account?
> > > >
> > > > "Craig HB" wrote:
> > > >
> > > > > I am building an asp.net app that will use reporting services to
> show
> > > reports within the application. Users login to the application and when
> they
> > > need to see a report I use web services to render the report. The
> asp.net
> > > app and reporting services are on the same windows 2003 server (not
> using
> > > active directory).
> > > > >
> > > > > Because reporting services uses Windows authentication and does not
> > > allow anonymous access, I have created a windows account (called
> "RSUser")
> > > that has access to my reports. When the user runs a report, I pass in
> the
> > > credentials for this windows account like this...
> > > > >
> > > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
> "password",
> > > "domain")
> > > > >
> > > > > This all works, and the report renders using the permissions from
> > > RSUser. The problem is that all the reports use treeviews for drill-down
> > > (and some use drill-through). When you expand a drill down you are
> prompted
> > > for a windows login. I think this is because this postback is now coming
> > > from the client PC, instead if from the asp.net app (i.e. on the
> server),
> > > and so reporting services needs to anthenticate this new user.
> > > > >
> > > > > The only solution that I have found for this is developing a
> security
> > > extension for reporting services...
> > > > >
> > > > >
> > >
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > > >
> > > > > ... but this seems like overkill and a very complicated process, and
> > > Microsoft says in the article that this is not fully tested and should
> not
> > > be used in a production environment (but that where I need it for).
> > > > >
> > > > > Does anyone have a solution ?
> > > > >
> > > > > Craig HB
> > >
> > >
> > >
>
>|||> Can i use my existing forms authentication
> ticket or do I need to create a new one.
No, you cannot use your app Forms Authentication ticket and you don't have
to have another logon form. Instead, your application needs to call the RS
LogonUser SOAP API once it authenticates the user. You will end up with two
authentication tickets (cookies) but this shouldn't be too much of an issue.
The MS article should be good enough to address you scenario. You just need
to understand how RS Forms Authentication works by debugging the extension.
I have a two-part article in the works for a magazine about Forms
Authentication. Unfortunately, judging by the editors speed, it won't make
it before the end of the year. Meanwhile, you can check the other threads
on this topic. It's been discussed many times.
--
Hope this helps.
----
Teo Lachev, 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/
----
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:3F687097-1790-4FF9-B8CB-0A163BF3074C@.microsoft.com...
> I have seen this article and it is good if I wanted to build a standalone
> application to allow access to the report server. However, i have an
existing
> application with forms authentication in which I want to embed url access
to
> the report server. I was hoping that there would be code samples or an
> article for this particular issue. I don't need to present another form to
> the user to capture credentials. Can i use my existing forms
authentication
> ticket or do I need to create a new one. Do I call the LogonUser
webservice
> to create a cookie for a user that has been created on the report manager.
It
> seems like there are a lot of people trying to solve the same problem, but
> not too many examples.
> "Teo Lachev" wrote:
> > Start here
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, 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/
> > ----
> >
> > "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> > news:3A2F7D63-C267-4CED-A5CC-4B42186B98B6@.microsoft.com...
> > > Is there any sample code for writing a custom security extension? I
have
> > > already validated the user and I want to provide url access to the
report
> > > server.
> > >
> > > "Teo" wrote:
> > >
> > > > Craig,
> > > >
> > > > You are right. You get prompted because the drilldown and
drillthough
> > > > interactive features require URL acccess and request goes out on the
> > client
> > > > side of the application.
> > > >
> > > > In a nutshell, if your reports have interactive features you need to
go
> > for
> > > > URL access. For Internet-oriented apps this means writing a custom
> > security
> > > > extension. It is not that involved to write and I have deployed an
> > > > application that uses a custom security extension in a production
> > > > environment. There are some gotchas to avoid but in general my
> > experience
> > > > writing custom security extensions have been positive and you will
learn
> > a
> > > > lot about how RS handles authentication and authorization.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > http://www.prologika.com
> > > >
> > > >
> > > > "Gash" <Gash@.discussions.microsoft.com> wrote in message
> > > > news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > > > > Just a thought: Have you tried to setup a individual Application
pool
> > that
> > > > works with your RSUser Account?
> > > > >
> > > > > "Craig HB" wrote:
> > > > >
> > > > > > I am building an asp.net app that will use reporting services to
> > show
> > > > reports within the application. Users login to the application and
when
> > they
> > > > need to see a report I use web services to render the report. The
> > asp.net
> > > > app and reporting services are on the same windows 2003 server (not
> > using
> > > > active directory).
> > > > > >
> > > > > > Because reporting services uses Windows authentication and does
not
> > > > allow anonymous access, I have created a windows account (called
> > "RSUser")
> > > > that has access to my reports. When the user runs a report, I pass
in
> > the
> > > > credentials for this windows account like this...
> > > > > >
> > > > > > rs.Credentials = New System.Net.NetworkCredential("RSUser",
> > "password",
> > > > "domain")
> > > > > >
> > > > > > This all works, and the report renders using the permissions
from
> > > > RSUser. The problem is that all the reports use treeviews for
drill-down
> > > > (and some use drill-through). When you expand a drill down you are
> > prompted
> > > > for a windows login. I think this is because this postback is now
coming
> > > > from the client PC, instead if from the asp.net app (i.e. on the
> > server),
> > > > and so reporting services needs to anthenticate this new user.
> > > > > >
> > > > > > The only solution that I have found for this is developing a
> > security
> > > > extension for reporting services...
> > > > > >
> > > > > >
> > > >
> >
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > > > > >
> > > > > > ... but this seems like overkill and a very complicated process,
and
> > > > Microsoft says in the article that this is not fully tested and
should
> > not
> > > > be used in a production environment (but that where I need it for).
> > > > > >
> > > > > > Does anyone have a solution ?
> > > > > >
> > > > > > Craig HB
> > > >
> > > >
> > > >
> >
> >
> >|||Is there any sample code for writing a custom security extension? I have
already validated the user and I want to provide url access to the report
server.
"Teo" wrote:
> Craig,
> You are right. You get prompted because the drilldown and drillthough
> interactive features require URL acccess and request goes out on the client
> side of the application.
> In a nutshell, if your reports have interactive features you need to go for
> URL access. For Internet-oriented apps this means writing a custom security
> extension. It is not that involved to write and I have deployed an
> application that uses a custom security extension in a production
> environment. There are some gotchas to avoid but in general my experience
> writing custom security extensions have been positive and you will learn a
> lot about how RS handles authentication and authorization.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Gash" <Gash@.discussions.microsoft.com> wrote in message
> news:FFF038F5-4A21-4DFA-846C-6A3A84683D2D@.microsoft.com...
> > Just a thought: Have you tried to setup a individual Application pool that
> works with your RSUser Account?
> >
> > "Craig HB" wrote:
> >
> > > I am building an asp.net app that will use reporting services to show
> reports within the application. Users login to the application and when they
> need to see a report I use web services to render the report. The asp.net
> app and reporting services are on the same windows 2003 server (not using
> active directory).
> > >
> > > Because reporting services uses Windows authentication and does not
> allow anonymous access, I have created a windows account (called "RSUser")
> that has access to my reports. When the user runs a report, I pass in the
> credentials for this windows account like this...
> > >
> > > rs.Credentials = New System.Net.NetworkCredential("RSUser", "password",
> "domain")
> > >
> > > This all works, and the report renders using the permissions from
> RSUser. The problem is that all the reports use treeviews for drill-down
> (and some use drill-through). When you expand a drill down you are prompted
> for a windows login. I think this is because this postback is now coming
> from the client PC, instead if from the asp.net app (i.e. on the server),
> and so reporting services needs to anthenticate this new user.
> > >
> > > The only solution that I have found for this is developing a security
> extension for reporting services...
> > >
> > >
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs_topic3
> > >
> > > ... but this seems like overkill and a very complicated process, and
> Microsoft says in the article that this is not fully tested and should not
> be used in a production environment (but that where I need it for).
> > >
> > > Does anyone have a solution ?
> > >
> > > Craig HB
>
>|||Teo. Is it possible to use web forms authentication with the standard
edition of RS?
If not, I'm guessing there is no other way to use the viewer over the
Internet..
Thanks, AHH
BTW: I bought your book - best one out there..|||Thanks. No, extending RS requires Enterprise Edition. Sorry.
How about generating reports on the server side of the app and sacrificing
the interactive features and the toolbar?
--
Hope this helps.
----
Teo Lachev, 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/
----
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:13CACEA1-BD84-4D6A-BB25-63D43E0F56A8@.microsoft.com...
> Teo. Is it possible to use web forms authentication with the standard
> edition of RS?
> If not, I'm guessing there is no other way to use the viewer over the
> Internet..
> Thanks, AHH
> BTW: I bought your book - best one out there..

authentication Mode

I am developing a client/server application using sql server as the back end
.
This app must allow for two login scenarios other than the usual one
user/one workstation. First, the app must allow users to log in from any
machine on the network even when using a machine running under a different
users login. Second, the app must be available to someone that does not hav
e
a windows login, eg a field worker that needs to access the app occasioniall
y
using a machine running under a different users login. For these reasons, I
have been using mixed mode authentication which does the job. Since all the
documentation seems to recommend Windows authentication mode and mixed mode
is for backward compability, am I missing something here? Can I handle the
two scenarios, particularly the second using Windows Authentication?
JBConsider using an Application Role rather than user level security.
Look in Books Online for "Establishing Application Security and Application
Roles".
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JB" <JB@.discussions.microsoft.com> wrote in message
news:AA8A22DC-D007-4CBE-BFF8-758693B9FCC0@.microsoft.com...
>I am developing a client/server application using sql server as the back
>end.
> This app must allow for two login scenarios other than the usual one
> user/one workstation. First, the app must allow users to log in from any
> machine on the network even when using a machine running under a different
> users login. Second, the app must be available to someone that does not
> have
> a windows login, eg a field worker that needs to access the app
> occasionially
> using a machine running under a different users login. For these reasons,
> I
> have been using mixed mode authentication which does the job. Since all
> the
> documentation seems to recommend Windows authentication mode and mixed
> mode
> is for backward compability, am I missing something here? Can I handle
> the
> two scenarios, particularly the second using Windows Authentication?
> JB|||That's hard to say as this line doesn't make sense:
"Second, the app must be available to someone that does not
have a windows login, eg a field worker that needs to
access the app occasionially using a machine running under
a different users login"
So what login is the "different users login" - but the user
doesn't have a login?
It really depends on what login is being used, if the field
work is accessing a machine in a domain, if it's multiple
domains, depends on trusts that may or may not be setup,
etc.
In terms of the other issue, Machines and logins are two
different things. If I have my windows login setup for
access to a SQL Server box in my domain, it doesn't matter
what machine I use. If I login into the network, that's the
credentials that are used no matter what machine I may be
logged into.
If users are logging into the domain with all different
logins, accessing network resources with various logins then
you have a security mess at the network level which will
lead to security messes in SQL Server as well when
implementing Windows authentication.
-Sue
On Fri, 22 Sep 2006 10:17:01 -0700, JB
<JB@.discussions.microsoft.com> wrote:

>I am developing a client/server application using sql server as the back en
d.
> This app must allow for two login scenarios other than the usual one
>user/one workstation. First, the app must allow users to log in from any
>machine on the network even when using a machine running under a different
>users login. Second, the app must be available to someone that does not ha
ve
>a windows login, eg a field worker that needs to access the app occasionial
ly
>using a machine running under a different users login. For these reasons,
I
>have been using mixed mode authentication which does the job. Since all th
e
>documentation seems to recommend Windows authentication mode and mixed mode
>is for backward compability, am I missing something here? Can I handle the
>two scenarios, particularly the second using Windows Authentication?
>JB

Authentication IIS and SSRS

I have two servers IIS Server and SQL2005.
I have web app using ReportViewer on IIS Server to view reports on SQL
Server SSRS.
Both servers are on domain1.
Authentication was not working so on IIS web app in web.config in system.web
I say identity impersonate="true" userName="domain1\username"
password="password" then it works fine -- but is this correct way to do? I
don't like to have to have an account in there that has admin privs -- I
thought it should be just impersonate="true" and not have to send a domain
username/password?
I read many things on nntp about this topic but was unable to finad anything
to work except for the way I have it above.
Thank you!Why dont you enable anonymouse access and give everyone access in the
report server.
dev648237923 wrote:
> I have two servers IIS Server and SQL2005.
> I have web app using ReportViewer on IIS Server to view reports on SQL
> Server SSRS.
> Both servers are on domain1.
> Authentication was not working so on IIS web app in web.config in system.web
> I say identity impersonate="true" userName="domain1\username"
> password="password" then it works fine -- but is this correct way to do? I
> don't like to have to have an account in there that has admin privs -- I
> thought it should be just impersonate="true" and not have to send a domain
> username/password?
> I read many things on nntp about this topic but was unable to finad anything
> to work except for the way I have it above.
> Thank you!|||The Report Server is not exposed to the Internet -- only my web application
can get at it -- more secure that way (so my IIS server is public but the
SQL is not). I followed what Steve said and it worked great!
Thanks.
"sunil.jaikumar" <sunil.jaikumar@.gmail.com> wrote in message
news:1152863352.300856.43580@.m73g2000cwd.googlegroups.com...
> Why dont you enable anonymouse access and give everyone access in the
> report server.
> dev648237923 wrote:
>> I have two servers IIS Server and SQL2005.
>> I have web app using ReportViewer on IIS Server to view reports on SQL
>> Server SSRS.
>> Both servers are on domain1.
>> Authentication was not working so on IIS web app in web.config in
>> system.web
>> I say identity impersonate="true" userName="domain1\username"
>> password="password" then it works fine -- but is this correct way to do?
>> I
>> don't like to have to have an account in there that has admin privs -- I
>> thought it should be just impersonate="true" and not have to send a
>> domain
>> username/password?
>> I read many things on nntp about this topic but was unable to finad
>> anything
>> to work except for the way I have it above.
>> Thank you!
>|||" I followed what Steve said and it worked great!"
Does this refer to using local reports, as was stated in your other
post?
I've been prototyping using the impersonation and it works, but I don't
know what the security requirement is eventually going to be. I don't
mind cutting the RS server out of the equation.
dev648237923 wrote:
> The Report Server is not exposed to the Internet -- only my web application
> can get at it -- more secure that way (so my IIS server is public but the
> SQL is not). I followed what Steve said and it worked great!
> Thanks.
> "sunil.jaikumar" <sunil.jaikumar@.gmail.com> wrote in message
> news:1152863352.300856.43580@.m73g2000cwd.googlegroups.com...
> > Why dont you enable anonymouse access and give everyone access in the
> > report server.
> >
> > dev648237923 wrote:
> >> I have two servers IIS Server and SQL2005.
> >> I have web app using ReportViewer on IIS Server to view reports on SQL
> >> Server SSRS.
> >> Both servers are on domain1.
> >>
> >> Authentication was not working so on IIS web app in web.config in
> >> system.web
> >> I say identity impersonate="true" userName="domain1\username"
> >> password="password" then it works fine -- but is this correct way to do?
> >> I
> >> don't like to have to have an account in there that has admin privs -- I
> >> thought it should be just impersonate="true" and not have to send a
> >> domain
> >> username/password?
> >>
> >> I read many things on nntp about this topic but was unable to finad
> >> anything
> >> to work except for the way I have it above.
> >>
> >> Thank you!
> >|||> Does this refer to using local reports, as was stated in your other
> post?
I followed what Steve said to get IIS authentication working to SSRS server.
I'm not using local Reports anymore (reason is that I need to be able to use
some of the features only available in Report Server -- see below for some
goo dlinks comparing).
Some links on Local vs Report Server:
http://msdn2.microsoft.com/en-us/library/ms251704.aspx
http://www.devx.com/dotnet/Article/30424/0/page/4|||Anonymous access seems like it would work but wait until you try to
administer RS. Since you are anonymous there is no such thing as
administrator rights. Oops. So then to administer it you have to switch off
of anonymous and then turn it back on. Most people would not want to do
that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sunil.jaikumar" <sunil.jaikumar@.gmail.com> wrote in message
news:1152863352.300856.43580@.m73g2000cwd.googlegroups.com...
> Why dont you enable anonymouse access and give everyone access in the
> report server.
> dev648237923 wrote:
>> I have two servers IIS Server and SQL2005.
>> I have web app using ReportViewer on IIS Server to view reports on SQL
>> Server SSRS.
>> Both servers are on domain1.
>> Authentication was not working so on IIS web app in web.config in
>> system.web
>> I say identity impersonate="true" userName="domain1\username"
>> password="password" then it works fine -- but is this correct way to do?
>> I
>> don't like to have to have an account in there that has admin privs -- I
>> thought it should be just impersonate="true" and not have to send a
>> domain
>> username/password?
>> I read many things on nntp about this topic but was unable to finad
>> anything
>> to work except for the way I have it above.
>> Thank you!
>

Authenticating to Native SQL 2005 Web Services

Hi,
In a SOA architecture, is it possible for an Internet (NOT intranet) user
of a Windows Forms app to authenticate with the Native Web Services of SQL
Server 2005 using Windows Integrated Authentication? The users would NOT be
in the same domain or in a trusted domain. If so, could you point me to som
e
configuration instructions please.
Thanks.
--
JohnHello John,
Based on my scope, Integrated Windows Authentication works only in
scenarios where the client computer can contact a domain controller to
validate their credentials or keep a circuit open for point to point
connection to server. In most firewall configurations, this scenario is not
possible and not desirable. Though it is not IIS, the requirement are
similar:
IIS Authentication and Security for Internet Developers
http://msdn.microsoft.com/library/d...-us/dnauth/html
/dnauth_security.asp
264921.KB.EN-US INFO: How IIS Authenticates Browser Clients
http://support.microsoft.com/defaul...KB;EN-US;264921
Internet Information Services 5.0 Authentication Methods
http://www.windowsitpro.com/article...?articleid=8443
IIS 101: The Basics of IIS Authentication
http://www.iisadministrator.com/Art...ArticleID=15843
As for authentication of Web service in SQL 2005, it requests client to
send credential to server directly. Otherwise it fails.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Authenticating to Native SQL 2005 Web Services
| thread-index: AcXeqMzup44GBeNiRta2Kazq0yKi6Q==
| X-WBNR-Posting-Host: 71.192.119.68
| From: "examnotes" <Jthayer@.online.nospam>
| Subject: Authenticating to Native SQL 2005 Web Services
| Date: Mon, 31 Oct 2005 21:55:02 -0800
| Lines: 9
| Message-ID: <E83A79C0-D7BB-4C3A-9435-166B06A081FA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.security
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:6416
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| Hi,
| In a SOA architecture, is it possible for an Internet (NOT intranet)
user
| of a Windows Forms app to authenticate with the Native Web Services of
SQL
| Server 2005 using Windows Integrated Authentication? The users would NOT
be
| in the same domain or in a trusted domain. If so, could you point me to
some
| configuration instructions please.
| Thanks.
| --
| John
|

audit tables, delete triggers, and asp.net

i'm in a bit of a bind at work. if anyone could help, i'd greatly
appreciate it.

i have a web app connecting to a sql server using sql server
authentication. let's say, for example, my login/password is
dbUser/dbUser. the web app however, is using windows authentication.
so if I am logged into the network as 'DOMAIN\Eric', when I access my
web app, my web app knows that I am 'DOMAIN\Eric'. but to the sql
server db, I am user 'dbUser'.

now, i for each table i have, i need to implement an audit table to
record all updates, inserts, deletes that occur against it. i was
going to do so with triggers. this is all fine for selects, inserts,
and updates. for each table, i have an updatedby and an updatedate.

for example, let's say i have a table:

create table blah
(
id int,
col1 varchar(10),
updatedby varchar(30),
updatedate datetime
)

and corresponding audit table:

create audit_blah
(
id int,
blah_id int,
blah_col1 varchar(10),
blah_updatedby varchar(1),
blah_updatedate datetime
)

for update and insert triggers, i can know what to insert into the
updatedby column of audit_blah because it's in a corresponding row in
blah. my web app knows what user is accessing the application, and
can insert that name into blah. blah's trigger will then insert that
name into audit_blah.

however, in the case of a delete, i'm not passing in an 'updatedby',
because i'm deleting. in this situation, how can the trigger know
what user is deleting? the db only knows that sql user 'dbUser' is
deleting, but doesn't know that 'dbUser' is deleting on behalf of
'DOMAIN\Eric'. is there any way for my app to inform the trigger to
access my windows identity without having a corresponding row in the
table from which to pull that info?

obviously, i could have each of my app's users log into SQL server
through Windows authentication; then i could just use SYSTEM_USER.
but let's say, for performance's sake, it'd be better for me to use
one sql server login. (i believe one user works better for connection
pooling purposes.) is there a way to get around this?

(i'm hoping a built-in function exists that solves all my problems.)

suggestions? resources?

any help would be great appreciated.

happy turkeys.

EricHi

You may want to do soft deletes instead (possibly with a garbage collection
job!) or do the deletes through a stored procedure and log them differently.

John
"ecastillo" <encee5@.gmail.com> wrote in message
news:a2f0a8ac.0411252044.5dffaa2@.posting.google.co m...
> i'm in a bit of a bind at work. if anyone could help, i'd greatly
> appreciate it.
> i have a web app connecting to a sql server using sql server
> authentication. let's say, for example, my login/password is
> dbUser/dbUser. the web app however, is using windows authentication.
> so if I am logged into the network as 'DOMAIN\Eric', when I access my
> web app, my web app knows that I am 'DOMAIN\Eric'. but to the sql
> server db, I am user 'dbUser'.
> now, i for each table i have, i need to implement an audit table to
> record all updates, inserts, deletes that occur against it. i was
> going to do so with triggers. this is all fine for selects, inserts,
> and updates. for each table, i have an updatedby and an updatedate.
> for example, let's say i have a table:
> create table blah
> (
> id int,
> col1 varchar(10),
> updatedby varchar(30),
> updatedate datetime
> )
> and corresponding audit table:
> create audit_blah
> (
> id int,
> blah_id int,
> blah_col1 varchar(10),
> blah_updatedby varchar(1),
> blah_updatedate datetime
> )
> for update and insert triggers, i can know what to insert into the
> updatedby column of audit_blah because it's in a corresponding row in
> blah. my web app knows what user is accessing the application, and
> can insert that name into blah. blah's trigger will then insert that
> name into audit_blah.
> however, in the case of a delete, i'm not passing in an 'updatedby',
> because i'm deleting. in this situation, how can the trigger know
> what user is deleting? the db only knows that sql user 'dbUser' is
> deleting, but doesn't know that 'dbUser' is deleting on behalf of
> 'DOMAIN\Eric'. is there any way for my app to inform the trigger to
> access my windows identity without having a corresponding row in the
> table from which to pull that info?
> obviously, i could have each of my app's users log into SQL server
> through Windows authentication; then i could just use SYSTEM_USER.
> but let's say, for performance's sake, it'd be better for me to use
> one sql server login. (i believe one user works better for connection
> pooling purposes.) is there a way to get around this?
> (i'm hoping a built-in function exists that solves all my problems.)
> suggestions? resources?
> any help would be great appreciated.
> happy turkeys.
> Eric|||[posted and mailed, please reply in news]

ecastillo (encee5@.gmail.com) writes:
> however, in the case of a delete, i'm not passing in an 'updatedby',
> because i'm deleting. in this situation, how can the trigger know
> what user is deleting? the db only knows that sql user 'dbUser' is
> deleting, but doesn't know that 'dbUser' is deleting on behalf of
> 'DOMAIN\Eric'. is there any way for my app to inform the trigger to
> access my windows identity without having a corresponding row in the
> table from which to pull that info?

You could use SET CONTEXT_INFO. This command is somewhat tricky to use,
but it's workable. This commands sets the column context_info in
sysprocesses. The value is a binary value. Here is an example:

declare @.bin varbinary(30)
select @.bin = convert(varbinary(30), 'DOMAIN\Eric')
set context_info @.bin
go
select convert(varchar(30), context_info)
from master..sysprocesses where spid = @.@.spid

The web server would do the first part, the trigger the second part.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Audit Design Question

I need to audit Insert, Update and Delete on tables in the database.
But the symin of the app can selectively enable and disable auditing
on tables. So I need to be able to switch the auditing on and off.
Is there any built-in function in SqlServer 2005 that I can use to
track changes?
In the absence of built-in auditing I have come up with two possible
solutions:
1) Pass a patameter called @.bAudit to the stored procedures performing
CRUD tasks and if the parameter is true then insert a row into the
audit table (audit table has the same schema as the main table but with
a few more columns for tracking).
2) Use triggers on Insert, Update and Delete but I can't find out how
to disable triggers at run time.
Can you please point me in the right direction? Thanks.S Chapman,

> 2) Use triggers on Insert, Update and Delete but I can't find out how
> to disable triggers at run time.
Use the statement "alter table".
alter table dbo.t1
disable trigger trigger_name
See BOL for more info.

> 1) Pass a patameter called @.bAudit to the stored procedures performing
> CRUD tasks and if the parameter is true then insert a row into the
> audit table (audit table has the same schema as the main table but with
> a few more columns for tracking).
A possible solution could be having an options table and check the option
inside the trigger.
update conf_options
set audit = 1
where table_name = 'my_table'
go
create trigger tr_mytable on my_table
for insert, update, delete
as
if exists (select * from dbo.conf_options where table_name = 'my_table' and
audit = 1)
begin
-- put here the audit code
end
go
AMB
"S Chapman" wrote:

>
> I need to audit Insert, Update and Delete on tables in the database.
> But the symin of the app can selectively enable and disable auditing
> on tables. So I need to be able to switch the auditing on and off.
> Is there any built-in function in SqlServer 2005 that I can use to
> track changes?
> In the absence of built-in auditing I have come up with two possible
> solutions:
> 1) Pass a patameter called @.bAudit to the stored procedures performing
> CRUD tasks and if the parameter is true then insert a row into the
> audit table (audit table has the same schema as the main table but with
> a few more columns for tracking).
> 2) Use triggers on Insert, Update and Delete but I can't find out how
> to disable triggers at run time.
> Can you please point me in the right direction? Thanks.
>

Attatching ASPNETDB to remote server

Hi there ;

I uploaded my app to host but crashed during membership activities, and i figured out it's cause of ASPNETDB.mdf that is not accessible in remote server, & it's driving me crazy.

As a solution i wanted to know :

is it possible to attatch my db in App_Data to remote server and make use of it just like before?

Please take it into concideration that i've already tried 'aspnet_regsql.exe' and the server gives me error:40 (only local access allowed ).

thanks in advance

No, for the AttacheDBFilename property, currently remote server, HTTP, and UNC path names are not supported. In other words the attached database file must be on the local disk of the SQL machine.|||

thanks lori ,

but i didn't say that i'm goin to set a http or sth alike as db File name . my problem is that my host company doesn't allow remote connections so that i can't use aspnet_regsql.exe .so i just wanted to know that if i send ASPNETDB.mdf to remote server (not necessarily to App_Date folder ) and ask admins to attach it in there, does it work just like the aspnet_regsql.exe ?so that i can then change my connectionstring to the one that my host offers after attaching.

thanks for any help

|||Sorry for misunderstanding Yes, aspnet_sql has nothing to do with database connection for your website, we use aspnet_regsql.exe to create database objects in specified database which can be used for membership. So just make sure connection can be made to the remote SQL Server.|||

So i'll do it .

really thanks for the help

|||

i did that, one final job is to set LocalSqlServer to the new one in the remote server, to do so, follow steps:

ControlPanel > Administrative Tools > Internet Information Services > here choose your website's virtual directory that is somewhere under "Default Web Site"

then right click and choose Properties, on the rightmost tab named "ASP.NET" click the button named "Edit Configuration..." .

on the window that will open, you see the "LocalSqlServer" under "Connection string manager", and it's value is set to the ASPNETDB.mdf that is in the App_Data directory, click on it & edit it to point to the one that is on the remote server .

Generally for those, who aren't allowed to access the db remotely, the best job, i think, is to generate a copy of "ASPNETDB.mdf" locally (In case there is someone else that has taken the name ASPNETDB, change its name) & send it to your remote server and ask them to attach it, then, with the connection string they offer you can have sth like "ASPNETDB.mdf" working remotely.

Thanks lori for the help

Attach to SQL Server 2K5 Express Problem

I am building a web app using VS 2K5 with SQL Server Express as the backend.
The project and database are stored in the default location: My
Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also downloaded and
installed the SQL Server Management Studio Express to be able to manage the
database. The problem I have is that I am unable to attach the database. I
go through the process but the SSMSE won't allow me to navigate to where my
database is. I'm needing to import a table with a lot of data. Is this a
bug? Do I need to temporarily move my database file? Thanks.
hi Phill,
Phill wrote:
> I am building a web app using VS 2K5 with SQL Server Express as the
> backend. The project and database are stored in the default location:
> My Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also
> downloaded and installed the SQL Server Management Studio Express to
> be able to manage the database. The problem I have is that I am
> unable to attach the database. I go through the process but the
> SSMSE won't allow me to navigate to where my database is. I'm
> needing to import a table with a lot of data. Is this a bug? Do I
> need to temporarily move my database file? Thanks.
I do not currently have VS2005 installed, but with SSMSE I was able to
navigate to My Documents\Visual Studio 2005\WebSites\MyWebSiteName (the
\WebSites\MyWebSiteName folders have been actually created by me as, again,
I do not have VS2005 installed on that machine) both with a windows
administrator member (trusted connections) and the "traditional" "sa" SQL
Server login (mixed mode security)
I do not think VS2005 sets some sort of particular ACLs for that folder,
but I can be wrong here..
the account running my SQLExpress instance is LocalSystem..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||When you created the database in that location, you probably did it as a
user instance because that's the Visual Studio default. The user running
Visual Studio has permissions to that directory because he created it. You
are now trying to attach it to the main SQL Server instance instead of the
user instance, The main SQL Express instance is running as Network Service
and more than likely does not have permissions to open files in you
application directory. You can get around this by giving Network Service
read-write permission on the database and log files. Be aware that while
the files are attached to the main instance, your application using the user
instance connection will not be able to access them.
Check this article for an explanation of user instances:
http://msdn.microsoft.com/sql/expres...xpuserinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:1CF63F8E-902B-4AD7-A2F8-B7DA7233D7AE@.microsoft.com...
>I am building a web app using VS 2K5 with SQL Server Express as the
>backend.
> The project and database are stored in the default location: My
> Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also downloaded
> and
> installed the SQL Server Management Studio Express to be able to manage
> the
> database. The problem I have is that I am unable to attach the database.
> I
> go through the process but the SSMSE won't allow me to navigate to where
> my
> database is. I'm needing to import a table with a lot of data. Is this a
> bug? Do I need to temporarily move my database file? Thanks.
|||Thanks, I am now able to connect to the database. It didn't really do me any
good though. I want to import an Access table into it but there aren't any
tools to do so. No ODBC drivers, nothing. So how are we supposed to import
data into SSE2K? Thanks.
"Roger Wolter[MSFT]" wrote:

> When you created the database in that location, you probably did it as a
> user instance because that's the Visual Studio default. The user running
> Visual Studio has permissions to that directory because he created it. You
> are now trying to attach it to the main SQL Server instance instead of the
> user instance, The main SQL Express instance is running as Network Service
> and more than likely does not have permissions to open files in you
> application directory. You can get around this by giving Network Service
> read-write permission on the database and log files. Be aware that while
> the files are attached to the main instance, your application using the user
> instance connection will not be able to access them.
> Check this article for an explanation of user instances:
> http://msdn.microsoft.com/sql/expres...xpuserinst.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:1CF63F8E-902B-4AD7-A2F8-B7DA7233D7AE@.microsoft.com...
>
>
|||Use the upsizing Wizard in access to push the data into SSE
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F00DFB95-4D36-47CC-9A46-41A5A3577D60@.microsoft.com...[vbcol=seagreen]
> Thanks, I am now able to connect to the database. It didn't really do me
> any
> good though. I want to import an Access table into it but there aren't
> any
> tools to do so. No ODBC drivers, nothing. So how are we supposed to
> import
> data into SSE2K? Thanks.
> "Roger Wolter[MSFT]" wrote: