Integrated Security and unauthorzed apps.

Greetings:
If SQL 2000 is configured only to accept Windows authentication, how
do I prevent end-users from connecting to SQL with unauthorized
applications (such as Enterprise Manager)?
I know it's possible to check (although a clever programmer can easily
fool) the calling application name, but to do so within every stored
proc would be onerous.
This has been a head scratcher in our effort to shut off SQL
authentication and removing stored credentials, so all help is
appreciated.
Thanks much
d.Unfortunately, there is nothing within the SQL 2000 product to block
connections/access to objects based upon the application you're using.
You could restrict access to specific machines using IPSec or Firewall
rules etc. So, this could work if you were developing a Web based
application, such that only the IIS machine had permission to connect to
SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Integrated Security

We have SS 2000 and SSRS 2000. SSRS is connecting as a Windows account of "NT
Authority\Network Service". That account is in both RSEXEC roles in
ReportServer and ReportServerTempDB. When we log into the Report Server
website as a domain name (which on the server has admin rights), it works
fine. When we log into ReportServer using SQL Authentication using a testname
that is on the server, we get the home page with no access to our folder. We
added that id to SQL, we have added that user to RS with full access, we have
tried even adding that to the RSEXEC. No change. We also checked IIS for the
Report folder and it is using Windows Integrated Security. One more thing,
the dataset is using the aspnet login. When we make the person an
administrator on the server - it works fine. Of course that options is not
feasible. Any ideas of what we missed?You are confusing two different things. First, who the user is and what the
credentials to retrieve the data for the report are.
This is completely two different things (although they can be the same).
When you go to the website it is using the integrated windows security that
identifies who the user is that is on the computer which is accessing
Reporting Services. This has nothing whatsoever to do with SQL
Authentication. That is strictly for credentials of the data source.
My suggestion is this. First read up on roles. Roles are used to give access
to the folders, reports, reporting functionality like deploying reports and
subscriptions. Plan how you want to organize and give access. What I do
because it is easiest for me, I create a local group that I add individual
domain users and domain groups to. Then I give this group role membership in
Reporting Services.
Next I run SQL Server in mixed mode security (which I think you are already
doing). I setup a login that is a read only login. I use that for the
credentials of the data source. This buys you performance as well. It allows
connection pooling to work its magic.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mgcap" <mgcap@.discussions.microsoft.com> wrote in message
news:93178514-7883-493F-8A18-83614CD56EF5@.microsoft.com...
> We have SS 2000 and SSRS 2000. SSRS is connecting as a Windows account of
> "NT
> Authority\Network Service". That account is in both RSEXEC roles in
> ReportServer and ReportServerTempDB. When we log into the Report Server
> website as a domain name (which on the server has admin rights), it works
> fine. When we log into ReportServer using SQL Authentication using a
> testname
> that is on the server, we get the home page with no access to our folder.
> We
> added that id to SQL, we have added that user to RS with full access, we
> have
> tried even adding that to the RSEXEC. No change. We also checked IIS for
> the
> Report folder and it is using Windows Integrated Security. One more thing,
> the dataset is using the aspnet login. When we make the person an
> administrator on the server - it works fine. Of course that options is not
> feasible. Any ideas of what we missed?|||Bruce, Thanks. That did clear up what we were stumbling with. We did get it
fixed this morning - we had not given security to the home folder. But I
still have one more question for you. We do understand a bit about the roles
(but are still learning) but when you talk about the groups are you talking
about a Windows group on the server or something else?
"Bruce L-C [MVP]" wrote:
> You are confusing two different things. First, who the user is and what the
> credentials to retrieve the data for the report are.
> This is completely two different things (although they can be the same).
> When you go to the website it is using the integrated windows security that
> identifies who the user is that is on the computer which is accessing
> Reporting Services. This has nothing whatsoever to do with SQL
> Authentication. That is strictly for credentials of the data source.
> My suggestion is this. First read up on roles. Roles are used to give access
> to the folders, reports, reporting functionality like deploying reports and
> subscriptions. Plan how you want to organize and give access. What I do
> because it is easiest for me, I create a local group that I add individual
> domain users and domain groups to. Then I give this group role membership in
> Reporting Services.
> Next I run SQL Server in mixed mode security (which I think you are already
> doing). I setup a login that is a read only login. I use that for the
> credentials of the data source. This buys you performance as well. It allows
> connection pooling to work its magic.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mgcap" <mgcap@.discussions.microsoft.com> wrote in message
> news:93178514-7883-493F-8A18-83614CD56EF5@.microsoft.com...
> > We have SS 2000 and SSRS 2000. SSRS is connecting as a Windows account of
> > "NT
> > Authority\Network Service". That account is in both RSEXEC roles in
> > ReportServer and ReportServerTempDB. When we log into the Report Server
> > website as a domain name (which on the server has admin rights), it works
> > fine. When we log into ReportServer using SQL Authentication using a
> > testname
> > that is on the server, we get the home page with no access to our folder.
> > We
> > added that id to SQL, we have added that user to RS with full access, we
> > have
> > tried even adding that to the RSEXEC. No change. We also checked IIS for
> > the
> > Report folder and it is using Windows Integrated Security. One more thing,
> > the dataset is using the aspnet login. When we make the person an
> > administrator on the server - it works fine. Of course that options is not
> > feasible. Any ideas of what we missed?
>
>|||I am talking about a local Windows group. In my case I found it easier to
assign all the different domain groups to a local windows group and then use
that when I was assigning rights in Reporting Services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mgcap" <mgcap@.discussions.microsoft.com> wrote in message
news:D1544B76-99F8-43CD-85F8-9156FBCC6C39@.microsoft.com...
> Bruce, Thanks. That did clear up what we were stumbling with. We did get
> it
> fixed this morning - we had not given security to the home folder. But I
> still have one more question for you. We do understand a bit about the
> roles
> (but are still learning) but when you talk about the groups are you
> talking
> about a Windows group on the server or something else?
> "Bruce L-C [MVP]" wrote:
>> You are confusing two different things. First, who the user is and what
>> the
>> credentials to retrieve the data for the report are.
>> This is completely two different things (although they can be the same).
>> When you go to the website it is using the integrated windows security
>> that
>> identifies who the user is that is on the computer which is accessing
>> Reporting Services. This has nothing whatsoever to do with SQL
>> Authentication. That is strictly for credentials of the data source.
>> My suggestion is this. First read up on roles. Roles are used to give
>> access
>> to the folders, reports, reporting functionality like deploying reports
>> and
>> subscriptions. Plan how you want to organize and give access. What I do
>> because it is easiest for me, I create a local group that I add
>> individual
>> domain users and domain groups to. Then I give this group role membership
>> in
>> Reporting Services.
>> Next I run SQL Server in mixed mode security (which I think you are
>> already
>> doing). I setup a login that is a read only login. I use that for the
>> credentials of the data source. This buys you performance as well. It
>> allows
>> connection pooling to work its magic.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "mgcap" <mgcap@.discussions.microsoft.com> wrote in message
>> news:93178514-7883-493F-8A18-83614CD56EF5@.microsoft.com...
>> > We have SS 2000 and SSRS 2000. SSRS is connecting as a Windows account
>> > of
>> > "NT
>> > Authority\Network Service". That account is in both RSEXEC roles in
>> > ReportServer and ReportServerTempDB. When we log into the Report Server
>> > website as a domain name (which on the server has admin rights), it
>> > works
>> > fine. When we log into ReportServer using SQL Authentication using a
>> > testname
>> > that is on the server, we get the home page with no access to our
>> > folder.
>> > We
>> > added that id to SQL, we have added that user to RS with full access,
>> > we
>> > have
>> > tried even adding that to the RSEXEC. No change. We also checked IIS
>> > for
>> > the
>> > Report folder and it is using Windows Integrated Security. One more
>> > thing,
>> > the dataset is using the aspnet login. When we make the person an
>> > administrator on the server - it works fine. Of course that options is
>> > not
>> > feasible. Any ideas of what we missed?
>>