Disaster Recovery – How to Connect to DR Server if PROD Site is Down

It can connect from SQL 1 server, which is the primary for AG.

By "connect", do you mean it can ping AG-LISTENER from SQL1?

It sounds like what your problem might be is with the port number you chose for your listener. By choosing 5525, you are selecting a non-default port (1433 would be the default).

So when you try to connect to the listener, what does your connection string look like? I'm guessing it looks something like this:

data source = ag-listener; initial catalog = ...

You have two options here. You can either be explicit with your listener's port number:

data source = ag-listener,5525; initial catalog = ...

Likewise, if you're testing this out with SQL Server Management Studio (SSMS), then for the Connect to Server dialog box, instead of putting in ag-listener for the Server name text box, put in ag-listener,5525.

Or you can change the port that your listener is listening on to 1433 (read the below BOL reference before considering this change):

alter availability group YourAvailabilityGroupName
modify listener 'AG-LISTENER'
(
    port = 1433
);

It is worth noting when you can use the default port (1433). Take a look at this reference on BOL explaining when you can and can't use 1433 for the listener (excerpt copy/pasted below for reference):

You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

(portions omitted for brevity)

If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

EDIT: If the above isn't your problem (as seen from your comment below) then, after you look through the logs, I'd say the next best course of action is to start looking at the network traffic to see what is (and isn't) happening. You can use a network monitoring tool like netmon to accomplish this.

Another thing I'd do, and I realize you said the firewall isn't a problem, but I'd see if the port is actually listening (my favorite tool for this is portqry).

SQL Management Studio (versions prior to 2016)

Unfortunately, there are some caveats that make using Application Intent in SQL Management Studio somewhat painful:

To connect manually with ReadOnly intent, after bringing up the Connect to Server dialog from the Object Explorer, users must remember to:

1. Click Options >>.
2. Go to the Additional Connection Parameters tab.
3. Enter the additional parameter as ApplicationIntent=ReadOnly;
4. (Note: Users must not click the Options << button after entering the Additional Connection Parameters or the parameters will be lost.)
5. Click Connect.
6. Always launch query windows by right-clicking on the desired database in the Object Explorer view and choosing New Query to avoid running into the #3 caveat below.

The caveats that apply are as follows:

1. Although you can get SQL Management Studio to connect with Read Only Intent, it does not store the Additional Connection Parameters when a connection is added to Registered Servers.
2. Behavior when hand editing the locally registered servers in the RegSrvr.xml file to add the Application Intent is extremely inconsistent and will be overwritten any time a change is made through the GUI making this workaround unreliable.
3. The Always On database must be selected before the query window is opened; otherwise, the connection gets routed to the primary server. If you attempt to select the database using the query window's drop down after the query window has already been opened to a non-Always On database, you will get an error dialog. If you try to change the database to an Always On database with a USE statement after the query window has already been opened to a non-Always On database, the results look like this when you attempt to execute the SQL query:

      Msg 979, Level 14, State 1, Line 1
      The target database ('AlwaysOnDatabase') is in an availability group 
      and currently does not allow read only connections. For more 
      information about application intent, see SQL Server Books Online.

SQL Management Studio (versions 2016 or later)

SQL Server Management Studio 2016 or higher can connect with Read Only Application Intent (using the same 6 steps as prior versions) and it does store the Additional Connection Parameters. There are still some caveats:

1. The Object Explorer view will not enumerate any of the tables or other objects in the AlwaysOn databases. Attempting to expand them results in an access denied message.
2. You cannot have a Read Only and a non-Read Only connection to the same listener open at the same time.
3. Intellisense for object names in the database does not work. (Oddly enough, Objects get enumerated just fine in the Query Designer that you can launch using Design Query in Editor... from the right-click menu.)
4. Caveat 3 from the prior versions' caveats still applies.

Third-Party Products

LinqPad stores the whole connection string including Application Intent and the database when you save a connection and therefore might be a viable option for performing Read Only queries against Always On databases.