December 31, 2009 at 6:45 am
Hi
I've spent a while searching the previous posts and cannot get an answer to my problem.
SQL Server 2005 Standard SP3, Windows Server 2003 Standard edition, 2 separate SQL server instances installed
I'm attempting to move away from using the LocalSystem account as the logon/startup accounts for SQL Server, SQL Server Agent and the other SQL services. I've had a new windows domain account created for the SQL Server Agent and given this account a sysadmin SQL server role and also added it to the local administrator group on the SQL server.
When I try to use this account to start the SQL Server Agent within SQL Server Configuration Manager I get this message initially:
WMI Provider Error
The process terminated unexpectedly. (0x8007042b)
and the Agent stops but keeps the new account details in properties.
When I try to restart the Agent with the new account I get this error:
SQL Server Configuration Manager
"The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details."
The log at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT looks like this after the error message -
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)
and then like this when I change it back to the LocalSystem account and restart -
2009-12-31 12:15:31 - ? [100] Microsoft SQLServerAgent version 9.00.4035.00 (x86 unicode retail build) : Process ID 1528
2009-12-31 12:15:31 - ? [101] SQL Server CLANCS-INFSTG version 9.00.4035 (0 connection limit)
2009-12-31 12:15:31 - ? [102] SQL Server ODBC driver version 9.00.1399
2009-12-31 12:15:31 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2009-12-31 12:15:31 - ? [310] 4 processor(s) and 4096 MB RAM detected
2009-12-31 12:15:31 - ? [339] Local computer is CLANCS-INFSTG running Windows NT 5.2 (3790) Service Pack 2
2009-12-31 12:15:31 - ? [432] There are 11 subsystems in the subsystems cache
2009-12-31 12:15:31 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2009-12-31 12:15:31 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2009-12-31 12:15:31 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
I'm not aware of anything changing at server level except for the creation of a new instance a few weeks ago. Could this be the problem?
December 31, 2009 at 9:19 am
And you are absolutely sure you are using the correct domain account password?
_________________________________
seth delconte
http://sqlkeys.com
December 31, 2009 at 9:30 am
Yes, I've browsed it from the domain and picked up the prefix domain name. I also tried it with my personal windows logon (same permissions) and got the same errors.
I also tried it using server administration and services management to get the new password in and restart but no luck.
It's suspicious that I only noticed this since I added the new instance a few weeks ago.
Thanks
December 31, 2009 at 9:39 am
lenrigby (12/31/2009)
Yes, I've browsed it from the domain and picked up the prefix domain name. I also tried it with my personal windows logon (same permissions) and got the same errors.I also tried it using server administration and services management to get the new password in and restart but no luck.
It's suspicious that I only noticed this since I added the new instance a few weeks ago.
Thanks
I am not sure it is related to the new instance because you made the account SA but not admin on the network level so that may be the problem. Here is the link for the Agent account requirements.
http://msdn.microsoft.com/en-us/library/ms345380.aspx
Kind regards,
Gift Peddie
December 31, 2009 at 10:19 am
How are you changing the username/password for SQL Server Agent? If you are not using SQL Server Configuration Manager - all necessary rights/permissions are not being set for this account.
What SQL Server 2005 and above does is create several Windows groups. Then, the accounts that are specified for each service are added to those local groups. Those local groups have the necessary rights to the appropriate folders.
Those groups are also added to the instance of SQL Server - so, you don't have to add the user to the instance. The user account will get it's permissions in SQL Server through the local group.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 31, 2009 at 10:34 am
Jeffrey, I'm using SQL Configuration Manager to change the logon account.
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?
Thanks
December 31, 2009 at 10:37 am
What SQL Server 2005 and above does is create several Windows groups. Then, the accounts that are specified for each service are added to those local groups. Those local groups have the necessary rights to the appropriate folders.
Those groups are also added to the instance of SQL Server - so, you don't have to add the user to the instance. The user account will get it's permissions in SQL Server through the local group.
I don't have this in Windows 7 one admin account is added in Management studio and three general accounts. These accounts are not Windows accounts.
Kind regards,
Gift Peddie
December 31, 2009 at 10:39 am
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.
Kind regards,
Gift Peddie
December 31, 2009 at 11:15 am
Gift - SQL Server 2005 and 2008 (I have both on this machine which is running Windows 7) create Windows Groups as in:
SQLServer2005DTSUser${instance name}
SQLServer2005MSFTEUser${instance name}
SQLServer2005MSSQLServerADHelperUser${instance name}
SQLServer2005MSSQLUser${instance name}
SQLServer2005SQLAgentUser${instance name}
And, you will have the following logins in SQL Server:
{server name}\SQLServer2005MSFTEUser${instance name}
{server name}\SQLServer2005MSSQLUser${instance name}
{server name}\SQLServer2005SQLAgentUser${instance name}
Each of those logins in SQL Server have been granted the appropriate permissions in SQL Server (e.g. MSSQLUser is a member of the sysadmin role).
Each of the Windows Groups have been granted the appropriate rights to the folders. For example, the MSSQL directory for that instance will have the MSSQLUser group added with Read & Execute, List Folder Contents, and Read rights. At a lower level, the groups are granted full rights - or read rights, depending upon what is required for each type of user.
Additionally, each of those groups has been granted the necessary rights to access the Registry - as well as other requirements.
When you use the SQL Server Configuration Manager to modify the user that runs the service - it adds that user to the appropriate group, which then has all of the necessary permissions to run that service.
On SQL Server 2008 - there are fewer groups, and they show up as generic logins - but they are still there and required for granting appropriate file level permissions and SQL Server rights.
I would review the appropriate windows groups - and make sure the domain user has been added to the appropriate groups. Review the file permissions and make sure the groups still exist and have the appropriate permissions. You can review this document:
http://msdn.microsoft.com/en-us/library/ms143504(SQL.90).aspx
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 31, 2009 at 11:26 am
SQLServer2005DTSUser${instance name}
SQLServer2005MSSQLServerADHelperUser${instance name}
You may be in a domain and running an upgrade I don't have these accounts and the ones I have are not Windows account because I have written Asp.net code and deployed it so I have seen all the defined accounts in my system. I am running Windows 7 Ultimate.
Kind regards,
Gift Peddie
December 31, 2009 at 11:37 am
Gift Peddie (12/31/2009)
SQLServer2005DTSUser${instance name}
SQLServer2005MSSQLServerADHelperUser${instance name}
You may be in a domain and running an upgrade I don't have these accounts and the ones I have are not Windows account because I have written Asp.net code and deployed it so I have seen all the defined accounts in my system. I am running Windows 7 Ultimate.
Nope - running Windows 7 Ultimate, not connected to a domain - never been connected to a domain. Have locally installed SQL Server 2005 Developer Edition and SQL Server 2008 Developer Edition on this laptop.
The Windows groups exist - the SQL Server logins exist. This has nothing to do with what OS you are running. The only time you will not see these groups is if you are installing on a cluster. The cluster installation does not add the local windows groups to SQL Server because they cannot be used in a clustered environment. The installation will use the domain user account and grants the appropriate rights - but the local windows groups are still created.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 31, 2009 at 11:46 am
Jeffrey Williams-493691 (12/31/2009)
Gift Peddie (12/31/2009)
SQLServer2005DTSUser${instance name}
SQLServer2005MSSQLServerADHelperUser${instance name}
You may be in a domain and running an upgrade I don't have these accounts and the ones I have are not Windows account because I have written Asp.net code and deployed it so I have seen all the defined accounts in my system. I am running Windows 7 Ultimate.
Nope - running Windows 7 Ultimate, not connected to a domain - never been connected to a domain. Have locally installed SQL Server 2005 Developer Edition and SQL Server 2008 Developer Edition on this laptop.
The Windows groups exist - the SQL Server logins exist. This has nothing to do with what OS you are running. The only time you will not see these groups is if you are installing on a cluster. The cluster installation does not add the local windows groups to SQL Server because they cannot be used in a clustered environment. The installation will use the domain user account and grants the appropriate rights - but the local windows groups are still created.
I am running VS2008 Team Suites and it was installed before I installed any SQL Server because my BIDs are labeled BIDs by Windows 7.
Kind regards,
Gift Peddie
December 31, 2009 at 11:50 am
Gift Peddie (12/31/2009)I am running VS2008 Team Suites and it was installed before I installed any SQL Server because my BIDs are labeled BIDs by Windows 7.
What does this have to do with SQL Server installing, creating and using local windows groups for security? What does BIDS have to do with SQL Server Agent won't start?
Not sure what you are trying to say here...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 31, 2009 at 12:00 pm
Jeffrey Williams-493691 (12/31/2009)
Gift Peddie (12/31/2009)I am running VS2008 Team Suites and it was installed before I installed any SQL Server because my BIDs are labeled BIDs by Windows 7.
What does this have to do with SQL Server installing, creating and using local windows groups for security? What does BIDS have to do with SQL Server Agent won't start?
Not sure what you are trying to say here...
I am saying I am running a development configuration of Windows 7 which is not the same, so accounts added are based on what is running in the operating system before SQL Server is installed.
Kind regards,
Gift Peddie
December 31, 2009 at 12:06 pm
Gift Peddie (12/31/2009)I am saying I am running a development configuration of Windows 7 which is not the same, so accounts added are based on what is running in the operating system before SQL Server is installed.
Not true - what is installed and running when SQL Server is installed won't make any difference to how SQL Server is installed. SQL Server requires and uses those local windows groups to manage security to the actual files.
The only time they are not created is when you install to a cluster - which cannot use local windows groups.
If your configuration does not have those groups - then they were removed after SQL Server was installed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply