Monitoring stopped Incorrect credentials or insufficient permissions

Azhkanizkael

Hey!

It seems to be every 2-3 weeks we get disconnected from monitoring an Azure Managed Instance. 

When I dive into the log file, I find this in the error:

  [Class]: '14'

  [LineNumber]: '19'

  [Message]: 'Connections to this database are no longer allowed.'

  [Number]: '40863'

  [Procedure]: ''

  [Server]: '*.database.windows.net'

  [Source]: 'Core Microsoft SqlClient Data Provider'

  [State]: '2'

Any ideas as to what's going on? Credentials didn't change as all I need to do to reconnect is enter the same credentials we've always used for the user.

ATurner

Hi Azhkanizkael
increasing the timeout may help alleviate the issue whilst it is failing over from configuration > monitored servers edit credentials on the MI then under SQL instance edit properties 

ATurner

Hi Azhkanizkael 

This looks to be an error from the MI. Sometimes this error can occur when there is some corruption detected on the database. There is a blog here that seems to have a similar issue https://sqltechblog.com/2020/04/15/its-2020-do-i-really-need-to-dbcc-checkdb/ 

Azhkanizkael

following a dbcc checkdb on every database, all we get is this on every database:

CHECKDB found 0 allocation errors and 0 consistency errors in database '<dbname>'.

So it doesn't look like there's any corruption; at least at the current time. It might be a bit tricky to figure out if there somehow becomes a tiny bit of corruption at the time of disconnect.

Azhkanizkael

One suggestion I might have:
Would it be possible to build some sort of "auto-retry" mechanism for when this happens?

Azhkanizkael

Got another disconnect last night, so a little over a week this time before it un-auth'd the user:


Maybe it's something we can work around or tell it to not attempt to connect to?

Azhkanizkael

Is there a possible way to tell SQL Monitor to ignore the database replicatedmaster? It's only there for a blip of time, so I can't select it from any list.

ATurner

Hi Azhkanizkael

Have you enabled auto retrying in SQL monitor? https://documentation.red-gate.com/sm/adding-and-managing-monitored-servers/managing-monitored-servers/reconnecting-automatically-after-monitoring-stopped