Sql-server – ny special configuration for SQL Server implementing Routing List in a heterogeneous enviroment (Client and Server in different domains)

availability-groupssql serverssrs

I had implemented successfully SQL Server Routing List Feature many times… but I have a very special scenario where our Reporting Services Node is located in a different Domain than SQL Server Nodes.

Brief environment description:

  1. Reporting Services/SQL2012 (connection success with other server environments using Application Intent)
  2. Always On Nodes/SQL2014.
  3. There are several security levels between then. Only require port for SQL Server Listener is open.
  4. When I built a Reporting Services DataSource without Application Intent Property, the connection test run with success but as soon I activate ApplicationIntent=ReadOnly;MultiSubnetFailover=True the connection fails throwing the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No such host is known.)

  1. Datasource is using SQL Auth, and the user exist in all participant nodes of the availability group.

Best Answer

I dig a little bit and found that adding the FQDN in the Reporting Services node the functionality works just fine! I also request to Security to check those entries to make sure works fine.

Thanks Sean Gallardy your clue was key for success. Cheers.

Related Question