Tuesday, March 20, 2012

Replication and security..is it really this UNSECURE!

Hi Guys
I need to create an anonymous pull subscription over the Internet.
I've got everything working but I don't want my subscribers to be able
to connect to the publisher/distributor via Enterprise Manager or
ISQLW or anything else for that matter!! Basically they should only
be able to talk to the Publishing SQL Server through replication
methods. How is this achieved?
I'm currently using SQL Security because I don't want an anonymous
subscriber to impersonate my publishers sqlserver agent account, the
agent on the publisher is used for other scheduled tasks that needs
Domain Admin privileges, am I missing something here!!?
Currently I've got it secure-ish. I've used a specific sql account
and given it access in the PAL and removed the guest account from
other DBs, however you cannot remove the guest account from Master or
Msdb and said account must also be a member of the db_owner role. The
most they can do is issue select statements against tables but I think
that is even too much, for instance they can query sysservers which
would allow them to see other subscribers that we do business with.
Even if I use a secure VPN between publisher and subscriber what's to
stop an inquistive subscriber, using the SQL account that I've told
them to use for their subscription, connecting using EM or ISQLW
through the VPN? Surely there must be a way to give ONLY replication
access but nothing else?
Please help!!
The problem is not with replication and security, but rather a larger one
with SQL Server security in general. Anytime you create an account on that
server, and give that account rights to only one user database, this account
can query system tables on the master database.
I suggest you post this question to the microsoft.public.sqlserver.security
newsgroup.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"greedo" <d8rrylf@.msn.com> wrote in message
news:f0badc7a.0408170010.47baef00@.posting.google.c om...
> Hi Guys
> I need to create an anonymous pull subscription over the Internet.
> I've got everything working but I don't want my subscribers to be able
> to connect to the publisher/distributor via Enterprise Manager or
> ISQLW or anything else for that matter!! Basically they should only
> be able to talk to the Publishing SQL Server through replication
> methods. How is this achieved?
> I'm currently using SQL Security because I don't want an anonymous
> subscriber to impersonate my publishers sqlserver agent account, the
> agent on the publisher is used for other scheduled tasks that needs
> Domain Admin privileges, am I missing something here!!?
> Currently I've got it secure-ish. I've used a specific sql account
> and given it access in the PAL and removed the guest account from
> other DBs, however you cannot remove the guest account from Master or
> Msdb and said account must also be a member of the db_owner role. The
> most they can do is issue select statements against tables but I think
> that is even too much, for instance they can query sysservers which
> would allow them to see other subscribers that we do business with.
> Even if I use a secure VPN between publisher and subscriber what's to
> stop an inquistive subscriber, using the SQL account that I've told
> them to use for their subscription, connecting using EM or ISQLW
> through the VPN? Surely there must be a way to give ONLY replication
> access but nothing else?
> Please help!!

No comments:

Post a Comment