bibi92
asked on
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Hello,
I wan't to monitor replication from one server admin with this script but I have this error :
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
/****** Object: StoredProcedure [dbo].[vcf_replmonitor] Script Date: 10/06/2010 23:49:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[vcf_replmonitor]
@Srvsource VARCHAR (80),
@pub_db VARCHAR(80),
@pubname varchar(100)
as
declare @sql nvarchar(4000);
set @sql = 'select repl.*
into replmonitor
from openrowset(''SQLOLEDB'','' SERVER= ' + @Srvsource + ';Trusted_Connection=yes;' ',
''SET FMTONLY OFF;
exec distribution.dbo.sp_replmo nitorhelps ubscriptio n
@publisher = N''''' + @Srvsource + ''''' , @publisher_db = N''''' + @pub_db + ''''' , @publication = N''''' + @pubname + ''''' , @publication_type = 0''
) repl ';
exec sp_executesql @sql;
SELECT publication,
publisher_db,
subscriber,
subscriber_db,
CASE publication_type
WHEN 0 THEN 'Transactional publication'
WHEN 1 THEN 'Snapshot publication'
WHEN 2 THEN 'Merge publication'
ELSE 'Not Known'
END,
CASE SUBTYPE
WHEN 0 THEN 'Push'
WHEN 1 THEN 'Pull'
WHEN 2 THEN 'Anonymous'
ELSE 'Not Known'
END,
CASE status
WHEN 1 THEN 'Started'
WHEN 2 THEN 'Succeeded'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retrying'
WHEN 6 THEN 'Failed'
ELSE 'Not Known'
END,
CASE warning
WHEN 0 THEN 'No Issues in Replication'
ELSE 'Check Replication'
END,
latency,
latencythreshold,
'LatencyStatus'= CASE
WHEN ( latency > latencythreshold ) THEN
'High Latency'
ELSE 'No Latency'
END,
distribution_agentname,
'DistributorStatus'= CASE
WHEN ( Datediff(hh, last_distsync, Getdate()) > 1
) THEN
'Distributor has not executed more than n hour'
ELSE 'Distributor OK'
END
FROM replmonitor
DROP TABLE replmonitor
Thanks
bibi
I wan't to monitor replication from one server admin with this script but I have this error :
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
/****** Object: StoredProcedure [dbo].[vcf_replmonitor] Script Date: 10/06/2010 23:49:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[vcf_replmonitor]
@Srvsource VARCHAR (80),
@pub_db VARCHAR(80),
@pubname varchar(100)
as
declare @sql nvarchar(4000);
set @sql = 'select repl.*
into replmonitor
from openrowset(''SQLOLEDB'',''
''SET FMTONLY OFF;
exec distribution.dbo.sp_replmo
@publisher = N''''' + @Srvsource + ''''' , @publisher_db = N''''' + @pub_db + ''''' , @publication = N''''' + @pubname + ''''' , @publication_type = 0''
) repl ';
exec sp_executesql @sql;
SELECT publication,
publisher_db,
subscriber,
subscriber_db,
CASE publication_type
WHEN 0 THEN 'Transactional publication'
WHEN 1 THEN 'Snapshot publication'
WHEN 2 THEN 'Merge publication'
ELSE 'Not Known'
END,
CASE SUBTYPE
WHEN 0 THEN 'Push'
WHEN 1 THEN 'Pull'
WHEN 2 THEN 'Anonymous'
ELSE 'Not Known'
END,
CASE status
WHEN 1 THEN 'Started'
WHEN 2 THEN 'Succeeded'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retrying'
WHEN 6 THEN 'Failed'
ELSE 'Not Known'
END,
CASE warning
WHEN 0 THEN 'No Issues in Replication'
ELSE 'Check Replication'
END,
latency,
latencythreshold,
'LatencyStatus'= CASE
WHEN ( latency > latencythreshold ) THEN
'High Latency'
ELSE 'No Latency'
END,
distribution_agentname,
'DistributorStatus'= CASE
WHEN ( Datediff(hh, last_distsync, Getdate()) > 1
) THEN
'Distributor has not executed more than n hour'
ELSE 'Distributor OK'
END
FROM replmonitor
DROP TABLE replmonitor
Thanks
bibi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks bibi
Trusted Connection means it is doing a Windows style login.
You most likely have configured the local SQL Server Windows service to start using a local account like LOCAL SERVICE or LOCAL SYSTEM. It is not recognized on the other SQL Server, so it is using the anonymous logon, which the other SQL Server rejects with this error message.