Solved

After Domain Migration, SQL Job Failes to authenticate user. SQL state 42000 Error 8198

Posted on 2007-12-01
8
1,016 Views
Last Modified: 2013-12-05
We migrated a windows 2000 server from an nt4 domain to a full 2003 domain. Afterwards we cannot run jobs under a sql service account. All the sql services are running fine under this account, but we can't run jobs under this account. This account works with other sql servers that were migrated.


The job failed:unable to determine if the owner (blah\blah) of job Test1 has server access(reason:could not obtain information about windows NT group user 'blah\blah')
SQL state 42000 Error 8198


I've seen alot of posts saying to change the user that this job runs under to SA, but why should I have to if this works on other servers. Is there anyway to make this work with the sql service account we created in AD?

Account has log on locally rights and is part of the local admin group.
0
Comment
Question by:WinPE
  • 3
  • 3
  • 2
8 Comments
 
LVL 29

Expert Comment

by:mass2612
ID: 20389721
Hi,

As you probably know the SQL agent uses the built in SP xp_logininfo to check the credentials of the account running the SQL job. This error generally means that this check is failing.

Have you tried adding the pre-Windows 2000 access group as described here - http://support.microsoft.com/kb/325363.

You have probably already reviewed these articles: -
http://support.microsoft.com/kb/834124
http://support.microsoft.com/kb/241643/
0
 

Author Comment

by:WinPE
ID: 20391340
Yeah we already have everyone added plus the user in that group... and unfortunatly I have seen those two posts :(...


This has to be stale due to the migration of computers... Is there anyway I can refresh the xp_logininfo...


I feel like its using credentials from the old nt4 domain account. I just dont understand why some domain accounts can work, and others cant.

0
 
LVL 29

Expert Comment

by:mass2612
ID: 20392222
Are you able to view the user running the SP via a query : -
EXEC xp_logininfo
or
EXEC xp_logininfo "BUILTIN\Administrators", @option = 'members'

Can you execute
EXEC xp_logininfo "DOMAIN\USERNAME"
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:WinPE
ID: 20394975
Yah when I run the first query it gives me two domain user accounts, 1 local and these two..
NTDomain\servicesql
DOMAIN\ServiceSQL

now the capital S's don't throw off the authentication by any chance to they? I know that way out there..


when I run the second command (on the actual user), this is what I get...
(1 row(s) affected)

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


0
 
LVL 29

Assisted Solution

by:mass2612
mass2612 earned 200 total points
ID: 20416995
I don't think there would be a problem due to the case. I'm stuck for what to try next sorry. I have also placed a reference to this Q in the community Advisor section to try to draw the attention of a true MS SQL expert here to help us out.
0
 
LVL 7

Accepted Solution

by:
macentrap earned 300 total points
ID: 20417213
Had this problem while ago, Only think of this right now:
Sloved it by setting the server service to run as the 'Local System' account and the Server Agent service to run as a domain account
0
 
LVL 7

Expert Comment

by:macentrap
ID: 20417288
Try Rebooting the ADS, first
0
 

Author Closing Comment

by:WinPE
ID: 31412137
Thanks Guys, our sql guys just ended up using a dif sql server acct, so much for testing :(
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article runs through the process of deploying a single EXE application selectively to a group of user.
Active Directory security has been a hot topic of late, and for good reason. With 90% of the world’s organization using this system to manage access to all parts of their IT infrastructure, knowing how to protect against threats and keep vulnerabil…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question