Troubleshooting No Endpoints Available Error for DTC Between Domain and Non-Domain SQL Servers

Symptoms

Receiving 'No Endpoints Available' error when attempting to set up Distributed Transactions Coordinator (DTC) between domain and non-domain SQL Servers.

Environment

Mixed environment with SQL Servers on different domains and non-domain machines, Windows Firewall enabled.

Most Likely Causes

Misconfiguration of DTC settings, firewall rules blocking DTC communication, or network connectivity issues.

What to Check First

1. Verify DTC service status on both SQL Servers.
2. Check network connectivity between the servers.
3. Ensure firewall rules allow DTC traffic.

Fix Steps

1. Check DTC service status on both servers.

   Ensure that the Distributed Transaction Coordinator service is running on both SQL Servers.

   Example pattern only. Adjust for your environment before running.

   sc query msdtc

2. Verify network connectivity between the servers.

   Use ping to check if the servers can communicate with each other.

   Example pattern only. Adjust for your environment before running.

   ping <Non-Domain_Server_IP>

3. Check Windows Firewall settings on both servers.

   Ensure that the firewall allows DTC traffic on the required ports (default is TCP 135).

   Safe to run: read-only

   netsh advfirewall firewall show rule name=all | findstr /C:"DTC"

4. Configure DTC security settings.

   Set the DTC security options to allow remote clients and enable network DTC access.

   Example pattern only. Adjust for your environment before running.

   dcomcnfg
   Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC > Properties > Security tab
   Check 'Network DTC Access', 'Allow Remote Clients', and 'Allow Inbound/Outbound' connections.

5. Restart the DTC service.

   After making changes, restart the DTC service on both servers.

   Example pattern only. Adjust for your environment before running.

   net stop msdtc
   net start msdtc

Validation

• Attempt to initiate a distributed transaction between the servers.
• Check for successful completion without errors.

Logs to Check

• Event Viewer > Applications and Services Logs > Microsoft > Windows > Distributed Transaction Coordinator > Operational

Rollback and Escalation

• Revert any changes made to firewall and DTC security settings if the issue persists.

Escalate When

• If the issue is unresolved after following all steps, escalate to the network team for further investigation.

Edge Cases

• If there are multiple firewalls or network appliances between the servers, ensure they are configured to allow DTC traffic.

Notes from the Field

• Document any changes made to firewall rules and DTC settings for future reference.
• Consider testing in a controlled environment before applying changes to production servers.