After I change Log on of SQL Server 2005, my SQL service agent can not start, any idea?

I use localsystem to start SQL 2005, and use a domain/user to start SQL 2005 agent, the user is an domain admin and local admin, which works.

I had problem to use openrowset, so I changed the SQL 2005 to use the same domain.user to start, which works, after that, I can use openrowset well, but now, I can not start SQL 2005 agent, I get error:

SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).

and another enry in event viewer, not sure if they are related

SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.

It is SQL 2005 on window 2003.

Any idea how to fix it?

thanks!

In sql 2005 the agent xps are turned 'off by default' so I think the first thing you need to do is turn on the Agent Xps.

exec sp_configure 'show advanced options' , 1 ;reconfigure

exec sp_configure 'Agent XPs',1;reconfigure

go

hth,

-Steven Gott

S/DET

SQL Server

p.s. SQL 2005 does not need to run as a domain admin , It would be much safer if you set it to run as a normal user b/c the high privilleges of the service account combined with some of the xps like xp_cmdshell allow a dba who is sysadmin to manipulate your domain as the domain admin.

|||

I had problem to use openrowset, so I changed the SQL 2005 to use the same domain.user to start, which works, after that, I can use openrowset well ...

I suspect that your problem with using OPENROWSET had to do with permissions to access the file you were attempting to open. Since your original SQL Server account was not a domain account, you would not have been able to access domain resources, including files, with the MSSQL service. Once you changed to use the domain account that was set up for SQL Agent, the OPENROWSET worked since it could now access domain resources.

|||

Hi,

I think you mixed SQL server account with SQL server agent account, I think it is SQL server account that will access the files for openrowset.

Well, I changed the account for my SQL server from local system to a domain admin, that solves the openrowset problem, but more other problem come in.

First, the Memory used by SQL keeps going up, reached 1.7G, I remember how to solve this, I need give the account "lock page in memory" permission, that solves it

Second, I got error, my CLR reports problem, it says

A .NET Framework error occurred during execution of user-defined routine or aggregate "linkclr_x_pdf_createpdf":
System.DllNotFoundException: Unable to load DLL 'ABCpdfCE6.dll': Not enough storage is available to process this command.

When the error happens, the SQL only use 170M memory, the server has 3G memory.

Really a big problem, how can I just set an account with fully localsystem permssion plus permission to access domain file? Since the localsystem worked fine except the openrowset problem.

thanks

|||

My God, after caused me so much trouble, I found the openrowset may have nothing to do with the account. It only works for some time after each restart, even with local system, it works fine, it can access remote files, maybe because I set the permission to the sql server.

But the issue is it stops working after some time, someone said to set permission to temp folder, doesn't work for me yet.

Isn't that a big bug? could someone from the SQL team to shed a light here? I havn't been asking for the solution for many months.

|||

Steve,

when I run this command I get

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 5845, Level 16, State 1, Line 1

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

It just will not let me turn it on ..... is there any other way to change the option?

thank you

mitch

|||

You can try using the SQL Server Surface Area Configuration tool.

It will be in the configuration Tools folder under SQL Server

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steve,

I tried this and I assume its the ole component? but I was able to fix some of the problem but it says it is not part of the sysadmin role which it is. I just dont know where to go from here.

thanks

mitch