0

We have seen this problem before and fixed it by looking at the KERBOS configuration program. But now I have something odd. Both I and another DBA have the same SysAdmin perms to both boxes. But when we are on the same server trying to run a query off the same linked server, mine works and yet his gives him the error above. What can be causing that? I would have assumed if all things are equal we'd either both be successful or both get the same error.

This is when we are running it in SSMS from our Terminal server. But when we both actually RDP onto the server we are running this from (not the linked server server) it works fine without any problems.

The security has nothing in the mapping and the Be made using the login's current security context is checked. Here is the create statement for the linked server:

@srvproduct=N'SQL Server'
@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
@optname=N'collation compatible', @optvalue=N'false'
@optname=N'data access', @optvalue=N'true'
@optname=N'dist', @optvalue=N'false'
@optname=N'pub', @optvalue=N'false'
@optname=N'rpc', @optvalue=N'true'
@optname=N'rpc out', @optvalue=N'true'
@optname=N'sub', @optvalue=N'false'
@optname=N'connect timeout', @optvalue=N'0'
@optname=N'collation name', @optvalue=NULL
@optname=N'lazy schema validation', @optvalue=N'false'
@optname=N'query timeout', @optvalue=N'0'
@optname=N'use remote collation', @optvalue=N'true'
@optname=N'remote proc transaction promotion', @optvalue=N'true'
Chris Woods
  • 1,711
  • 13
  • 21
  • Post the details of the linked server and how it's configured, specifically the security section and how authentication is handled. – Jonathan Fite Feb 10 '17 at 16:06
  • @JonathanFite Added, let me know if that is what you were looking for – Chris Woods Feb 10 '17 at 16:15
  • Chris, do you both have permissions (and the same permissions) into the target linked server? – Jonathan Fite Feb 10 '17 at 17:54
  • Jonathan, Yep we do. – Chris Woods Feb 10 '17 at 17:55
  • Does the answer in this question help? Unfortunately, I'm out of ideas besides that. Best of luck. http://dba.stackexchange.com/questions/44795/how-can-i-get-my-linked-server-working-using-windows-authentication – Jonathan Fite Feb 10 '17 at 18:02
  • Thanks, yes I am sure that is the overall problem. What I am confused about is why it is impacting him but not me. – Chris Woods Feb 10 '17 at 18:53
  • Do both of you have the `ALTER ANY LINKED SERVER` and `CONTROL SERVER` permission set up correctly? Do both of you have a login on the target server and a user in the target database(s)? Failing all else, try recreating the linked server but with a SQL Login created specifically for the linked servers (on both the source and destination) and create a corresponding user with appropriate permissions in the database(s). – SQL_Deadwood Feb 10 '17 at 23:54
  • Thanks, we are both SysAdmins, so those two permissions shouldn't matter, right? – Chris Woods Feb 12 '17 at 06:41

0 Answers0