question

tony-1469 avatar image
0 Votes"
tony-1469 asked AlbertoMorillo commented

need help tracking down azure sql error Message: Login failed for user 'manager'.

We recently changed our azure sql server manager password.
We confirmed (multiple times) that all connection string configurations have the correct password.
I've exhausted my list of theories of what the root issue is, and don't even know where to look next.

This problem is intermittent and doesn't appear to be an issue with our app service UI, and appears to happen "mostly" in our .net our azure functions and in our IHostedService implementations.

It "feels" like a connection string my be stuck in a cache that survives between deployments.

If anyone could give me some things to try or look into it would be greatly appreciated.

azure-sql-databaseazure-functions
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I forgot to provide the stack trace we are seeing
126461-login-failed-for-user-manager.txt


0 Votes 0 ·
AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered

Please enable Auditing on your Azure SQL Database to further investigate. After that you can click "View Audit logs" and search for Event type "Login" and action not successful.

126416-auditing1.png

Once you filter login events, make a click on any event, scroll the window that shows the detail of the event you just clicked on, and you will see important information like IP address of the host.

If you use the dashboard available on the "View Audit Logs" you can have details by type, by IP address and by principal. Just click on the type you would like to see details, and you will get all related events, each one will all details.

126429-auditing2.png

Make a click on the IP addresses that you don't recognize (left side on above image), make a click on the failed login attempts for each principal (right side on above image, where you see the Pie chart).





auditing1.png (107.1 KiB)
auditing2.png (20.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

tony-1469 avatar image
0 Votes"
tony-1469 answered AlbertoMorillo commented

Thanks for the auditing tip. I have enabled it and will take a look.

I'm not sure how identifying the IP of the app service or function app will help me solve this issue, I can see in my logs what specific functions fail with this error. If you can please elaborate on how the IP address will help solve this issue, that would be great.

Note: This issue is intermittent and cannot be duplicated on local dev environment at all, and only intermittently on azure. Another weird to me fact is when I put special logging in to detect a connection string having wrong password, nothing gets logged.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Tony is not only the IP address. Audit provides you other columns of information.

About the IP address information, that can give you an idea from what datacenter is coming the request and what Azure service is using that IP (see the service tag on this list).



0 Votes 0 ·
tony-1469 avatar image
0 Votes"
tony-1469 answered tony-1469 commented

Like I said, I know where these calls are coming from.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What about the other pieces of information Auditing is providing to you?

0 Votes 0 ·

So I turned auditing on and there are some entries if I go into log analytics, but I don't see any having an event_type field. My UI doesn't look anything like what you posted so I'm guessing I didn't do something correctly.



Just to give an example of how inconsistent and intermittent this issue is, I logged this issue happening 26 times yesterday, and 8000 times today.

127263-image.png


127216-image.png


0 Votes 0 ·
image.png (81.3 KiB)
image.png (48.7 KiB)
tony-1469 avatar image
0 Votes"
tony-1469 answered

I'll have to wait until I see the issue happen in the logs then I will use your suggestion to see if there are any clues contained in that audit data.
I feel like it is an issue in the azure function apps/service apps, maybe with connection pooling??? maybe there is a connection stuck in a pool somewhere? :-) I'm not sure if that is a sql server or azure app responsibility. Is there a way to like clear a connection pool?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo commented

Tony,

Let's forget about the portal. Save the Audit to BLOB storage account and read it like this:

  SELECT event_time, action_id, class_type, additional_information,database_name, statement,*
        
  FROM sys.fn_get_audit_file('https://myblobstorage.blob.core.windows.net/sqldbauditlogs/my_svr/MyAuditDBTest/', default, default) 


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

where do you run that query if not through the portal?

0 Votes 0 ·
tony-1469 avatar image
0 Votes"
tony-1469 answered

so I was finally able to run this query.
A majority of the additional information contents were <login_information><error_code>18456</error_code><error_state>113</error_state></login_information>
And the IP address on those rows is for our one of our azure function apps which we already knew was one of the sources of the problem.
Does this error code provide a clue that I am failing to see?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo commented

Due to this high number of login failures from the same client IP, the DoS guard kicks in and block all connections from that IP to the database for a few minutes (error 18456, state 113) and thus you start getting the stated error. For more information about DoS Guard, please check this documentation.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I understand what the error is, but that doesn't help me solve the root cause.

I'm guessing since no one else has chimed in, it means there are no other ideas of what to try next.

Right now the only thing I can think to try is to delete and recreate the function and service apps, but it feels extreme.

0 Votes 0 ·

Tony try to create the question again.

0 Votes 0 ·