Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Ressourcen für IT-Professionals >
> 
> 
Unable to validate replication with sys.sp_validate_replica_hosts_as_publishers for Availability Group

已答复 Unable to validate replication with sys.sp_validate_replica_hosts_as_publishers for Availability Group

  • Samstag, 23. Februar 2013 01:12
     
     
    Anmelden
    0

    Im getting error the following error when trying to run the above SP to validate my replication schema:

    Msg 21892, Level 16, State 1, Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 60

    Unable to query sys.availability_replicas at the availability group primary associated with virtual network name 'MyAGListener' for the server names of the member replicas: error = 18456, error message = Error 18456, Level 14, State 1, Message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'..',

    I've already tried to manually register the AG listener name with setSPN, and confirmed that the name, all the AG replicas, and the replication distributor and subscribers have registered SPNs against the SQL Service Account (going so far as checking ADSIEdit.msc on my domain controller), but I'm still getting the error even though I'm running the query under the same account (which is SA on all instances, as well as domain admin on all boxes), and when I check the connection with "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID" on the primary node and AG listener, it still shows as connecting using NTLM instead of KERBEROS.

    Funnily, before I ran setSPN on the AG listener name, only the AG listener and primary AG replica (where WSFC and AG were set-up from) returning NTLM.  All other replicas, subscribers, and distributor were returning KERBEROS.  If I failed over my AG group to a replica running with KERBEROS, connecting as the AG listener name still reported NTLM.  I failed back to my primary replica and after I ran setSPN, I still go the same result, except when I failed over to a secondary replica with KERBEROS, AG listener also returned KERBEROS.  However running the above validate replica hosts SP still gave me the same error.

    By the way, regardless of the error, I still get the first result back from the validate replica hosts SP returning the correct AG listener name.  Also failing over is still automatically failing over transactional replication, so whatever is happening doesn't seem to be detrimental to the process.

    Am I missing something?

    Diane



    • Bearbeitet Diane Sithoo Samstag, 23. Februar 2013 16:52
    • Bearbeitet Diane Sithoo Samstag, 23. Februar 2013 16:57 More details provided
    •  
     

Alle Antworten

  • Montag, 25. Februar 2013 08:39
     
     
    Anmelden
    0

    resolved

    http://social.msdn.microsoft.com/Forums/sk/sqlreplication/thread/3ec87e1e-0596-4e63-85d3-0cdaf1690124

     
  • Montag, 25. Februar 2013 15:40
    Moderator
     
     
    Anmelden
    0

    I am not sure if this solves the issue. Diane you need to drill down on your SQL Server Error Log and see if you see a message like the one below:

    Message
    The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/PUBLISHER ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

    It should happen close to the start of the log.

    I suspect that the linked servers are using the NT_Authority account as opposed to a sql or windows account. You might need to use contained logins as well.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Montag, 25. Februar 2013 17:57
     
     
    Anmelden
    0

    Hi Hilary,

    I couldn't find the message you listed in the log, however I did find the following in MyAGPrimary, after failing over to MyAGSyncSecondary and then restarting MyAGPrimary:

    The service account is 'MyDomain\MyServiceAccount'. This is an informational message; no user action is required.

    A little later:

    SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

    A bit later afer starting up user dbs:

    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/MyAGPrimary.MyDomain.com ] for the SQL Server service.

    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/MyAGPrimary.MyDomain.com:1433 ] for the SQL Server service.

    A little later after:

    A connection timeout has occurred while attempting to establish a connection to availability replica 'MyAGSyncSecondary' with id [########-####-####-####-############]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

    This was followed by in the same second:

    A connection for availability group 'MyAGGroup' from availability replica 'MyAGPrimary' with id  [########-####-####-####-############] to 'MyAGSyncSecondary' with id [########-####-####-####-############] has been successfully established.  This is an informational message only. No user action is required.

    Which I assume meant it was alright.  After failing back over to MyAGPrimary, I saw the message :

    A connection timeout has occurred on a previously established connection to availability replica 'MyAGSyncSecondary' with id [########-####-####-####-############].  Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

    And a bit later:

    A connection for availability group 'MyAGGroup' from availability replica 'MyAGPrimary' with id  [########-####-####-####-############] to 'MyAGSyncSecondary' with id [########-####-####-####-############] has been successfully established.  This is an informational message only. No user action is required.

    And:

    A connection for availability group 'MyAGGroup' from availability replica 'MyAGPrimary' with id  [########-####-####-####-############] to 'MyAGASyncSecondary' with id [########-####-####-####-############] has been successfully established.  This is an informational message only. No user action is required.

    And finally before the DBCC CHECKDBs:

    Failed to verify Authenticode signature on DLL 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\msxmlsql.dll'.

    Any clues?  There didn't seem to be any SPN messages related to my listener 'MyAGListener' either, even though I setSPN for it too.

    Diane


     
  • Montag, 25. Februar 2013 18:32
    Moderator
     
     
    Anmelden
    0
    This tells me that your VNN is successfully registered with AD and Kerberos should be working. I am not sure what the problem is here.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Montag, 25. Februar 2013 18:58
     
      Enthält Code
    Anmelden
    0

    I forgot to include one important error message which is the one I get when the following code:

    USE distribution
GO
DECLARE @redirected_publisher sysname
EXEC sys.sp_validate_replica_hosts_as_publishers 
	@original_publisher = 'MyAGPrimary',
	@publisher_db = 'my_db',
	@redirected_publisher = @redirected_publisher OUTPUT
SELECT @redirected_publisher  -- If error or NULL is returned, something is wrong.

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 172.16.x.x], where 172.16.x.x is the IP of MyTransReplDistributor.

    Also, here is the partial list when I run setspn -L MyDomain\MyServiceAccount:

    Registered ServicePrincipalNames for CN=PRE Database Service,OU=Service Users,OU
    =Information Services,DC=MyDomain,DC=com:
            MSSQLSvc/MyAGListener.MyDomain.com:1433
            MSSQLSvc/MyAGListener
            MSSQLSvc/MyAGPrimary.MyDomain.com:1433
            MSSQLSvc/MyAGPrimary.MyDomain.com
            MSSQLSvc/MyAGSyncSecondary.MyDomain.com:1433
            MSSQLSvc/MyAGSyncSecondary.MyDomain.com
            MSSQLSvc/MyAGASyncSecondary.MyDomain.com:1433
            MSSQLSvc/MyAGASyncSecondary.MyDomain.com
            MSSQLSvc/MyTransReplDistributor.MyDomain.com:1433
            MSSQLSvc/MyTransReplDistributor.MyDomain.com
            MSSQLSvc/MyTransReplSubscriber1.MyDomain.com:1433
            MSSQLSvc/MyTransReplSubscriber2.MyDomain.com:49331
            MSSQLSvc/MyTransReplSubscriber2.MyDomain.com:49261

    where the last 2 entries are dynamic ports because I'm using named instances, even though I ran setspn - A MSSQLSvc/MyTransReplSubscriber2.MyDomain.com:NamedInstance1 MyDomain\MyServiceAccount and setspn - A MSSQLSvc/MyTransReplSubscriber2.MyDomain.com:NamedInstance2 MyDomain\MyServiceAccount for them.

    Diane




     
  • Montag, 25. Februar 2013 20:06
     
     Beantwortet
    Anmelden
    0

    Okay, I think I've solved this although it was a bit convoluted not having all the answers up front.  I googled the error I was getting in the SQL Server Event Log "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided".

    That led me to this MSDN Forum "SQL 2012 Linked Servers and Delegation", which then led me to this link "How to Implement Kerberos Constrained Delegation with SQL Server 2008".  So even though I'm running SQL 2012 on Windows 2012, my AD is running on a Windows 2008 R2 server.  Anyways, the important thing to note here is that in addition to setting SPN for the AG Listener and all the nodes involved, I had to also configure constrained delegation since that page specifically states "... you can’t rely on a user supplied SPN and configure Kerberos constrained delegation scenarios like a linked server". 

    Hence I followed the steps to explicitly Trust services only specified service account user using Kerberos only for MyServiceAccount (in my case for the purposes of testing, I'm using the same account for all SQL services for all servers in my environment) for the following:

    Service Type         User or Computer                                      Port
    MSSQLSvc              MyTransReplDistributor.MyDomain.com
    MSSQLSvc              MyTransReplDistributor.MyDomain.com       1433
    MSSQLSvc              MyAGListener.MyDomain.com
    MSSQLSvc              MyAGListener.MyDomain.com                      1433
    MSSQLSvc              MyAGPrimary.MyDomain.com
    MSSQLSvc              MyAGPrimary.MyDomain.com                       1433
    MSSQLSvc              MyAGSyncSecondary.MyDomain.com
    MSSQLSvc              MyAGSyncSecondary.MyDomain.com           1433
    MSSQLSvc              MyAGASyncSecondary.MyDomain.com
    MSSQLSvc              MyAGASyncSecondary.MyDomain.com         1433

    After that, I restarted MyTransReplDistributor again and now the validation works.

    Hopefully, this helps anyone else with the same issue.  Right now, I have purposely excluded my transactional replication subscribers from the list, but if someone could let me know whether that's wrong, or whether this list includes too much, I'd be most appreciative!

    Diane

     
  • Mittwoch, 6. März 2013 23:34
     
     Vorgeschlagene Antwort
    Anmelden
    0

    Hi folks - I just ran into this as well.  The simple fix in my case was to execute the validation procedure sys.sp_validate_replica_hosts_as_publishers directly on the distribution server (via Remote Desktop, etc.), instead of from my own client.  This resolves the double-hop issue that is resulting in the anonymous NTLM negotiation attempt on the publisher, as seen in netmon traces with empty user name & domain field data in the NTLM challenge response from distribution server to publisher.

    Best of luck,

    Eric

    • Als Antwort vorgeschlagen EricHoll Donnerstag, 7. März 2013 19:13
    •