Insert Error in a Linked Server

Question

Insert Error in a Linked Server

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have the following problem, I am running a script that makes an export of a SQL Server 2012 server to another SQL Server that is tied by a Linked Server, the script is as follows:

INSERT INTO [192.168.10.82].[INTERFAZ_IQWARE].[dbo].[T_FACTURACLI] (IDFactura, FechaDocumento, NombreCliente, NIT, DireccionFacturacion, Folio, Subfolio, Habitacion, Serie, NumeroDoc, IdVendedor, IDClienteIQware, IDClienteSAP, DocType)
SELECT
IDFactura,
FechaDocumento,
NombreCliente,
NIT,
DireccionFacturacion,
Folio,
Subfolio,
Habitacion,
Serie,
NumeroDoc,
IdVendedor,
IDClienteIQware,
IDClienteSAP,
DocType
FROM [T_FACTURACLI]
WHERE (FechaDocumento = (SELECT CurrentHotelDate - 1 FROM prProperty))

The problem is that I log in with the user and the script works correctly, but when setting up a Job so that the process is done automatically, the procedure fails, even if the user is already the owner of the Job and it still does not work.

sql sql-server script db

asked by Gio Gómez 03.05.2018 в 20:12

source

2 answers

Is it possible to convert an Object result into an Arrangement in C #? Modify the bootstrap navbar-brand 4

score 1 · Answer 1

Regardless of which the Owner of the Job is going to execute with the permissions of the account that the SQL Server Agent service has configured (administrative tools - Services)

In this I have 3 suggestions to evaluate:

Add the user of the service to the linked server instance and give him the corresponding permits.

Create a stored procedure and add the exec clause as user = 'sa'

Create an ETL with SSIS

score 0 · Answer 2

This is the error:

Message Executed as user: NT SERVICE \ SQLSERVERAGENT. The OLE DB provider "SQLNCLI11" for linked server "192.168.10.82" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Can not initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "192.168.10.82". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI11" for linked server "192.168.10.82" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412). NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.