March 10, 2016 at 8:23 am
By higher direction, we were told to put a logon trigger into a SQL Server 2012 clustered environment. This was tested in a non clustered sql 2008R2 environment (yeah, I know) but in any event, nobody can log in at present through SSMS and the DAC on the machine itself is also denying access. Basically, we're screwed unless I can somehow disable that trigger to see what the problem is. Can anyone offer any advise whatsoever at this point?
March 10, 2016 at 8:33 am
I think logon triggers don't fire when you use DAC (I could be wrong on that), so I'm wondering if you have some other problem going on as well.
You might try starting with the minimal configuration flag and see if it gets you going:
https://msdn.microsoft.com/en-us/library/ms186400(v=sql.110).aspx
The Redneck DBA
March 10, 2016 at 9:06 am
Are you using the DAC locally? Maybe show the connection string so we can be sure this is working?
In a cluster, you might need to log onto the node, not the virtual node. I'll try to find a reference.
March 10, 2016 at 9:11 am
By default, the DAC isn't available remotely, so you need to be on the console of the host: https://msdn.microsoft.com/en-us/library/ms189595.aspx
The DAC doesn't fire logon triggers: https://www.mssqltips.com/sqlservertip/1631/connecting-to-sql-server-with-a-bad-logon-trigger/
March 10, 2016 at 9:41 am
Steve Jones - SSC Editor (3/10/2016)
Are you using the DAC locally? Maybe show the connection string so we can be sure this is working?In a cluster, you might need to log onto the node, not the virtual node. I'll try to find a reference.
I just tried using the DAC on a test cluster/AlwaysOn I have set up and it failed as well. I determined that I didn't have the port DAC was using open on my firewall. After I cleared that up, I'm able to use it locally on a node, or remotely, and it works with both the server names and the listener name.
Might make sure your firewall is letting that traffic through.
I found this info: "SQL Server listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon Database Engine startup. The error log contains the port number the DAC is listening on."
https://msdn.microsoft.com/en-us/library/ms189595.aspx
The Redneck DBA
March 10, 2016 at 9:50 am
Glad it's working.
March 10, 2016 at 9:51 am
Not particularly related to your problem I don't think, but another interesting tidbit I didn't realize...
I don't know when exactly, but somewhere between SQL 2005 and SQL 2012 SSMS can no longer be used to connect via DAC. I have an old SQL2005 box that works fine, but for a SQL 2012 box, I get the following message: "Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design."
The Redneck DBA
March 10, 2016 at 10:21 am
I've used SSMS 2012 to connect. Just need to not allow the OE to connect and put ADMIN in the query connection dialog.
March 10, 2016 at 2:54 pm
A appreciate all the replies and I've just gotten clear of this after one hellacious day. The DAC facility simply doesn't work in our clustered environment so I could never gain access that way -- and I tried everything and every switch available. But, I found another way to get back to a point before the damn trigger was implemented and enabled. I shut down SQL Server via NET STOP command, and physically replaced the MASTER database (mdf, ldf) files retrieved from external backup and when I restarted the systems it came back beautifully. I wouldn't suggest this "solution" to anyone, however it was either that or rebuild from scratch. So far, it all looks fine right now.:crazy:
Edit to add: Tried the SSMS Admin:SERVER route as well -- no joy. Finally I said "ef it" and did what I did.
March 10, 2016 at 4:35 pm
Glad it's working, but you should make sure the DAC works.
If you use SSMS and the ADMIN: trick, you likely need to be on the console of a node. Not on any remote desk.
March 10, 2016 at 4:45 pm
TheRedneckDBA (3/10/2016)
Not particularly related to your problem I don't think, but another interesting tidbit I didn't realize...I don't know when exactly, but somewhere between SQL 2005 and SQL 2012 SSMS can no longer be used to connect via DAC. I have an old SQL2005 box that works fine, but for a SQL 2012 box, I get the following message: "Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design."
Just to clarify what Steve was saying. You can use SSMS, you just need to click File > New > Database Engine Query then in the Server field type ADMIN: SERVERNAME. Object explorer tries to steal your connection of you just try connecting the way you likely normally do.
March 10, 2016 at 5:47 pm
Steve Jones - SSC Editor (3/10/2016)
Glad it's working, but you should make sure the DAC works.If you use SSMS and the ADMIN: trick, you likely need to be on the console of a node. Not on any remote desk.
Tried that, even unplugging outward NICs to make sure mine was the only one connected to it!
Obviously, we have some forensics work ahead and my concerns have been voiced over the lack of DAC capability. But I work for folks who don't even remotely think along these lines so my work is more than cut out for me. Today was in the end a good thing, as all object lessons tend to be. Thanks again for the input, even though I got too buried to even check back here -- it is still much appreciated.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply