August 14, 2009 at 4:15 am
Hello,
Our company's network admin team have been tasked with installing SCCM (used to be SMS). It needs a SQL Server database which is on a separate server from the one on which SCCM is installed.
They initially asked me for a database on our main production SQL 2008 server, but then they discovered that according to the SCCM documentation, the SCCM login must have SYSADMIN(!) rights on the SQL Server.
We are (obviously) not prepared to do this, and we don't have another server kicking around to do a complete new install on.
I was thinking that one approach would be to install a second instance of SQL Server on the same box.
Can anyone tell me if this is a reasonable thing to do?
If it is, what pitfalls might we encounter, and is there anything specific we need to bear in mind when setting it up?
If it isn't, what other options to we have?
Thanks,
Rachel.
August 14, 2009 at 4:41 am
This is just personal preference, but I hate having databases for 3rd party applications (even Microsoft ones) on the same instance as our own databases.
The ability to upgrade to a later SQL version is then constrained by the 3rd party's support for the new version.
The downsides to a second instance:
Another instance to backup/maintain/patch.
Memory has to be balanced across the instances. I know memory would be a consideration when adding another database to an existing instance, but the extra instance adds additional overhead for the SQL Server binaries, system databases etc.
August 14, 2009 at 8:33 am
IAN has stated it well, but I tend to like combining apps on one server. Hate instances because of the memory issues, but I do like sharing stuff.
I'd dig in on the sysadmin right. We had Dynamics years ago (or Great Plains) and they claimed this. turned out to be just so they could create users, so it wasn't true.
August 14, 2009 at 8:42 am
I had this a few times in the past where 3rd party applications have requested that the login be sysadmin, I told them where to go also.
Largely people request sysadmin because they don't actually know what permissions their application requires, so it's just easier to request sysadmin. I would suspect that as posted above the requirements for permissions will be adding logins and creating databases (at install).
August 17, 2009 at 8:49 am
The prerequisite check on the installer fails unless the login has sysadmin rights. A subset won't do.
We're going to try and do the install then remove the sysadmin rights afterwards, and hope that it will carry on working.
On a test machine to start with.
I find it a little frustrating that MS preaches the principle of least privilege then don't practice it themselves.
August 17, 2009 at 9:08 am
The installed might need to do something, create db, add users, etc. Likely it doesn't need sysadmin, but it's probably not worth figuring it out, unless you want to document it. I would try to remove it afterwards and see what you can do.
And I agree. Stinks when MS doesn't follow their own guidelines.
August 18, 2009 at 9:09 am
Been there, done that, got the t-shirt. Run into a number of apps that "think" they need sa. Every time, we've managed to cut their permissions to db_owner at most after install. Let me know what you find out, because I'm about to install SCCM in test with our server team.
August 18, 2009 at 10:12 am
OK, this is where we've got to now.
The account used to install the SCCM software needed to have sysadmin rights, otherwise it wouldn't install because it failed when the prerequisites were checked.
Once the installation was complete, there was a new login DOMAIN\SCCMMACHINE$ on the SQL Server with securityadmin server role, db_datareader in the master database and dbo access to the SCCM database. The original account was still sysadmin (obviously).
Interestingly, all of the SCCM services run as the Local System account, but if you change them to a specific user, it still logs onto the SQL Server using the DOMAIN\SCCMMACHINE$ login, so it breaks if you remove that login even if you set up an appropriate login for the service user account.
Next step was to remove lots of permissions. The original install account I have removed all access and disabled. The DOMAIN\SCCMMACHINE$ account I have removed securityadmin server role and master database access, but left dbo access on the SCCM database.
Everything seems to be working fine...
We're not familiar enough with SCCM to know what kind of operations might conceivably have needed the master access or the securityadmin rights, so it's a little difficult to test.
I'm not inclined to remove db_owner at the moment - we don't have the resources to work out the actual permissions it needs.
April 26, 2011 at 2:23 pm
We are installing SCCM 2008 R2. We removed sysadmin AFTER the install and assigned DBO. Everything was fine until the LAN Admin called and said they needed to run the backup and recovery of the sql server database from the application. There is a wizard that is used to set this up and schedules it. SCCM is required to keep all files 'in sync'. Apparently, this is the only way this application can work. (Even though a sql server database can be recovered to a point in time.)
Sysadmin is required to execute a restore. At least that's what I'm thinking. Does that sound right?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply