September 30, 2003 at 9:12 am
I've been looking at running our SQL Server 2000 boxes using accounts that do not have Administration rights. Seems like a more secure thing to do right?
I've reviewed KB article 283811 and Chris's article: http:\\qa.sqlservercentral.com\columnists\ckempster\sql_server_security.asp
regarding this subject.
What I am wondering what might be the gotcha's I need to be aware of when removing the Administration permissions for the service accounts?
Right now, from my limited testing seems like all I need to do is remove the Admin permissions from the account, and restart the service. Is there any thing else I need to consider? It can't be that simple. Or can it?
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here:http:qa.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 30, 2003 at 9:40 am
BKelley has some good advice somewhere. Not sure if he wrote an article or if it is in an ebook he did, might check with him. Or send him an email and ask him to post here.
I think you want to change the service account, remove admin permissions, then change back using EM, which should reset all permissions needed.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
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
September 30, 2003 at 9:53 am
Not exactly sure what you are saying here "I think you want to change the service account, remove admin permissions, then change back using EM, which should reset all permissions needed.". So I'll just rephrase it, and then ask you if this is what you where talking about.
Step 1) Change the service account to another account using EM.
step 2) Remove the admin permissions for the service accounts you plan to have services running under.
Step 3) Using EM change the service account back to the ones originally.
Does this sound right?
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:qa.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 1, 2003 at 6:49 am
I haven't written an article on the specifics of what permissions the account needs (need to... too many things going on right now), but the basic approach you've put together handles the file and registry permissions, which can be a problem. Step 1 can actually be localsystem (unless you are on a cluster) since you do plan on putting the SQL Server services running back under the same service accounts.
The reason for Steve suggests using EM is because EM is supposed to set all of the permissions properly. This can save a lot of headaches (been there, done that).
You may also want to read up in Books Online the exact ramifications of not having services running under administrator. For instance, SQL Server Agent won't be able to autostart SQL Server if it should stop. This isn't typically a concern, especially if you have other means of monitoring (task scheduler and psservice, a completely free solution is one option) and restarting services.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 1, 2003 at 6:55 am
The main roles the service IDs need include (so far as I understand it):
I could be wrong. We currently set ours to have local admin rights, but not domain admin.
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 1, 2003 at 7:08 am
I believe EM is supposed to set these, too, but you're right, these are needed. If you're on Win2K or Win2003, you can set these using the Local Security Policy if EM doesn't set them. The file/registry permissions are necessary if you're not a member of the Administrators role and EM takes care of that, too.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 1, 2003 at 8:41 am
We are trying to experiment with this idea as well.
1st Create a complety new login domain service account.
2nd we added that user to the local user group.
3rd we added the user to full control on c:\ and other data, log and backup drives.
4th login locally at the server and change EM to use the new logon.
It works but when we start sql we got some strange error and couldn't figure out if it was related to sql server or not. This is suppost to be more secure because the new service account does not have right to logon through terminal service. The new account is not part of the administrators group.
It would be helpfull if anybody else had similar experience.
mom
October 1, 2003 at 8:43 am
What error are you receiving?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 1, 2003 at 9:04 am
Error from sql server log:
Cannot load the DLL xp_logattach.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
October 1, 2003 at 9:16 am
That holds the extended stored procedures that are part of Lumigent's Log Explorer. I don't have Log Explorer installed on any systems. You may want to check if the dll is in the proper location... default instance:
\Program Files\Microsoft SQL Server\MSSQL\Binn
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 1, 2003 at 10:01 am
We don't have that software install on any of our system. I also went out and search for that file in binn directory on local server as well as the installation directory and nothing came up. None of our production servers has this file either.
mom
October 1, 2003 at 11:33 am
My guess is someone at one point installed and then uninstalled the software and for whatever reason it didn't clean up completely. Is this a possibility? Is it happening on all servers you try this on?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 1, 2003 at 3:16 pm
It is quiet possible, but we have a strick guidline as far as what software can be install in any server. I have not try change the service account on another server yet. I will try it on another test server to see if I got the same result.
October 1, 2003 at 3:42 pm
If someone is actively running Lumigent, perhaps they could look into the registry and see what Lumigent inserts. Then you could compare. If it was an uninstall that wasn't clean, perhaps you'll get a "hit" on the registry which would confirm things.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 7, 2005 at 1:37 am
I use EM to change service account from local system account to local user account,log on success,but SQL server can't be restarted.Anyboy know why?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply