Wednesday, February 1, 2012

Could not connect to server '' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. .

Today, actually from 2 days I am trying to fix an issue with linked server. My aim is to trigger job of Server Test02 from server Test01. When I verified linked server it already exists between 2 servers. But when I tried below query from TEST01 server to trigger job of TEST02 server

EXEC test02.MSDB.dbo.sp_start_job @job_name = 'Remote Job'

it has thrown below error:

Server '' is not configured for DATA ACCESS.(Microsoft SQL Server, Error: 7411)

The error says Linked server configured is not having DATA ACCESS. So to provide data access I ran below command:

EXEC master.dbo.sp_serveroption @server=N'TEST02', @optname=N'data access', @optvalue=N'TRUE'

After running above command it fixed the data access issue but I received another error:

Could not connect to server 'TEST02' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.

This error was tricky it was not giving any user name for which remote login is not defined. Then verified the options of linked server and ‘Be made using the login’s current security context’ is checked under ‘security’ tab of linked server properties. ‘RPC Out’ option under ‘Server Options’ was also set to ‘TRUE’. With these settings linked server should work. When tested the linked server connection manually by right clicking on linked server the test is successful.

Then ran ‘select * from sys.sysservers’ and output of this has given me the issue details. The ‘isremote’ value should be ‘0’ for linked server to trigger jobs or SP of linked server but as expected the value of ‘isremote’ was ‘1’. Hence came to confirmation here the issue lies.

I am using SQL SERVER 2008 R2 and to change the value from ‘1’ to ‘0’ instead of updating system tables directly I dropped and created the linked server and YES this fixed the issue.

So for the error below:

Could not connect to server 'servername' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.

Dropping and re-creating the linked server resolved the problem.

2 comments:

  1. Thanks, this fixed it for me. As an aside, following a warning, I used the catalog view sys.servers rather than sys.sysservers, in which case the relevant field appears to be "is_linked", rather than "isremote":

    select is_linked, * FROM sys.servers ORDER BY name

    But either way, dropping then recreating the linked server did the trick.

    Simon Dooley

    ReplyDelete