SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issue with Linking Servers


Issue with Linking Servers

Author
Message
forceman29
forceman29
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 162
This is my current situation.

I have job scheduled to run every morning that collects data from various servers to a local database on Server A and then we will use that data for reports, etc. The owner of the job is the same user as the SQL service service account and this account has access to all our sql servers and runs other jobs without any issues.

When the job is ran I receive this error in the history log:
Executed as user: domain\sqluser. Login failed for user 'sa_Link'. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000] (Error 18452). The step failed.

I have created links to each of the server's I am connecting to.
I have used the following to create the links between the servers:

EXEC sp_addlinkedserver
@server=N'ServerB', -- Remote Computer Name
@srvproduct=N'', -- Not Needed
@provider=N'SQLNCLI', -- SQL Server Driver
@datasrc=N'ServerB'; -- Server Name And Instance

Then under server objects-Linked Servers I see the linked server.
For the Security tab I don't have any users in the local login.
The only change here I have done is select the "Be made using this security context" with the sa_link user account and password. This is a local SQL user and is both on server A and server B with the same password.
Under server options I have also enabled RPC and RPC Out

Now I have setup the sql domain service account setup to delegate via kerberos due to a need the occurred between to other server's and a programmers PC (2nd hop not liking NTLM, etc).
But since this is only one hop NTLM or Kerberos will work just fine.

I've been trying to figure out what I am missing or did incorrectly that isn't allowing the SP's in the job to run.
- Win
- Win
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 707
Hope this help...!

exec sp_dropserver @server = 'ASMREPLICADB' ,@droplogins = 'droplogins'
go

-- Add linked server
EXEC sp_addlinkedserver
@server = 'ASMREPLICADB'
, @srvproduct = ''
, @provider= 'SQLOLEDB'
, @datasrc= '10.164.0.152'

GO

-- Add login (create / use same login account on source and destination )
sp_addlinkedsrvlogin @rmtsrvname = 'ASMREPLICADB'
,@useself = 'FALSE'
,@locallogin = 'ASMDBSQLDBA'
,@rmtuser = 'ASMportaluser'
,@rmtpassword = 'ASMportaluser'
GO

-- Test a query
select top 1 * from [ASMREPLICADB].[geo].[sku]

GO


EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'ASMREPLICADB',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'ASMportaluser',
@rmtpassword = N'ASMportaluser'

Cheers,
- Win
"Dont Judge a Book by its Cover"
forceman29
forceman29
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 162
I shall give that a go and give you an update when i'm done.


Thank you Smile
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12126 Visits: 7444
Unless you've already done it, you might need to enable the "Data Access" option as well

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
forceman29
forceman29
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 162
Thank you both for your help.

I am now able to run job's against that database. I am now having a different issue that will need to be resolved in a different thread.

Thank you again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search