security loophole for the TargetServerRole ?!

microsoft.public.sqlserver.programming

11 Aug 2006 3:41 PM

=== Steve L ===

I'm really surprised not hearing anything back about this. to me, this
is a serious security loophole, it allows users to hop servers and
access the databases and info restricted to them and went unnoticed.

is microsoft even monitoing this newsgroup?!
even a simple 'thanks to inform us about this problem, we are working
on it' will do.

steve lin
mcdba

=== Steve L === wrote:
Show quote

> need some expert advices here.
>
> background sql2k with sp4
>
> i granted some developers TargetServerRole for msdb to wirte and
> maintain dts for their applications but only found out that with that
> role, one can pause and even stop sql server! also, they can write dts
> packages to add ole db provider to transfer tables from database they
> do not have permission for to a database they have permission for and
> read everything from there. they also are able to browse master
> database even i don't give them permission for the master database.
>
> they do need permission to create and maintain dts packages and
> scheduled jobs. so what can i do to revoke the unexpected permissions i
> mentioned earlier? thank you.

11 Aug 2006 7:38 PM

David Portas

=== Steve L === wrote:

> I'm really surprised not hearing anything back about this. to me, this
> is a serious security loophole, it allows users to hop servers and
> access the databases and info restricted to them and went unnoticed.
>

Did you try removing the developers from TargetServerRole to see if
they still had the same ability as before? You may find they have the
sysadmin role by some other route - perhaps due to the credentials
cached with their Enterprise Manager registration or through membership
of the Administrators user group.

AFAIK TargetServerRole is intended only for logins used by SQL Agent
for multi-server administration. It is a database role, so it should
not permit the server to be shutdown but on the other hand it barely
seems to be documented so I would not recommend assigning it to
non-sysadmin users anyway. In any case, developers don't need to be
members of this role in order to create DTS packages - only to manage
jobs.

> is microsoft even monitoing this newsgroup?!
> even a simple 'thanks to inform us about this problem, we are working
> on it' will do.

This is a public newsgroup, not owned by Microsoft. MS employees do
post here but this is not the place to contact Microsoft to inform them
of a problem. For that you should open a case with Product Support or
use the Product Feedback Centre.

http://support.microsoft.com/Default.aspx
http://connect.microsoft.com

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

14 Aug 2006 9:53 PM

=== Steve L ===

I created a SQL standard account for my testing so I know I'm
isolating the login and the extra permissions are not flowed down from
somewhere else. I only give the account northwind database public
role. and soon as I grant that login TargetServerRole, it can do all
what i have described. But when i inspected the explicit permissions
for TargetServerRole, I didn't see (or know) anything to prevent it
from doing those things. the permissions probably are inherited from
somewhere thru the role. You are correct about there's little
documentation about TargetServerRole or how to manage it.

I understand MS doesn't own this newsgroup but I do know the
newsgroup is a great resource for them to uncover many of the product
issues. For this particular issue, I just don't feel right that I
should spend more of my resources to inform them of something not quite
right about their product. My companies has been paying for MSDN and
Technet, if this is indeed a bug, they should fix it and post the
solutions in those subscriptions.

David Portas wrote:
Show quote

> === Steve L === wrote:
> > I'm really surprised not hearing anything back about this. to me, this
> > is a serious security loophole, it allows users to hop servers and
> > access the databases and info restricted to them and went unnoticed.
> >
>
> Did you try removing the developers from TargetServerRole to see if
> they still had the same ability as before? You may find they have the
> sysadmin role by some other route - perhaps due to the credentials
> cached with their Enterprise Manager registration or through membership
> of the Administrators user group.
>
> AFAIK TargetServerRole is intended only for logins used by SQL Agent
> for multi-server administration. It is a database role, so it should
> not permit the server to be shutdown but on the other hand it barely
> seems to be documented so I would not recommend assigning it to
> non-sysadmin users anyway. In any case, developers don't need to be
> members of this role in order to create DTS packages - only to manage
> jobs.
>
> > is microsoft even monitoing this newsgroup?!
> > even a simple 'thanks to inform us about this problem, we are working
> > on it' will do.
>
> This is a public newsgroup, not owned by Microsoft. MS employees do
> post here but this is not the place to contact Microsoft to inform them
> of a problem. For that you should open a case with Product Support or
> use the Product Feedback Centre.
>
> http://support.microsoft.com/Default.aspx
> http://connect.microsoft.com
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --