(I hope this is the proper place for posting, as it is about the database and its connection.)
SQL Server 2019 / ASP Classic / ASP.NET
I followed the information from this page. ReadOnly Routing (sqlservercentral.com) and have everything set up and all tests passed in SQL Server for the AOAG.
With the ASP Classic ConnectionString test.
(Once I get the asp classic ConnectionString working, I will implement the asp.net version.)
With the below code, if I run the SQLListener,2433, the ConnectionString works.
(Makes testing easier, creating the Variable and going between them instead of changing it in the ConnectionString.)
'getSerInstance = "SQLListener,2433" ' Works
'getSerInstance = "sql1.domain.local,1433" ' Does not Work
'getSerInstance = "SQLListener,1433" ' Does not Work
Set siteconn = CreateObject("ADODB.Connection")
siteconn.ConnectionString = "Provider=MSOLEDBSQL;Server=tcp:"&getSerInstance&";
Database=dbname;ApplicationIntent=ReadOnly;IntegratedSecurity=SSPI;
MultiSubnetFailover=True;User ID=USERNAME;Password=PASSWORD;Max Pool Size=1024;"
siteconn.Open
Running SQLListener,2433 works, but once I run either of the other two, I receive the error.
'80004005' Invalid connection string attribute.
Added: User ID=USERNAME; Password=PASSWORD; was the only way to make "SQLListener,2433" work. So it is left in, in hopes that it would also help to make one of the others work as well.
In each of the SQL Instances, the following Firewall rules are implemented.
New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "SQLServer Listener instance" -Direction Inbound -LocalPort 2433 -Protocol TCP -Action Allow
