July 8, 2010 at 10:26 am
We have recently setup a SQL 2008 R2 box to run SCOM (Systems Center Operations Manager 2007) and SharePoint 2010. We also have the DPM2010 client software running on it since it is part of a Recovery Job on the DPM Server (which is another box) and so this 2008R2 box has a host of severe based Microsoft apps on it.
While I am the DBA and tasked with being responsible for our SQL stuff I do play second string to the Sys Admins & 'network Admins and so I don't always have the final say on things related to my SQL instances. Typically when they setup something they will run it all under the sa account so as to ensure there is no security/permissions related issues that prevent their app from running because it’s not using the sa login/acct.
My question for you guys (and I'm hoping there are at least a few of you with similar scenarios where you have SCOM, SharePoint and or DPM setup/running ) is...
1) Do these MS Server Apps like SCPM really need to use the sa login? I would think at a minimum we could setup a Login that is a member of the sysadmin server role and let it be the acct these apps use.
2) Is it really necessary for SQL Jobs and the like that SCOM and these other apps use to be owned by sa? Again I would think this is not necessary.
I ask only because I have been told in the past that its common for these Enterprise/Server apps from Microsoft like SharePoint 2010, to require sa or sysadmin account use and that simply creating a new account with sysadmin/sa privileges will at best cause problems and at worst break the application entirely. I am adimant about not using the sa for anything unless absolutely necessary; I don;t even log in as sa unless for some reaosn my domain acct login causes something to not work as needed. But if my network guys say these apps gotta have it I can't really say no unless I can back it up with something like an MSDN/TechNet recomendation.
Any thoughts on the matter is greatly appreciated.
Kindest Regards,
Just say No to Facebook!July 8, 2010 at 11:46 am
Sharepoint most definitely has to run as 'sa.' I've never seen anything to suggest otherwise and everything I've seen it do on the server pretty much suggests that's how it works.
Our SCOM system is set up that way too. You could probably tweak down what SCOM owns, but you couldn't tweak it much. I has to be able to pretty much own the databases it works with and it sets up all kinds of agent jobs... again, it's probably better to just give it 'sa'.
Pretty messed up that Microsoft's own internal stuff violates all the rules though, isn't it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2010 at 11:50 am
Grant Fritchey (7/8/2010)
Sharepoint most definitely has to run as 'sa.' I've never seen anything to suggest otherwise and everything I've seen it do on the server pretty much suggests that's how it works.Our SCOM system is set up that way too. You could probably tweak down what SCOM owns, but you couldn't tweak it much. I has to be able to pretty much own the databases it works with and it sets up all kinds of agent jobs... again, it's probably better to just give it 'sa'.
Pretty messed up that Microsoft's own internal stuff violates all the rules though, isn't it.
Microsoft knows best!! 😉
July 9, 2010 at 9:19 am
UGGGHHHH! I hate that rascally wabbit of a software vendor.
I was afraid I would get that kind of feedback.
Thanks Lynn & Grant for chiming in.
Kindest Regards,
Just say No to Facebook!July 9, 2010 at 10:19 am
I would question that is has to run as "sa", but I wouldn't be surprised. The developers at MS do make shortcuts, and it could be hard coded.
However it also might not be worth it for you to detangle the application from the account. Years ago Great Plains said it had to run as SA. Turned out that they just wanted to be able to add logins. If you added them manually, GP could pick them up.
I suspect that SCOM does need SA/sysadmin access. Sharepoint, likely it's because the interface allows the SP admins to do things that SQL admins typically do. If you don't run it as SA, it still might work, but lots of stuff won't work for the SP admins, they'll be calling you for things, and it might not be worth the frustration.
MS will make the excuse that most companies running SP don't have a DBA. Probably true, but doesn't necessarily mean they made a good design.
July 9, 2010 at 10:37 am
Steve,
I use to be a SharePoint fan but now that I have to be in charge of the SQL Boxes SP is a bit of a nightmare. Not only does SP want SA everything but it has a serious DATABASE SPRAWL issue. Just about everything new on the SP site not onlky appears to get its own DB but SP has to assign it some cryptic name that would make even a high function math genuis go "huh". Its a real P.I.T.A to deal with and I guarantee that SP works this way because the SP designers are probably doing like many procedural departments at most sofwtare companies and that is they are doing everything themseleves and not seeking assiatncae/input from anyone SQL Server knowledgable excpet when they can't make something work.
UGGGHH!
Kindest Regards,
Just say No to Facebook!July 9, 2010 at 10:51 am
I think SP is built that way. The guys that built SMS moved on, and tried to be "dynamic" in every way, and random naming is how they solved the collision issues.
It works well on the front, bad on the back. A disturbing idea for the people that might have to rebuild the back end.
July 9, 2010 at 11:48 am
Yeah, it's a bit of a bear.
Fact is, you're right Steve, SharePoint doesn't need 'sa', but it needs about 85% of what 'sa' does, so it's easier to just give it that rather than try to pick things apart, and deal with the fact that everything could change with the next plug-in, service pack or upgrade.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2010 at 12:47 pm
We have a number of Sharepoint servers, and we have never used the SA login or used an account in the sysadmin role for the Sharepoint account.
I just checked one of our Sharepoint database servers, and the Sharepoint service account is in the dbcreator and securityadmin roles only, so I have to assume that this is all that is needed. The Sharepoint admins do not have any additional access to the DB server, and they seem OK with that.
I setup the model database so that it is a good template for a sharepoint database: good inital size for data and log files, good autogrowth settings, no autoshrink, etc.
I setup the full backups and transaction log backup to pick up all user databases, so that when they create new web sites it picks up new databases automatically.
July 9, 2010 at 1:39 pm
Wouldn't be the first time I did something wrong. Time for experimentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2010 at 2:06 pm
It appears from this that only dbcreator and securityadmin are required:
SharePoint Server 2007
Plan for administrative and service accounts (Office SharePoint Server)
http://technet.microsoft.com/en-us/library/cc263445(office.12).aspx
I never looked into this before. I just insisted that the Sharepoint admins had to prove that they needed any fixed SQL server role before I would grant them access, and that is what they came with.
This is also how I handle any other vendor supplied software from Microsoft or any other vendor. If they insist that they need sysadmin access, I insist that they will have to purchase a standalone server, since it would compromise the security of other databases on that server.
July 9, 2010 at 3:32 pm
Michael Valentine Jones (7/9/2010)
It appears from this that only dbcreator and securityadmin are required:SharePoint Server 2007
Plan for administrative and service accounts (Office SharePoint Server)
http://technet.microsoft.com/en-us/library/cc263445(office.12).aspx
I never looked into this before. I just insisted that the Sharepoint admins had to prove that they needed any fixed SQL server role before I would grant them access, and that is what they came with.
This is also how I handle any other vendor supplied software from Microsoft or any other vendor. If they insist that they need sysadmin access, I insist that they will have to purchase a standalone server, since it would compromise the security of other databases on that server.
Thanks for the info Michael. We were on SP2007 (MOSS) but now we're on SP2010. My boss (the CIO) is a SP fan to the max so we have upgraded to each new version (starting from WSS 3.0) fairly quickly.
I'm curious if you happen to use DPM (Data Protection Manager) from Microsoft as well?
Kindest Regards,
Just say No to Facebook!Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply