MSSQL linked server problem
Posted on 2012-03-13
I have tried to make a db link (linked server) between two sql servers without success.
I've read several suggestions and solutions on the Net, but none of them helped us.
Please give me a solution.
The details are:
Server1 (link owner):
sql server 2008 R2
sql server 2008
both of the server are in the same domain, use the same auth. type (NTLM)
My aim is to make the linked server WITHOUT delegation. All of the suggestion what I've read said that
we HAVE TO USE kerberos and delegation. My question is, that is it possible to make linked server without Kerberos (using only NTLM auth.) and delegation?
Current settings are:
server1 and server2 >> Delegation TAB:
Trust this computer for delegation to any service (Kerberos only) [checked]
service runner user (same for both) >> Account TAB
Account is sensitive and cannot be delegated [unchecked]
(Remark: we've tried to checked the above mentioned option - as it's sugested - too, with the same result)
We made the linked server on behalf of the service runner user (it has sysadmin role).
Making the link was possible only as SQL server with "Be made using the login's current sec. context" security option. In this case we could connect to the linked server as administrator, but only for 10 minutes (as this is the default setting for the query timeout). If we tried to use it as a "normal" db user it didn't work. We got the err. msg:
"Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
Anyway I have tried all the variations of linked server properties, ie.: mappings, and different connections types. Unfortunaly none of them worked. Please suggest me a solution.