Linked Server Anonymose Logon Error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Linked Server Anonymose Logon Error

Linked Server Anonymose Logon Error

Rate Topic

Display Mode

Topic Options

Author

Message

lmacdonald

Posted Monday, March 04, 2013 12:54 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:47 AM
Points: 24, Visits: 88

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.

Post #1426421

Evil Kraig F

Posted Monday, March 04, 2013 12:57 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

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.

- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1426424

lmacdonald

Posted Monday, March 11, 2013 12:51 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:47 AM
Points: 24, Visits: 88

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.

Post #1429438

« Prev Topic | Next Topic »

Permissions