Login failed for login <Login Name> due to trigger execution. Changed database context to ‘master’. Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

Came across in a situation, where I was not able to connect to SQL Managed instance and the main reason was a trigger which was preventing me to logon to SQL managed instance

While connecting, I was getting below login failed “Changed database context to ‘master’.” Error.

Login failed for login <Login Name> due to trigger execution. Changed database context to ‘master’. Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

To resolve this issue, we had to connect to SQL instance using DAC (dedicated administrator connection)

Created a DAC connection followed Microsoft document: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators?view=sql-server-ver15

Added a NSG to open port 1434 to open the DAC connection through SQLCM.

Once connected through DAC, dropped the trigger which was causing an issue.

Post then we tested the connection from SSMS and no more issues were faced.