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

tony 1 Reputation point
2021-08-25T19:08:55.953+00:00

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 Database
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,267 questions

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2021-08-25T21:39:36.063+00:00

    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).

    0 comments
  2. tony 1 Reputation point
    2021-08-25T22:18:04.85+00:00

    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.

  3. tony 1 Reputation point
    2021-08-25T23:53:01.773+00:00

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

  4. tony 1 Reputation point
    2021-08-26T03:32:53.507+00:00

    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?

    0 comments
  5. Alberto Morillo 32,886 Reputation points MVP
    2021-08-27T21:31:53.853+00:00

    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)