Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

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_replmonitorhelpsubscription
      @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
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

from openrowset(''SQLOLEDB'',''SERVER= ' + @Srvsource + ';Trusted_Connection=yes;'',

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.
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92

ASKER

Thanks bibi