Link to home
Start Free TrialLog in
Avatar of Alex E.
Alex E.

asked on

SQL SERVER 2008 R2 Problem copying database

We have a problem using the copy database feature in SQL Server R2 2008. We have Active Directory and we changed the domain controller name everything works perfect including we can access to the SQL with our default windows account we used always and described at the bottom. And before the domain change we were able to use the feature of copy database inside SQL server manager. After the domain controller name change if we use the feature of copy database in SQL server manager we have this error:

"SQL Server Scheduled Job 'CDW_1478_1478_39' (0xA29577A31E361122337745154479134C) - Status: Failed - Invoked on: 2017-04-30 11:01:35 - Message: The job failed.  Unable to determine if the owner (1478\course) of job CDW_1478_1478_39 has server access (reason: Could not obtain information about Windows NT group/user '1478\course', error code 0x534. [SQLSTATE 42000]"

The old domain controller name with our user was: "company.complex\course" and server name 1478
The new domain controller name with our user was: "mocgih\course" and server name no change 1478

How can we fix that?
Avatar of arnold
arnold
Flag of United States of America image

Edit the SQL job, and update the owner to a valid credential on the new setup.
The owner of the job reflects the user who created it at the time it was
Changing the owner to sa will also resolve the issue.
The issue is company.complex\course user can no longer be validated so SQL has not user context.
Avatar of Alex E.
Alex E.

ASKER

The problem is not a specific job with specific job is ok like you said we used that once. But I'm talking in going to fix the feature when you to "Tasks\Copy Database..." that feature creates a sequential number named job and always is different and of course always say the message at the post. How can we fix the "Tasks\Copy Database..." not a specific job?
How do you login into SQL? The credentials assigned to you is what sets who executes the job, check which credentials does SQL use to start all services.
Avatar of Alex E.

ASKER

yes exactly I suppose is that. But how can I give permissions or whatever to that windows authentication login mode and where? To login in SQL we use SQL login option Windows Authentication mode  only, we never used for SQL manager account to login in SQL manager just windows authentication mode. And like main topic says before renaming the domain controller in windows 2008 was working perfect after the rename of the domain controller stopped working?

Thank you
how do you login? do you use windows authentication? check whether the windows logon you are using is actually cached from your prior DC and not from you new. Was the system from which you connect joined to the new Domain?

was the system from which you are running, rebooted (properties of computer) which domain reference does it reflect new or old?

The AD domain was renamed ..
When renaming the references are preserved, but a system that has not rebooted, with the new name, will likely still refect the old name reference.....

try login in with the sa or any other sql login that has sysadmin rights and see if the issue exist.  as you noted, the issue is not with the server, sql but with the account you use to connect and attempt to run the task, copy DB.
Avatar of Alex E.

ASKER

To login windows before and after the renaming of the domain is like the main post says:

The old domain controller name with our user was: "company.complex\course" and server name 1478
The new domain controller name with our user was: "mocgih\course" and server name no change

Is the same kind of login I use for SQL Server because the login screen of Windows SQL Server is in ghost mode (you can't alter) the user name shows the new domain information windows authentication "mocgih\course". Before the domain rename there appeared "company.complex\course" then I suppose the SQL server detected the change of domain because the user name field like you see you can't alter but is updated with the new domain name. But anyway where can be checked that cache you mention?

This has a month of course the machine was rebooted a lot of times.

If I login with "sa" it works perfectly but we don't want to use sa account. We want like it was before with windows authentication mode. And like I mentioned in user name of sql server in ghost mode is updated the new name of the domain controller and we can do things except copy database feature
I realize that, and I pointed to the error which reflects the login credentials as based on the servername 1478\course instead of domain based credentials. While on a DC there are no local users so 1478\course is the same as mocgih\course but on the SQL server there is a significant distinction/permissions granted between 1478\course and mocgih\course.

I am not suggesting you use an SA command, It was a way to illustrate the cause of your issue.
For some reason, when you login into the system on which you are and then connect from that system if not the same as the SQL server via SSMS you are seen as 1478\Course and your rights there by are limited on the SQL server.
Check the database server security\logins listing and then the Database higherarchy security logins
make sure to update the logins to have mocgih\course in both with appropriate rights. This can be done using the sa account login.
see if after the change, your mocgih\course logged in session when connecting via ssms with windows authentication resolves your copy db error for which you opened this question.

reference 1478\course of old DC and 1478\Course of new DC while appearing the same are actually are not the same.
Avatar of Alex E.

ASKER

I see this resolution to configure a specific job:


Have your DBA, run the SQL server Agent Jobs with an account that has sysadmin rights.

1. Open SQL Management Studio

2. Expand SQL Server Agent and Right click on one of the MessageStats jobs and select Properties.

3. On the General Screen change the Owner to an account that has sysadmin rights and click OK.


And it works if I change owner to SA to a specific job but the question is simply how can I give sysadmin rights similar to SA to my new domain controller renamed user: "mocgih\course"? Appears has limitations and I went to security/logins and right click on that user and then server roles and it has all checked, sysadmin, etc. everything and nothing still not working. Where can I give power to my user similar to SA then? And where exactly I'm lost I don't if inside SQL server 2008 and where or in windows AD?

Thank you
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Alex E.

ASKER

Thank you