locked
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' RRS feed

  • Question

  • Hi,

    I am getting intermittent error message Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' while connecting azure sql db from web app. I am using SQL AAD authentication. I have given all necessary permission to user.

    Please help me here.


    Thursday, May 24, 2018 9:11 AM

All replies

  • Hello,

    This error occurs when an AAD user tries to connect to the master database, but does not have a user in master. To correct this issue, either specify the SQL Data Warehouse you wish to connect to at connection time or add the user to the master database

    Please check these links for similar issues -

    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-troubleshoot

    https://social.msdn.microsoft.com/Forums/azure/en-US/33e073f1-29d5-4bc7-83d7-7c2075ed817b/trouble-to-access-azure-sql-database-with-microsoft-sql-server-management-studio?forum=ssdsgetstarted

    Regards

    Kapil


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Thursday, May 24, 2018 9:18 AM
  • Hi Kapil,

    I have created user in Master db as well. But issue is occurring after continuously hitting db for 1-2 Hrs.

    Thanks,

    Ashutosh

    Thursday, May 24, 2018 9:37 AM
  • Hi Ashutoshks.

    Have you added a contained AAD user for the Azure SQL Server? The below documentation explains (overview) and then walks you through (configuration) of Azure SQL + Azure Active Directory integration. 

    Overview

    Use Azure Active Directory Authentication for authentication with SQL Database, Managed Instance, or SQL Data Warehouse

    Configuration

    Configure and manage Azure Active Directory authentication with SQL Database, Managed Instance, or SQL Data Warehouse

    Through the Azure Portal, the Active Directory Admin that is configured will appear in the Master db, and has all the necessary access to browse databases hosted by that server instance.

    Monday, June 4, 2018 6:13 PM
  • I'm having the same problem, and I can see by logging on using the SQL Server authenticated normal server admin that my Azure AD user was indeed created successfully by Azure portal in the master database as a user, and I can see that it lists that same user as the "Active Directory Admin" in the Azure portal for that server, so Azure portal thinks everything is okay -- just that the login always fails (after successful password and MFA code is verified) with the  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error in SSMS 2017.
    Wednesday, July 25, 2018 4:42 PM
  • I'm having the same issue - it just started failing recently. I put up a question on ServerFault about it here:

    https://serverfault.com/questions/923752/importing-bacpac-via-ssms-to-azure-sql-server-fails-with-azuread-user

    Here is the full text:

    Recently, trying to import a BACPAC to an Azure SQL Server using SSMS (SQL Server Management Studio) has been failing with the following error:

    enter image description here

    Failed to connect to server ___.database.windows.net. (Microsoft.SqlServer.Smo) Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)


    The steps I have used to import the BACPAC are as follows:

    1. Connect to server in SSMS using my AzureAD credentials.
    2. Right click on Databases and choose "Import Data-tier application"
    3. Select my BACPAC file and click next.
    4. Leave default settings for S2 Azure DB and click next.
    5. Error appears before it gets to the Summary page.

    The above steps have worked great for the last 2 years, and just recently stopped working. I've tried on 2 different computers, different networks, and different versions of SSMS (that have worked find in the past) but they all still fail.

    Using a regular SQL server password login (non AzureAD account) imports the BACPAC successfully, as does importing via Azure Portal.

    Any idea what could be causing this issue?

    Friday, July 27, 2018 7:35 PM
  • Hello,

    If you could please create a new forum thread, you may find you get more help. This is an old thread.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, July 27, 2018 7:54 PM
  • Have you checked whether you have signed into Visual Studio? If you are not, then you could get this error. There should be an account configured for Azure Service Authentication. Once you sign into an account in Visual Studio this will be automatically taken care.
    Friday, March 22, 2019 11:53 AM
  • I was able to solve the connection issue on my Azure SQL service by adding an Azure AD Group as a user on my master database. This gives the AAD group CONNECT privilege on the server.

    To do this you need to login to your Azure SQL service with an Azure AD account that has full privileges.  This will be the same AAD user that created the service, or a user with the role of Admin on the AAD Directory.

    Once logged in, run this on Master "CREATE USER [MY_AAD_USER_OR_GROUP_NAME]  FROM EXTERNAL PROVIDER;"

    You will then be able to login with the AAD account or an AAD account belonging to the group.  Now you will need to adjust AAD groups and SQL users to grant the specific access you need for each database.  I didn't see a way to grant access at the server level.

    Cheers,

    John


    Wednesday, January 8, 2020 10:39 PM