none
SQL Server 2016 DB Mirroring Error RRS feed

  • Question

  • I'm seeing this error in the SQL logs every 100 seconds for a mirrored DB:

    Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://MANAGE3.xyz.com:5022'.

    Error: 1474 Severity: 16 State: 1.

    Note: MANAGE3 is the witness.  Error occurs on both partners.  Also occurs on the witness for the connection to the partners, but not between partners.  Witness is also SQL 2016 (Express edition).  The partners are both Standard edition.

    I've been using mirroring for years on SQL 2005, 2008 but this is the first time on 2016.  No problem at all with the earlier editions of SQL Server, only seeing this on 2016.  

    Couple others are having same problem reported here:

    http://dba.stackexchange.com/questions/153385/mssql-mirror-configuration-troubleshooting-witness-server-forcibly-disconnectin

    I don't have any firewalls between the servers.  Earlier versions of SQL Server work just fine on the same network.

    Thoughts?

    Thursday, January 12, 2017 9:47 PM

All replies

  • What this means is that the SQL server process cannot connect to the remote sql server. It could mean that SQL server is not running, it could mean that it could not find the instance SQL Server is running on on the remote machine, or it could mean that the port is not open on the remote machine.

    I know you do not have firewalls between the two machines, but by default windows is locked down for most ports, so start up the firewall server and open it up for port 5022 in bound and outbound. If you have an instance ensure that the browser is running and you may need to restart SQL server for the browser to be able to redirect incoming requests to it.

    Ensure SQL Server on the remote server is using port 5022.

    select * from sys.database_mirroring_endpoints

    Thursday, January 12, 2017 11:12 PM
  • Hi,

    This could be because of the CONNECT permission. Grant CONNECT permission to all remote SQL instances on current instance's DB Mirror Endpoint.

    1. ON Witness Server [MANAGE3]
    USE [master]
    GO
    CREATE LOGIN [DOMAIN\ServiceAccount_PrimaryServer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    CREATE LOGIN [DOMAIN\ServiceAccount_SecondaryServer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    
    GRANT CONNECT ON ENDPOINT::[mirroring_endpoint] TO [DOMAIN\ServiceAccount_PrimaryServer]; 
    GRANT CONNECT ON ENDPOINT::[mirroring_endpoint] TO [DOMAIN\ServiceAccount_SecondaryServer]; 
    
    2. ON Primary Server [MANAGE1]
    USE [master]
    GO
    CREATE LOGIN [DOMAIN\ServiceAccount_WitnessServer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    CREATE LOGIN [DOMAIN\ServiceAccount_SecondaryServer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    
    GRANT CONNECT ON ENDPOINT::[mirroring_endpoint] TO [DOMAIN\ServiceAccount_WitnessServer]; 
    GRANT CONNECT ON ENDPOINT::[mirroring_endpoint] TO [DOMAIN\ServiceAccount_SecondaryServer]; 
    
    3. ON Primary Server [MANAGE2]
    USE [master]
    GO
    CREATE LOGIN [DOMAIN\ServiceAccount_WitnessServer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    CREATE LOGIN [DOMAIN\ServiceAccount_PrimaryServer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    
    GRANT CONNECT ON ENDPOINT::[mirroring_endpoint] TO [DOMAIN\ServiceAccount_WitnessServer]; 
    GRANT CONNECT ON ENDPOINT::[mirroring_endpoint] TO [DOMAIN\ServiceAccount_PrimaryServer]; 
    
    * Assuming all the three nodes are running with different service accounts. You might be using just one service account across all the three servers.
    

    Query to verify the permission on the nodes using the following:

    , p.permission_name, p.state_desc as permission_state, e.state_desc endpoint_state
    FROM sys.server_permissions p
    INNER JOIN sys.endpoints e ON p.major_id = e.endpoint_id
    INNER JOIN sys.server_principals s ON p.grantee_principal_id = s.principal_id
    WHERE p.class_desc = 'ENDPOINT' AND e.type_desc = 'DATABASE_MIRRORING'

    e.g. On Witness Server, it should look something like:

    mirror_endpoint_name login_name permission_name permission_state endpoint_state
    mirroring_endpoint DOMAIN\ServiceAccount_PrimaryServer CONNECT GRANT STARTED
    mirroring_endpoint DOMAIN\ServiceAccount_SecondaryServer CONNECT GRANT STARTED


    Query to check the service account connect permission on the DB Mirror endpoints:

    SELECT EndPnt.name, SvrPerm.STATE,CONVERT(nvarchar(38), suser_name(SvrPerm.grantor_principal_id)) AS GRANTOR,
       SvrPerm.TYPE AS PERMISSION,CONVERT(nvarchar(46),suser_name(SvrPerm.grantee_principal_id)) AS GRANTEE
       FROM sys.server_permissions SvrPerm, sys.endpoints EndPnt
       WHERE SvrPerm.major_id = EndPnt.endpoint_id
       ORDER BY Permission, grantor, grantee;

    e.g. On Witness Server, it should look something like:

    name STATE GRANTOR PERMISSION GRANTEE
    mirroring_endpoint G sa CO   DOMAIN\ServiceAccount_PrimaryServer
    mirroring_endpoint G sa CO   DOMAIN\ServiceAccount_SecondaryServer
    TSQL Local Machine G sa CO   public
    TSQL Named Pipes G sa CO   public
    TSQL Default TCP G sa CO   public
    TSQL Default VIA G sa CO   public


    Hope it's helpful.

    Cheers,

    Sunit





    • Edited by Sunit Kapur Friday, January 13, 2017 12:06 AM
    Thursday, January 12, 2017 11:49 PM
  • Thanks for the SQL - ran that and it returned a row for the Partner role, looks good there.

    Also, the Windows Firewall is disabled for the domain profile.  Double checked by telnet'ing to the witness server using port 5022, worked fine.

    Mirroring appears to be working, see the status as "synchronized" but still getting these errors.  Occasionally the mirroring completely breaks so there is definitely something wrong.

    Thursday, January 19, 2017 2:40 PM
  • Thanks for putting all that info together.  I ran that query and do see the correct info, appears the endpoints and the grants are setup correctly.

    Still getting the error.  One question - I'm running the SQL Server service under the Network Service acct.  I'm granting Connect access to the endpoints for the machine accounts (domain\machine$).  This is what I've always done in previous versions of SQL server and always worked perfect.  Is this still valid?

    Thanks for the input!

    Thursday, January 19, 2017 2:50 PM
  • Looks like the SP1 CU1 just released from MS fixed the problem!  Woohoo!

    https://support.microsoft.com/en-us/help/3208177/cumulative-update-1-for-sql-server-2016-sp1

    Friday, January 20, 2017 11:25 PM