October 25, 2010 at 10:13 am
Steve
thanks for that tip. I'll try it.
Len
October 25, 2010 at 10:40 am
Okay, the error you are getting is an access denied error:
2009-12-31 12:12:51 - ! [241] Startup error: Unable to initialize error reporting system (reason: 5)
2009-12-31 12:12:52 - ? [098] SQLServerAgent terminated (normally)
When you use SQL Server Configuration Manager to change the service account, it is supposed to add that user to the local groups defined for SQL Server:
SQLServer2005{service}${instance name}
Review each group and make sure the domain account you are using has been added. I have seen situation where the domain account has not been added correctly. If that is all setup correctly, then you need to validate the folder permissions.
You can review this document, and the Books Online topic outlined in the following:
From the above reference - you will find this:
For SQL Server 2005, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, see the "Reviewing Windows NT Rights and Privileges Granted for SQL Server Service Accounts" section in the "Setting Up Windows Service Accounts" topic in SQL Server 2005 Books Online.
I do not recommend putting the service account in the local administrators group. Doing so is the same as using a local system account and is not recommended.
It is very possible that the reason you are having problems is because the system databases and/or error logs were moved. When they were moved, they may not have been granted the appropriate rights which is preventing the service account from accessing those files.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 26, 2010 at 5:16 am
Thanks Steve and Jeffrey
We looked into proxies and got the desired result by leaving the agent service logon alone and changing the steps in the scheduled job to run as an SSIS job with our new AD account. This covers our requirements.
I will look into the other stuff as I suspect there are some areas where the logs are stored which cannot be accessed by the new AD logon account.
I have the desired result, many thanks
Len
October 27, 2010 at 10:18 am
Make sure the new service account is set up in the Local Security Policies as well (Administrative tools/Local Security Policy/security settings/users right assignment). Another thing to check is the delegation flag in AD.
October 29, 2010 at 3:28 am
Thanks very much to all who took the trouble to reply. We have now got to the bottom of our problem.
Someone in the past has inadvertently (naively and by mistake) added “Domain Users” to the "Guest" group on the server. I'm told that the Guest group is heavily restricted and any members can’t start services or do a number of other things on the server, including viewing the event viewer which made troubleshooting very difficult!
We removed the offending entry from the Guest group and hey presto we can now use the new AD account to start the SQL Server Agent service automatically.
We also had earlier success based on Steve's earlier post using a proxy to run a job step.
Thanks
October 29, 2010 at 9:03 am
Thanks for the update, and I never add anything to Guest, Public, Everyone, or similar shared groups all over.
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
October 29, 2010 at 9:32 am
christina.sharp (10/27/2010)
Make sure the new service account is set up in the Local Security Policies as well (Administrative tools/Local Security Policy/security settings/users right assignment). Another thing to check is the delegation flag in AD.
The above is not relevant because in the right setup SQL Server is a member server that is added to the domain and not participate in AD tasks. The Agent is used for data automation such tasks cross server and domain boundaries correct permissions in required in SQL Server 2005 and above.
Kind regards,
Gift Peddie
August 5, 2011 at 5:05 am
Gift Peddie (12/31/2009)
Gift, my setup is consistent with the guidance in the atricle
http://msdn.microsoft.com/en-us/library/ms345380.aspx. What do you mean by "not admin at the network level"? Are you saying this new account needs to be in the domain admins group?
That is correct because you can define the account as proxy when using it for jobs and other admin and automation tasks.
What a nonsens - why should a service account be member of the local administration group 😎
If it's so as you has pointed out, Microsoft would not sell any licence to a bank!
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
August 5, 2011 at 6:33 am
spam-993574 (8/5/2011)
Gift Peddie (12/31/2009)
Gift, my setup is consistent with the guidance in the atricle
http://msdn.microsoft.com/en-us/library/ms345380.aspx. What do you mean by "not admin at the network level"? Are you saying this new account needs to be in the domain admins group?
That is correct because you can define the account as proxy when using it for jobs and other admin and automation tasks.
What a nonsens - why should a service account be member of the local administration group 😎
If it's so as you has pointed out, Microsoft would not sell any licence to a bank!
Microsoft relationship with banks is handled by Microsoft consulting with the Context account which was created during SQL Server 7.0, so most bank at home is run with SQL Server I worked in one and our license included Microsoft and Oracle consulting on speed dial.
Kind regards,
Gift Peddie
August 7, 2011 at 1:24 am
Microsoft relationship with banks is handled by Microsoft consulting with the Context account which was created during SQL Server 7.0, so most bank at home is run with SQL Server I worked in one and our license included Microsoft and Oracle consulting on speed dial.
Hallo Gift,
Give me ANY reason why you should consider a service account in the local admin group!
NO account - if not necessary - should be a local admin!
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
August 7, 2011 at 7:31 am
I think this is a conversation you need to have with Microsoft and not me because the information is in the link I posted I did not make it up. The banks and many other companies my current employer included use the Agent as a free automation system because the customer only care about their data and not the how.
http://msdn.microsoft.com/en-us/library/ms345380.aspx
Kind regards,
Gift Peddie
August 7, 2011 at 9:04 am
Gift Peddie (8/7/2011)
I think this is a conversation you need to have with Microsoft and not me because the information is in the link I posted I did not make it up. The banks and many other companies my current employer included use the Agent as a free automation system because the customer only care about their data and not the how.
Hi Peddie,
thank you for the link - but it did not answer my question.
Why do you need to have a service account in the local Admin Group?
With respect - that's definitly a high risk if you do so!
Please note that all jobs which will not use a proxy account or T-SQL Jobs which run under a definied login will automatically run with the account of the SQL job agent!
If you will privilege this account with local administrative rights you open all doors for any intrusion and attack forced by jobs.
e.g.
You are the database administrator / operator
I send you an T-SQL script which should be implemented as a job
You implement it and it's running with the account of the sql server agent
What will happen if it runs the following job step:
EXEC sp_configure 'Show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC xp_cmdshell 'DELETE D:\myonlyconstructiondata\*.*'
EXEC sp_configure 'xp_cmdshell', 0
EXEC sp_configure 'Show advanced options', 0
RECONFIGURE
Do you see what will happen if the account has administrative privileges?
So - with respect - don't post recommendations to give ANY service account higher privileges as needed.
Another risk will be raised if you use multi-instance configurations.
In this case I can create a backup of databases from INSTANCE 1 by a job wich runs sqlcmd
Next will be the emailing of this backup to any account with t-sql, too.
Do you see the risks?
My above example is a primitive demonstration.
Maybe you think about it next time.
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
August 7, 2011 at 9:45 am
You are the database administrator / operator
I send you an T-SQL script which should be implemented as a job
You implement it and it's running with the account of the sql server agent
What will happen if it runs the following job step:
EXEC sp_configure 'Show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC xp_cmdshell 'DELETE D:\myonlyconstructiondata\*.*'
EXEC sp_configure 'xp_cmdshell', 0
EXEC sp_configure 'Show advanced options', 0
RECONFIGURE
Do you see what will happen if the account has administrative privileges?
So - with respect - don't post recommendations to give ANY service account higher privileges as needed.
Another risk will be raised if you use multi-instance configurations.
In this case I can create a backup of databases from INSTANCE 1 by a job wich runs sqlcmd
Next will be the emailing of this backup to any account with t-sql, too.
Do you see the risks?
Then I ask you did you know xp_cmdshell is disabled by default by Microsoft in SQL Server 2005 and up and that it is not needed in most automation operation?
That also mean you have not done SQL Server to Oracle and DB2 automation which actually comes with the admin requirement in both systems.
Because some configuration options require a server stop and restart to update the currently running value, RECONFIGURE does not always update the currently running value (the run_value column in the sp_configure result set) for a changed configuration value.
Kind regards,
Gift Peddie
August 7, 2011 at 10:48 am
Then I ask you did you know xp_cmdshell is disabled by default by Microsoft in SQL Server 2005 and up and that it is not needed in most automation operation?[/QUOTE]
The question is not, whether it is activated or not. The question is THAT I CAN DO IT WITH SYSADMIN PRIVILEGES!
The service account is by default sysadmin to the instance...
That also mean you have not done SQL Server to Oracle and DB2 automation which actually comes with the admin requirement in both systems.
Funny - what has that to do with local admin privileges?
Because some configuration options require a server stop and restart to update the currently running value, RECONFIGURE does not always update the currently running value (the run_value column in the sp_configure result set) for a changed configuration value.
With respect, Peddie - but you have to learn SQL Server.
Your answers are drifting away from the problem!
Concerning your above mentioned text:
xp_cmdshell can be activated by any user with sysadmin or serveradmin privileges
activation of xp_cmdshell does not require a restart of the service
See the following msdn articles for more information
http://msdn.microsoft.com/en-us/library/ms188787.aspx
http://msdn.microsoft.com/en-us/library/ms176069.aspx
BTW: IF the service account is local admin it CAN restart, stop any service which is running on the computer!
So - depending on my former example...
-- I'm an intruder and will get you courious
EXEC sp_configure 'show advanced options", 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
-- Now I disable any service (e. g. SQL Server Browser)
-- This will make other instances unreachable for any user!
EXEC xp_cmdShell 'NET STOP "SQL Server Browser"'
-- Now I will reconfigure xp_cmdshell (because it is deactivated by default)
EXEC sp_configure 'xp_cmdshell', 0
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
The problem here is - from my point of view - that you give advices which can make systems instable and insecure. I see every day such a crap of configuration which have been done by "specialists". Especially system configuration and security is a problem for most system administrators who will be able to install a SQL server but can not handle it.
Due to this banks and insurances will have a policy called "segregation of duty" which will give specialists their territory which they know!
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
August 7, 2011 at 10:56 am
I understand that but I am telling you the account ACL(Access Allocation) being a service cannot be used by anything other than the Windows process running it. Here is a link that explains it better and even goes on to say verify the account is admin or SQL Server cannot be upgraded.
One of the comments in that link shows the reasons for the priviledge because if it is required to access operating system process for OLEDB operations then it is needed.
SQL Server Agent (MSSQLSERVER) Use the autorestart feature. Must be a member of the Administrators local group.
Before you upgrade SQL Server, enable Windows Authentication for SQL Server Agent and verify the required default configuration: that the SQL Server Agent service account is a member of the SQL Server sysadmin group.
http://technet.microsoft.com/en-us/library/ms143504.aspx
Due to this banks and insurances will have a policy called "segregation of duty" which will give specialists their territory which they know!
That is comical because if I was not there Security would have shot down bank at home at the bank I worked because the Agent context account runs five hours a day five days a week to keep accounts in synch.
Kind regards,
Gift Peddie
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply