?
Solved

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

Posted on 2007-12-01
8
Medium Priority
?
1,020 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 800 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 1200 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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Here's a look at newsworthy articles and community happenings during the last month.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

770 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