Jobs fail when a domain account is the owner. 2

[Robbomobb]

Hello!

The jobs which has domainaccounts specified as the job owner fails with the following error message:

"The job failed. Unable to determine if the owner (domain\account) of job TestDB backup has server access (reason: Could not obtain information about Windows NT group/user 'domain\account'. [SQLSTATE 42000] (Error 8198))."

When I run the xp_logoninfo from the queryanalyzer I get the following error:

"Server: Msg 8198, Level 16, State 24, Procedure xp_logininfo, Line 58
Could not obtain information about Windows NT group/user 'domain\account'."

According to Microsoft, state 24 indicates some sort of access problem:

"When the SQL Server engine queried the Windows operating system to retrieve information about a Windows account name, access was denied."

The jobs work just fine when I change the owner to local accounts or native SQL-accounts but what I really what is for it to work under domainaccounts. I've tried starting the SQL-agent with an domain account. I've also tried setting the domainaccount to local admin and ever included them into the system admin role but I didn't get it to work. No doubt this looks like an access problem but I can't understand where and why it fails? Does anyone have a clue?

SQL2k std, w2k3sp1 std

Please excause my poor english.

 

[DrSql]

Is this domian account part of SQL SYSADMIN group. Try to add this account part of local windows Admin group and that should resolve your iussue.

Dr.Sql
Good Luck.

 

[mutley1]

Further to DrSql's good start point, can you check that your SQL server can still see the Domain controller. I know there are a few circumstances that can make this happen, but every time I have had it the server has had problems (even if for a few milliseconds) contating the DC to validate.

M.

 

[SQLBill]

Most likely, your SQL Server is not on the same domain as the user's computers and they don't use a common Domain Controller.

-SQLBill

Posting advice: FAQ481-4875

 

[Robbomobb]

Thanks for all replies.

The account has been added to the local administrators group and is a member of the system administrators SQL role, so my conclution is that access is sufficient concerning the local server and the SQL instance.

I ran a couple of tests to try out the servers connectivity to the domain:
C:\>whoami
amfpension\svcsql04

C:\>dsquery user -name svcsql04
"CN=SvcSql04,OU=Services,OU=Accounts,DC=ad,DC=domain,DC=se"

C:\>hostname
SQL04
From this I can tell that I can logon as the user trying to run the job. I can query the Directory for the user account and I'm running it from the computer holding the SQL-server.

These are the some of the groups returned from the gpresult command.

BUILTIN\Administrators
BUILTIN\Users
REMOTE INTERACTIVE LOGON
NT AUTHORITY\INTERACTIVE
NT AUTHORITY\Authenticated Users


The server is a member of the same domain as the dc and they're on the same subnet. When I ping the dc I get fast replies.

Reply from 172.19.55.6: Bytes=32 time<1ms TTL=128


The server agent i configured to run under a local account who is a member of local administrators group and the system administrators group. Is there a possible problem here?

 

[SQLBill]

The server agent i configured to run under a local account who is a member of local administrators group and the system administrators group. Is there a possible problem here?

A local account does not have permissions on the domain. So the local account probably is not authenticating with the domain. Try making the local account a member of the domain.

-SQLBill

Posting advice: FAQ481-4875

 

[mutley1]

On a slightly relevant note I just want to add a quick question. When I set up the Server and Agent accounts, I always use a domain account for both - I don't tend to use local for the Agent as I often need to do "stuff" where the a domain user is required (for access to certain files etc.) so I automatically run both under a domain account whether required or not. Is this bad practice?

Cheers,

M.

 

[Robbomobb]

Hello and sorry for my absence.

I created a domainaccount which whom I specified as the account to start the SQl Agent with. The account I created is a member of the lokal administrator group and has been granted 'Act as part of operatinve system' rights via the local security policy.

The SQLSERVERAGENT starts just fine but when I try to start a job I get an errormessage saying the server agent isn't started.

When I look in the application event log I get the following messages:
***
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 322
Date: 2006-06-20
Time: 14:49:57
User: N/A
Computer: SQL04
Description:
The data portion of event 17052 from MSSQLSERVER is invalid.
***
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 2006-06-20
Time: 14:49:54
User: N/A
Computer: SQL04
Description:
You do not have sufficient operating system permission to open the SQLServerAgent status event.

Data:
0000: a8 01 00 00 0a 00 00 00 ¨.......
0008: 0c 00 00 00 53 00 51 00 ....S.Q.
0010: 4c 00 30 00 34 00 00 00 L.0.4...
0018: 0e 00 00 00 6d 00 61 00 ....m.a.
0020: 73 00 74 00 65 00 72 00 s.t.e.r.
0028: 00 00 ..
***

In this scenario 'server' service is started with the local account and the 'agent' service is started with the domain account.
Should I configure these services to use the same accout?

//Robbomobb

 

[Robbomobb]

This has been solved by setting the server service to run as the 'Local System' account and the Server Agent service to run as a domain account.

Sorry for the late respons on this thread and thanks to all who responded