Azure Synapse Analytics: Login Failed for User 'NT AUTHORITY\ANONYMOUS LOGON' - Error 18456
Question
There is a dedicated Azure SQL Pool in Azure Synapse Analytics.
You are getting “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
(Microsoft SQL Server, Error: 18456)” when accessing it.
What may be the cause of this issue?
Answers
Explanations
Click on the arrows to vote for the correct answer
A. B. C. D.Correct Answer: A.
This error occurs due to one cause only.
If the AD user is trying to connect to the master database and that user does not exist in the master, there are two ways to mitigate this error.
One, we should specify the dedicated SQL pool to which we are trying to connect at the connection time.
Second, add the user to master.
Option A is correct:It is the cause of the error.
Option B is incorrect: This error is caused when the user does not exist in the master and not any other specific database in the pool.
Option C is incorrect: There will be a connection timeout or related error if the firewall is blocking.
Option D is incorrect: It's an error that can occur when a login has been created on the SQL Database master database, but not in the specific SQL database.
To know more, please refer to the docs below:
The error message "Login failed for user NT AUTHORITY\ANONYMOUS LOGON
(Microsoft SQL Server, Error: 18456)" indicates that the user attempting to access the Azure SQL Pool is not authenticated or authorized to do so.
The possible causes of this error are:
A. User doesn't exist in master: This is unlikely to be the cause of the error, as the NT AUTHORITY\ANONYMOUS LOGON
user is a built-in account that is used when a user is not authenticated or when authentication fails.
B. User doesn't exist in a specific SQL database: This could be a possible cause if the user has not been granted access to the specific SQL database within the Azure SQL Pool. In this case, the user would need to be added as a user to the specific database and granted the appropriate permissions.
C. Firewall blocking access: This could also be a possible cause if the firewall settings on the Azure SQL Pool are configured to block access from the IP address of the machine or application attempting to connect to the database. In this case, the firewall rules would need to be updated to allow access from the appropriate IP address or range.
D. CTAIP error: CTAIP stands for "Cannot Translate Address/Name". This error occurs when the SQL Server cannot resolve the IP address of the client machine attempting to connect to the database. This is unlikely to be the cause of the error if the user is attempting to connect to the Azure SQL Pool from within the Azure environment.
In summary, the most likely cause of the error "Login failed for user NT AUTHORITY\ANONYMOUS LOGON
(Microsoft SQL Server, Error: 18456)" when accessing an Azure SQL Pool in Azure Synapse Analytics is either that the user has not been granted access to the specific SQL database within the pool or that the firewall settings are blocking access.