February 9, 2022 at 9:19 am
Has this ever worked?
Not from the DMZ host, no, but it is something that has only been needed very recently.
From everywhere else on the LAN both standalone instances and AG instances are perfectly fine, for both SSMS and real-world user access; most of the AGs have been in production for nearly a year. All SQL workloads are on SSD and have the data location separation in terms of different disks and these disks are presented as unique LUNs from the SAN. The hypervisor is Hyper-V and I have given each VHDX file its own SCSI controller to meet that best practice also. Some of the AGs are hosting 30+ databases.
Not sure if it helps, but for example backing up a database from its old SQL instance takes say 30 minutes. Restoring this database on its new AG instance takes like 5 minutes in comparison. That shows the performance of the new instances and their underlying infrastructure. Again, in the non-DMZ world we are seeing no performance drops in AGs vs standalone instances in this regard.
AV is provided by Sophos Central and every SQL host has the same policies applied, whether that host is running WSFC or not (but WSFC exclusions are there as well).
So I don't believe it's an AG/AV-configuration issue. This issue is absolutely isolated to the DMZ host, whether it is access from SSMS or an actual app.
February 9, 2022 at 6:00 pm
Were you able to confirm that using SQL auth works - but windows auth has the issue? Or do you see the issue regardless of how you are logging into SQL Server?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 9, 2022 at 6:13 pm
SQL auth has the issue.
The DMZ host is not domain joined so can't use Windows auth anyway (well not without trying to run SSMS as another user and specifying domain\username format)
February 9, 2022 at 8:53 pm
The DMZ host is not domain joined so can't use Windows auth anyway (well not without trying to run SSMS as another user and specifying domain\username format)
If you can create a domain in the DMZ, I think your problems will go away.
You said you are using a Windows account as you service account. Since this is not joined to a domain, is it an account local to the server? I'm betting the rights assignments are not correct if it is.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 10, 2022 at 3:46 am
This was removed by the editor as SPAM
February 10, 2022 at 6:45 am
So just to repeat, all the SQL Server instances are LAN-based, domain joined and using domain accounts for the services.
I am simply having this problem connecting to any AG-based, domain-joined SQL instance, from the non-domain joined DMZ device.
There is no SQL instance inside the DMZ. Just SSMS running on a DMZ device.
February 10, 2022 at 1:37 pm
Is the SQL Browsing service running on the machines?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 11, 2022 at 2:51 pm
Yes it is. As mentioned, I can connect to all instances (standalone and AG) from SSMS just fine.
The problem on AG instances is expanding the Databases node and querying user databases with T-SQL; expanding the System databases node is fine, and querying them with T-SQL is also fine.
We've tried connecting to them from SSMS with AG Listener name and SERVER\INSTANCE conventions. Same problem with both.
So glad I'm not the only one stumped!
February 11, 2022 at 3:04 pm
A thought (it happens :-)):
Have you tried looking at and comparing the routing tables on both servers to see if packages take the same route from both sides? Done a traceroute from the AGs to the dmz server?
Even though the connection is only established from the dmz server, the ethernet packages have to find their way back from the targeted AG. If some packages are sent the wrong way, you may have trouble brewing.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply