Solved

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

Posted on 2007-12-01
8
1,014 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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 to another domain controller. Log onto the new domain controller with a user account t…

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now