Login Failure for SQL Agent

Question

Login Failure for SQL Agent

pfrench3

SSC Enthusiast

Points: 190
More actions
February 19, 2003 at 4:49 am

#101385

In order to stop local admin users doing whatever they want in SQL Server on a new installation I have denied access for the Builtin/Administrators login.
Domain Accounts with local admin rights on the machine have been created for the mssqlservice and mssqlagent services.
I created SQL logins with sa rights for the mssqlservice and mssqlagent.
The problem is that the SQL Agent service will not start. The mssql service is okay and will stop and start fine. The SQL agent will only start if I grant the Builtin/Administrators login access. The password is correct I've checked this by logging on the server with it, can't be password problems anyway as it is okay if I grant access to builtin/admins. I've also tried to start the mssqlagent service in Services but it still fails.
Any ideas??
Thanks in advance

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

WHat account is SQLAgent running under. If the account is an Admin and not given access to it's user account then you have denied it.

pfrench3 SSC Enthusiast Points: 190 More actions · Answer 2

SQL Agent is running under a domain user account with local admin rights on the machine. I have added it as a login with sys admin rights. I have also changed the Agent account to run under the mssqlservice account as the mssqlservice is okay but it still will not start.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Because you have already denied the access for BUILTIN\Administrators, so any local admin accounts will be denied too.

Remove BUILTIN\Administrators from your SQL Server logins and grant the local admin account with 'sa' right and see what will happen when starting the Agent Serveic.

pfrench3 SSC Enthusiast Points: 190 More actions · Answer 4

I see your point about denying the login as a deny supersedes anything else but this doesn't explain why the MSSQLService account is okay?

Do you know of any possible problems with completely removing the builtin/admin login?

Thanks for the reply.

pfrench3 SSC Enthusiast Points: 190 More actions · Answer 5

As an aside I did manage to get round it by changing the connection details in the Agent properties to connect with the sa user instead on Windows authentication. But I didn't want to go down this road as I'm uncertain as to maybe causing problems further down the road.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 6

If you have to run Full Text search service, you have to add 'NT Authority\System' to your SQL Server logins with 'sa' right. You also need add your own NT id with 'sa' to mamage SQL Serevr.

I removed login 'BUILTIN\Administrators' from all my SQL Servers including Cluster SQL Server too.

tcady SSC Rookie Points: 46 More actions · Answer 7

i agree with shirley.scott

removing the 'BUILTIN\Administrators' is really bad from my experiences. Any scheduled backups and other 'automated' sql tasks cannot be performed unless 'BUILTIN\Administrators' is there.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 8

I don't agree with the BOL. As long as you add the cluster service account into SQL Server login with 'sa' right, your cluster server will work fine.

I don't think the jobs depend on the login 'BUILTIN\Administrators'. The jobs are really depend on who is the job owner. It the job owner is the member of 'BUILTIN\Administrators', remove 'BUILTIN\Administrators' will definitely make the jobs fail. Change the job owner to 'sa' and I believe the jobs will work fine too.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

Allen is right... there is a KB article that talks about this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;263712

Do note, you can only ever impede administrators. We can still get in, if by no other way than changing your password.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

vishy Mr or Mrs. 500 Points: 596 More actions · Answer 10

builtin/administrators can be removed to deny access to sqlservers for local admins.

problems would arise if jobs need to do network access on that server, then not only "SA" level access on the local server is necessary ,but have network access too like sqlservice does. The same is the case with clustered servers where "is active" thread cannot work.

So my two cents on the matter is you can do it , but not in all situations.