August 6, 2007 at 1:18 pm
Recently I have been tasked with building a new development server so we could retire one of our old boxes.
I had to move some of the SQLServer databases from the old server to the new one. I also had to move some dts packages as well.
I managed to get the databases restored, used the transfer logins dts package to get the logins, and ran the sp_change_users_login procedure to supposedly synch up the users with the logins...
Anyway, we can run the dts packages but when we schedule them as a job we get errors.
The errors we are getting seem to be pertaining to the domain and logins. They vary depending on who owns the job when it is ran and whether it is ran as a scheduled job or invoked by a particular user.
Here is one example:
The job failed. Unable to determine if the owner (AD\asrodrig) of job Provider Directory - CT CMD- Medicaid - Production2 has server access (reason: Could not obtain information about Windows NT group/user 'AD\asrodrig'. [SQLSTATE 42000] (Error 8198)).
Im thinking I missed a step somewhere as far as transferring the logins and users from old server to the new one.
Oh... system info... SQLServer2000 with service pack 4.
oops...wrong forum too...
August 6, 2007 at 1:30 pm
Did you check to see that the new server service accounts for MSSQL and Agent are appropriate for the Owners of the Jobs? Even though MSDB lists the owners, until this is synched with the Agent service, the "Owner not recognized" error can occur.
Elliott
August 6, 2007 at 2:09 pm
How do I do this?
August 6, 2007 at 11:42 pm
Louis -
Is the "old" server by any chance in another domain than the new server? Check the owners of each of the failing jobs and make sure it's a valid user on the new server - and in the same domain as the new server...
August 7, 2007 at 10:54 am
In the interest of just getting it running, you may want to just change the job owner to <sa> and see if that allows the job to run. Also, did you set the service accounts for SQl Server and SQL Agent to log on using a valid Domain account? Is that account a member of the AD domain?
Dan
August 7, 2007 at 1:50 pm
Both servers are in the same domain. I transfered the logins using the dts transfer logins.
I ran sp_change_users_login procedure.
I have tried setting the owner to sa and get a similar error.
And the SQLServer and agent are run using a local user. That is how it was set up on the old machine and it worked over there.
August 7, 2007 at 1:59 pm
the sp_change_users_login procedure is only used for SQL server Logins. When dealing with Windows Authentication, the SIDs assigned to a Domain user are set at the domain controller, so you won't get orphaned database users (that are Windows Authenticated) as long as they have been granted login rights.
At this point, I don't want to assume anything. Can you verify that the server has been added to the domain? I'm not questioning your intelligence, but need to eliminate that as a possibility. How are you logging on to the server? Using an AD Domain user account, or a local server account? Can you access the SQL server via Enterprise manager on a seperate box using windows authentication to connect?
August 7, 2007 at 2:29 pm
Yes I log on to the server using my Domain user account. and I can access the server via enterprise manager from any of our other servers.
August 7, 2007 at 2:35 pm
OK, thanks. In a previous reply, you said you tried setting the owner to sa and received a similar error? Could you post the exact error? ... reason being, it couldn't have complained about being unable to authenticate the Windows NT Group/User when the owner is sa. If it is the same message and the same user is being complained about even though it's sa, maybe there is a clue in that.
August 7, 2007 at 3:14 pm
Step 0: The job failed. The Job was invoked by User AD\lkapp. The last step to run was step 1 (xxxxxxx- xx xxx- xxxxxxxxx - Production2).
Step 1: Executed as user: MSSQL1DEV\SYSTEM. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSCreateProcessTask_1 DTSRun OnError: DTSStep_DTSCreateProcessTask_1, Error = -2147024891 (80070005) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4900 DTSRun OnFinish: DTSStep_DTSCreateProcessTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
August 7, 2007 at 5:57 pm
Well, I got the job to run.
I set the sqlserver service to run under local system and the sqlagent to run under my AD account and the job ran.
I dont know why it works but it is. I will have to have a domain account user setup for the server I guess.
August 7, 2007 at 7:57 pm
Louis -
The secret is the sql agent account - I bet if you check the old server you will find that the sql agent is running under a domain account (e.g. a service account). I would suggest that you configure the sql agent on the new server to use the same account as the old server as your personal domain account probably has a different set of permissions (e.g. group membership) than the service account - your personal account probably requires you to change your password from time to time, etc? Usually service accounts are set up for just the situation you've described.
Joe
August 8, 2007 at 12:34 pm
Actually, the old server had the local system account on the agent and that is why I didnt figure it out sooner. Like I said, it was a dev box. I just used my account as a temporary measure. I'm gonna have an account set up for the agent.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply