Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with Linking Servers Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 3:49 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 9:27 AM
Points: 18, Visits: 142
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.
Post #1443518
Posted Wednesday, April 17, 2013 11:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:45 PM
Points: 119, Visits: 490
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"
Post #1443598
Posted Thursday, April 18, 2013 12:10 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 9:27 AM
Points: 18, Visits: 142
I shall give that a go and give you an update when i'm done.


Thank you :)
Post #1444069
Posted Thursday, April 18, 2013 1:51 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,910, Visits: 7,138
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"
Post #1444125
Posted Wednesday, April 24, 2013 7:36 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 9:27 AM
Points: 18, Visits: 142
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.
Post #1445967
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse