September 16, 2004 at 1:15 pm
I have a developer who created a DTS package using an NT authenticated ID. Now that we've migrated the SQL Server to another Domain in Active Directory, I am showing the following error for his jobs:
"The job failed. Unable to determine if the owner (Domain\USer) of job EmployeeDump has server access (reason: Could not obtain information about Windows NT group/user 'Domain\USer'. [SQLSTATE 42000] (Error 8198)).
When I went to check and see what the permissins problems might be, I noticed that all of my NT Authenticated ID are broken after migration - in other words, I can add the Active Directory NT ID's to the SQL Server instance, but I cannot give these ID's permissions to databases because it says they already exist??? I know if I have a sql login that is 'orphaned', I can use "sp_change_users_login" to fix it, but I think I read that this won't work for NT authenticated IDs.
IS there a way to fix this short of dropping ALL of my NT authenticated logins and recreating them????????
September 16, 2004 at 4:13 pm
Since no one has replied, I wanted to take this opportunity to do a little clarifying..
The job I spoke of earlier that won't run.. runs if I change it's owner to an NT Authenticated user from our old Domain.
Our Server was migrated to an Active Directory Domain, and now all of the Active Directory NT Authenticated logins do not work properly. I noticed that when I display the properties (from EM) on the SQL Server 2000 Instance running on the Server that is now in the Active Directory Domain, there is now an 'Active Directory' tab. Do I NEED to add the SQL Server to the Active Directory Domain???? In reading, I also see that you can register Databases in the Active Directory Domain as well... when would you need to do this? and do I NEED to do this in order for my Databases to work properly in an Active Directory Domain environment?
September 17, 2004 at 7:38 am
I'm not sure if I will be any help, but I am interested as my company will be doing the same migration soon.
I have used exec sp_change_users_login with the 'auto_fix' option to fix my NT authentication logins in the past.
I don't know about AD so I can't tell you if the SQL object iself needs to be added even though the server has been added to the AD. I hope someone else can respond.
Michelle
September 17, 2004 at 8:35 am
First a tutorial on how Windows Authentication works.
A user logs into his/her computer. That computer is part of a domain and the user's login is authenticated by the domain controller. Then when they connect to SQL Server on the same domain, the domain controller still recognizes their login credentials. But if SQL Server is on a different domain, that domain controller may not recognize the login so the access fails.
Therefore, in your situation....
Are the users and the SQL Server still on the same domain?
If so, go to Enterprise Manager, expand down to Security, click on Logins and see what it shows as the path for the failing logins. If it's not the correct path, you will have to redo them (I believe you can use the Copy function - right click on the login).
If not, then you need to have both domain controllers update each other with the user information.
-SQLBill
September 17, 2004 at 8:36 am
I hope some will respond as well. I'm sure someone else has been through this.
Also, I have used exec sp_change_users_login with the 'auto_fix' option to fix my NT authentication logins in the past as well, in this case, it did not work.
I found this article that helped me work around it.. worth bookmarking if you will be going through this drill later!
http://support.microsoft.com/default.aspx?scid=kb;en-us;819261&Product=sql2k
if the link doesn't work - it is KB article 819261
It's just so puzzling!!! This user has the exact same permissions in the Instance on his 'old' domain NT account as he does on his 'AD' domain account and the old one works and the new one doesn't. I can even duplicate the error use MY OWN "old" and "AD" domain accounts.. and I have sysadmin on the Instance!!
September 17, 2004 at 8:50 am
Please excuse my ignorance.. but I'm not sure what you mean by checking the 'path'? and I don't see anywhere that I can 'copy' a user.
In our environment, all users and some servers have been migrated to AD - both old and new domains are trusted.. so any one user has the ability to reboot and log onto the old domain or the new one. (I checked with my Server guys and they say the Domain Controllers are being updated properly)
A couple of weeks ago, unbenounced to me, they migrated the Server that hosts this particular SQL Server Instance to the AD domain. I'm trying to figure out why my 'AD' NT authenticated logins aren't working.. and I don't know if I need to add this Instance to AD with the new tab I see in the properties of the sql server???
September 17, 2004 at 11:19 am
Ahh, more information...they migrated your SQL Server to the AD. That means they changed domains - correct?
As for the 'path', in Security, click on Logins. There will be a list of logins. In the 'Name' column, there are two 'versions' of logins. Let's say my SQL Server is on domain MYDOMAIN. Then I will have local logins as:
JonesJJ (or however you create them).
Then DOMAIN logins will be:
MYDOMAIN\JonesJJ.
Most likely your accounts are all still 'local' accounts and not domain accounts. Or they are 'wrong' domain accounts.
You are right, I thought there was an option to copy the login but I can't find it. I suggest taking one account, deleting it, recreating it using the new domain and testing it. See if that solves the issue.
-SQLBill
September 17, 2004 at 12:26 pm
More information.. LOL Sorry about that .. this post has gotten out of Hand!!!
When they migrated the Server, all the Users with NT authenticated logins in 'old' domain were still able to connect to the sql server instance because their own domain account had already been migrated and the domains are trusted -
our Domains are called US (active directory domain) and WCG (old NT domain). The logins I am having a problem with have both the US\user and WCG\user logins in SQL Server.
I deleted my own NT Authenticated login(s) from the Instance and readded only the US\user account. I am still having the problem. The SQL Server is complaining that "it can't determine if the owner of the job (US\user) has Server access - could not obtain information about windows NT group/user" I then added back in my WCG\user account and I can run the job fine????
I still don't understand if I need to add the SQL Instance to Active Directory now that the Server itself is migrated.
September 17, 2004 at 12:53 pm
This particular message is more than likely caused by the fact that the SQL Server Agent service is either running as LocalSystem or is still running under the "legacy" NT Domain account.
Question for you ... how was the SQL Server Agent Service account migrated (if you had one other than the LocalSystem or a local user account)? Are you relying on the SID history "band-aid" method of migration, or was the account actually migrated and re-added to SQL Server? I have seen weird things happen when sysadmins copy the SID history from NT to AD while migrating users ....
September 17, 2004 at 12:54 pm
We just migrated. If the DNS entry was changed, then this will cause a lot of connectivity issues, including getting connected, but then timing out. What we found is that the WINS has to be removed, if there is one there.
If the servers are clustered, then they have to have static addresses (should be anyway).
Also, if you have linked servers setup, you may still experience connectivity issues. We worked around it by fully qualifying the servername in the datasource field. Example: <servername>.<dnsname> and if it is a named instance you will have to qualify it as <servername>.<dnsname>\<instancename>.
Hope this helps.
September 17, 2004 at 1:05 pm
On almost all of my SQL Instances, I start my Services with a Domain Account that has local admin priviledges on the Server. This way I can take advantage of Network Backups if need be and SQL Mail notifications, etc...
This Particular Instance is a DEV Instance and the services are being started with the "system account". would you recommend changing to a domain account in the new domain?
September 17, 2004 at 4:49 pm
Either add an Active Directory Domain account to be the SQL Server Agent service account, or change your DTS scheduled jobs to run as a SQL Login account (i.e. sa or an account with sysadmin authority) if you are running in Mixed Authentication mode. That should get rid of the particular error message that you reported earlier.
September 17, 2004 at 8:04 pm
I know the job runs when I set the user to be a a SQL account and with an account from the old Domain. So.... I changed the startup account for the agent to be a US\Domainaccount (AD Domain) that has local admin on the Server as well as sysadmin in the SQL Instance. I got the same error:
The job failed. Unable to determine if the owner (US\user) of job EmployeeDump 6 has server access (reason: Could not obtain information about Windows NT group/user 'US\user'. [SQLSTATE 42000] (Error 8198)).
This is very frustrating..
September 19, 2004 at 12:02 pm
Very interesting .. we had a similar problem when our servers were migrated to AD ... and reconfiguring the SQL Server Agent worked for our situation. Check out this KB article .. maybe it will be able to help you in isolating the error further, and maybe help determine why the NT Authentication if failing when the xp_logininfo is being called while your job(s) are trying to execute.
September 20, 2004 at 2:13 pm
Thanks for the article. Hope it will help!
Also, I changed the account that starts my SQL Server and SQL Agent Services to a Domain account in the new AD Domain that also has local administrator priviledges on the server - I get the same results. I can 'xp_logininfo' the windows account from the 'old' domain, but I cannot 'xp_logininfo' the account from the new AD Domain without getting this error:
Server: Msg 8198, Level 16, State 34, Procedure xp_logininfo, Line 58
Could not obtain information about Windows NT group/user 'us\jpotucek'.
I have 2 SQL Servers that were built in the new AD domain (not migrated) and I can 'xp_logininfo' account from both domains with no problem.
time to do some more diggin I guess.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply