Replication Fails after removing BUILTIN\Administrators
-
I removed BUILTIN\Administrators from my 2 SQL Servers participating in replication and now replication fails.
Server 1's SQL Agent would start then stop due to a login failure for the SQL Agent Service so I made sure that that Domain Account running SQL Agent had dbo privilages on all my databases and was a member of the Systems Administrators role. It did not work so I put BUILTIN\Administrators back in Server1 and the SQL Agent started again and replication on Server 1 worked correctly.
Server 2 still does not have BUILTIN\Administrators but the Domain Account running both Server 1 and Server 2' SQL Agent is a sysadmin of this server.
When replication is started it fails to log on to Server 2.
The domain account is configured to be a local admin of both Servers and a sysadmin of both SQL Servers. The publisher is configured to impersonate the SQL Agent service.
Do I need to remove this domain user and re-add them?
Thanks in advance,
Ben
-
okay, nobody has responded. I'm going to bite and ask "why?"
WHY are you removing the builtin/admins? Exactly what procedure did you use to do so? Is there really a business case for this? Who do you trust? and if you REALLY are a newbie, well, maybe this is a lesson you didn't want to learn.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
I removed BUILTIN\Administrators through Enterprise Manager. The reason is because we have more Domain Administrators that I would like which is a different problem that won't be resolved in a timely manner. Normally I don't remove BUILTIN\Administrators but I need to reduce this server’s exposure.
I believe my knowledge is lacking in the Security Logins area and the way logins are granted permissions. Keep in mind I am normally looking at things from Enterprise Manager and not TSQL which might reveal things to me that are not so obvious now.
A while back I changed the SQL services accounts to a domain user which I did through Enterprise Manager to ensure all the various other settings were implemented correctly.
I now believe that my problem is with the way that domain user account was added and granted permissions. In Enterprise Manager the account for which SQL and the Agent run is granted access "Via group membership" I am assuming this it linked through the BUILTIN\Administrators group somehow.
If all that is true then would it be safe to remove that user then re-add that user. Also if there is a way for me to see how the login is linked through a group I would be interested to know how.
-
... Also if there is a way for me to see how the login is linked through a group I would be interested to know how. ...
EXEC master..xp_logininfo @acctname = 'yourdomain\youruser',@option = 'all'
go
EXEC master..xp_logininfo @acctname = 'yourdomain\yourgroup',@option = 'members'
go
btw the second thing we do at a server is remove BUILTIN\Administrators
(the first thing is add the appropriate dba-windows-group to the sysadminsgroup of sqlserver)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I was smart enough to put a new group in place before I removed BUILTIN\Administrators. I also changed and validated the sa password.
Thanks for the query this points me directly to my problem.
-
I agree that this is necessary in some situations... and it seems you have taken the necessary precautions by creating a back door before removing the builtin admins... at a minimum, that was where I was going with my question.
I used to do this, too... and for years, while an Oracle DBA, I would also take special precautions with "sys" and "system" accounts, since the sys account was the key to security.
But over the years I have found that you can cause yourself more trouble than it's worth by playing this game. As soon as you think you've got a "secure" system, and make such a statement, you are turning a network of admins on to a challenge... to prove you wrong... they try, you try harder... and eventually you've created a nightmare.
I always try to remember... it's not MY system... there are admins there for a reason, different types of admins... system admins, network admins, database admins... and they had better be able to get along.
it boils down to "who are you going to trust"...
Good luck with your query.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
I think both points are valid to an extent. For me, the reason to remove the BUILTIN\Administrators account is to reduce the 'potential' for compromise to the databases under my administration. The less people with admin privileges to my SQL Server, the more control that I have.
-
I am not trying to prevent the various administrators from gaining access to our SQL Servers as much as I am providing a very specific auditable method in which they can access the data.
It also decouples the data from the network. The method in which you access the data and its exposure stays consistent despite domain level changes. To me it helps prevent a tug of forces.
I have also never seen an organization without at least one service account in the Domain Administrator group.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply