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
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.
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.
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":
ReplyDeleteselect is_linked, * FROM sys.servers ORDER BY name
But either way, dropping then recreating the linked server did the trick.
Simon Dooley
Glad it helped you Simon
DeleteThank you. Dropping the linked server and recreating it fixed my issue as well.
ReplyDeleteGlad it helped.
DeleteDropping and re-creating just saved my bacon. Thank you!
ReplyDeleteThe reason (according to my resident DB expert) is because when you create the linked server you must be connected to a different node to the target server else it doesn't think it's linked). We have a high availability environment where we have two physical servers (DB1 & DB2) and two logical listeners (SQL01 & SQL02). Normally, SQL01 points to DB1, & SQL02 to DB2. So when you create a linked server when connected to DB1 it's quite happy to create a linked server to SQL01 because the wizard (& probably sp_addlinkserver) thinks they are two different boxes - however the internal SQL bits know they are the same box and therefore does not set the "is_linked" bit.
I think this may have come about when we were setting up a replicated database.
Glad it help you and Thank you for your analysis.
DeleteThank you. Worked for me too.
ReplyDelete