November 29, 2007 at 10:17 am
If I have all of the services configured to use a network account, can I safely remove sysadmin rights from NT AUTHORITY\SYSTEM and NT AUTHORITY\local inside SQL Server?
Is Integration Services impacted by this change when it goes out to other servers gathering data? Do those other servers need to grant permissions to the specific network account that SSIS is running under?
Is Reporting Services impacted on the local server?
Thanks for the help!!!
December 2, 2007 at 12:52 pm
If you have moved all services to their own network accounts, then this should not be a problem. Each service runs under the context of the service account, or under the account executing the process (such as a SQL Agent job running an SSIS package).
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 2, 2007 at 6:31 pm
Thanks Steve...I'll let you know how it goes after removing that account from the Sysadmin role.
December 3, 2007 at 4:56 pm
As a followup...After removing SysAdmin privileges from the NTAuthority and the BuiltIn/Administrators account, everything is running and operating smoothly.
All of the various services are firing off at their scheduled times and, I believe, we have plugged a security hole exposed by the use of these two accounts as sysadmins.:cool:
Thanks Steve!!!
December 3, 2007 at 5:14 pm
You are welcome and thanks for the update.
If anything comes up, please post again here. Might help someone else.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 4, 2007 at 2:33 am
Basically, I set up in similar fashion (i.e. SQL Server / Agent service is running on its own service account, and no sysadmin rights for NT AUTHORITY\Local Admin or Local System, ) The SQL Server functionality is OK.
However, I wonder how will it impact the backup. The service "SQL Server VSS Writer" did run on local system. While the server is being back up (we use Veritas, but the problem should relate with all VSS base backup program), the VSS mechanism did kick start the service, and reported various failure message.
Is there any further suggestion? Should I also use a service account to run that service ?
December 4, 2007 at 6:42 am
I have a general distrust of agents such as Backup-Exec performing direct DB backups. We prefer to backup the databases and logs to disk using SQL Server and then let the backup software backup those database dumps to tape.
I got burned awhile back by trusting a backup agent from a third-party backup solution. It took weeks to recouperate. At the very least, I would hedge my trust of any third-party solution by using SQL Server backups too.
Of course not every shop has the luxury of dumping the entire DB/logs to disk.
December 4, 2007 at 7:36 am
Any of the direct to disk products have burned many people here in the past. I have not heard of issues from Litespeed, Red Gate Backup, or the third party disk products.
I'm not sure about the VSS service. I've never changed this from running under Local Service or Local System before. Not entirely sure how it works. I might leave this alone, let the backups run, and test restores a few times to be sure they're working.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 4, 2007 at 7:21 pm
Thanks Martin and Steve.
We also just depends on SQL Server's own backup/restore utility (to disk file) for backup / recovering DB while Veritas just help to recover the Server image. Our restore drill does not shows any problem.
I found a related support article :
http://support.microsoft.com/kb/919023
I just wonder the VSS section and Security section of SQL Server team in MS may just never talk to each other, as if the support article is correct, they are just contradicting to each other.
Rgds,
Eric
December 5, 2007 at 7:44 am
Very interesting and I hadn't seen this. Looks like you might need the System account if you are using VSS. Personally I've avoided VSS, not sure that the teams talked and it wasn't worth having a suspect database on the far end to get a copy.
There's too many other ways to get the information moved.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply