Sql-server – Connection refused when adding ApplicationIntent

sql servert-sql

Environment : AlwaysOn HA group, 2 nodes, 1 database, SQL2012, Server2012R2.

Here is my .NET C# code :

string connectionString = "Server=tcp:ecouteuralwayso.xppocsql.local,1435;Database=AdventureWorks2012;Integrated Security=SSPI;";
string queryString = "select top 30 * from Person.Person;";
using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

[...]

It works perfectly. "ecouteuralwayso.xppocsql.local" accepts connections on TCP1435, I can read my database, no matter which node is the primary.

I want to use Read-Only redirection (http://msdn.microsoft.com/en-us/library/hh710054.aspx), so I configured read-only urls as described in the KB. Here are the results: 

select read_only_routing_url from sys.availability_replicas

Which gives:

TCP://MSSQLCLUST-3.xppocsql.local:1433 [the "primary"]

TCP://POCSQL3.xppocsql.local:1433 [the "secondary"]

Then, I add 

ApplicationIntent=ReadOnly;MultiSubnetFailover=True

To my connection string … But it doesn't work :

[Win32Exception (0x80004005): The remote computer refused the network connection]

[SqlException (0x80131904): 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 – The remote computer refused the network connection.)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +6564850

Without the MultiSubnetFailover, the error is : 

[Win32Exception (0x80004005): No connection could be made because the target machine actively refused it]

Nothing logged in SQLServer…Any ideas?

Best Answer

Ok, got it. I had to force the TCP1433 port in SQL Configuration manager... That was only that. I found it when trying to connect with a wrong password : SQLServer denied connection because of this wrong pass. So, the mistake was between the listener and the server supposed to accept the connection... which was the second one, because of the read-only intent.

And this second one, even if it was listening on TCP1433, wasn't responding without explicitely configuring it in Configuration Manager.

Sorry!

Related Question