March 4, 2013 at 12:54 pm
Hi,
So we are changing the SQL service account. The account we are changing to is in a domain user and the previous one was a domain admin. The account is inside a group and the group has been added to the local administrators group per server using a group policy. The group has been given logon rights to the servers, and the sysadmin server role.
When I changed the sql service account to the new account it seemed to go fine. A few days later developers are complaining about linked servers being disconnected. So I logged in to a server through ssms that had a link server to the server that I changed the account on. When I test the connection to the linked server that had the service account changed I get "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)."
Are there specific permissions that a domain account needs to be used for link servers? We temporary resolved the issue by changing the logon under the linked server properties to the sa account, but the sql service still runs as the newly changed account. I have seen some documentation but it's pretty hard to follow i'm pretty new to the more complex areas of sql.
March 4, 2013 at 12:57 pm
Google up Kerberos Double Hop. This is screaming this as the cause, but until you've ran into this headache and a half you're not aware it's hiding out there. My guess is the old login/group was setup via SPN and the new one(s) need this as well.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 11, 2013 at 12:51 pm
Hi,
So a domain admin gave the account permissions to dynamically modify spn's with read and write. This fixed an issue we were having where people could not log in to SSMS from their machines. We did for another account as well, the one that we are having the linked server issue, but this did not resolve the problem. We read somewhere that allowing the computer to delegate the account would fix the issue, so we changed this setting for the machine on the computer object in AD. We still get a failed test to linked servers from our SSMS. If we log in to the machine over RDP and then open up SSMS and test the linked server connection, it works. It get's confusing though because I think when I test it, it's actually using my domain account and not the service account.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply