User can not log onto Azure Sql Server

Tom Burke 0

Users can not log on to SQL Server.
Only the Admin account can access the server.
I have created the users and given them roles.

The users show up in the database correctly but the logons do not work.

Mike Bowen 1,871 Reputation points Microsoft Employee

2025-02-20T02:29:35.56+00:00

Hi @Tom Burke ,

As your question does not pertain to the Microsoft Open Specifications I have removed the [SQL Server Open Specifications] tag and added the [Azure SQL Database] tag. Hopefully, this will bring your question to the attention of the right audience.

Best regards,

Michael Bowen Senior Escalation Engineer - Microsoft Open Specifications
Vijayalaxmi Kattimani 1,485 Reputation points Microsoft Vendor

2025-02-20T03:17:00.53+00:00
Hi Tom Burke,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand that, you are unable to log in to SQL Server, this may be due to issues with permissions for users to log in, roles, or enabling SQL Authentication (SA) for connection.

Here are a few steps you can take to troubleshoot and resolve the problem:

Check SQL Server Authentication Mode: Ensure that SQL Server is configured to allow both SQL Server and Windows Authentication modes. You can do this by opening SQL Server Management Studio (SSMS), right-clicking on the server, selecting Properties, and then navigating to the Security page.

Verify User Permissions: Make sure that the users have the necessary permissions to log on to the SQL Server. You can check this by reviewing the roles and permissions assigned to the users in the database.

Enable TCP/IP Protocol: Ensure that the TCP/IP protocol is enabled for SQL Server. You can do this by opening SQL Server Configuration Manager, navigating to SQL Server Network Configuration, and enabling TCP/IP.

Check Firewall Settings: Make sure that SQL Server Port 1433 is open for inbound and outbound connections in Windows Firewall and in the Network Security Group (NSG) if applicable.

Review SQL Server Error Logs: Check the SQL Server error logs for any specific error messages related to the logon failures. This can provide more insight into the root cause of the issue.

Reset User Passwords: Sometimes, resetting the passwords for the users can resolve logon issues. Ensure that the passwords are correctly configured and meet the security requirements.

Check for Deny Permissions: Ensure that there are no deny permissions set for the users that might be preventing them from logging in.

Please refer to the below mentioned link for more information.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/consistent-authentication-connectivity-issues

If you continue to experience issues, please provide more details about any specific error messages you are seeing, and I can assist you further.

I hope this information helps. Please do let us know if you have any further queries
Nandan Hegde 34,341 Reputation points MVP

2025-02-20T14:29:54.3633333+00:00

Can you please provide some additional details as to :

Is the user unable to access the database via SSMS or from from a specific system etc?
Tom Burke 0 Reputation points

2025-02-20T17:02:32.7733333+00:00

Yes this is using Azure SQL server.
No, none of the users are able to access the server via SSMS(20.2) As Admin, I can access SQL server via SSMS and apps. There is lots of data within.

I miss the old Microsoft support even with a slow response time.
Nandan Hegde 34,341 Reputation points MVP

2025-02-20T17:06:42.75+00:00

What error are they getting when trying to access via ssms? Are they providing the database name in additional properties while trying to connect to the Az sql database via ssms?
Tom Burke 0 Reputation points

2025-02-20T17:11:02.0833333+00:00

Here is the error message when using ssms with SQL Server Authentication:
TITLE: Connect to Server

Cannot connect to jkimlawtest.database.windows.net.

ADDITIONAL INFORMATION:

Login failed for user 'Tom.Burke'. (Microsoft SQL Server, Error: 18456)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

BUTTONS:

OK
Tom Burke 0 Reputation points

2025-02-20T17:12:01.57+00:00

Here is the error message when trying to use Entra ID:
Request Id: 233155e0-3bde-4721-bdb1-13c1a62f6000

Correlation Id: 34830ef8-180e-436b-a059-58ed25f06d4f

Timestamp: 2025-02-20T16:56:54Z

Message: AADSTS900021: Requested tenant identifier '00000000-0000-0000-0000-000000000000' is not valid. Tenant identifiers may not be an empty GUID.
Tom Burke 0 Reputation points

2025-02-20T17:25:27.2266667+00:00
Verify User Permissions: Their roles have been set.

Enable TCP/IP Protocol: TCP.IP is enabled.

Check Firewall Settings: Firewall is good.

Reset User Passwords: Reset passwords, both Entra and Sql server.

Check SQL Server Authentication Mode:
Right clicking on the database shows this list:
Connect
Disconnect
New Query
Azure Data Studio
Reports
Refresh

I don't see a Properties any where
Of course I have Security underneath the Server with the User Logins, but nothing about authentication modes. When joining the server, I have both SQL Server and Windows Authentication options.

Thanks for the responses!
Erland Sommarskog 117.8K Reputation points MVP

2025-02-20T22:50:14.4533333+00:00
First, since Azure SQL come in many flavours, can you verify that you are talking about Azure SQL Database? Or is it Azure SQL Managed Instance, or SQL Server in an Azure VM? It sounds like Azure SQL Database, but I want to be sure.

From where are you connecting? On-prem? Or from an Azure VM? If you are connecting from on-prem, do you have an Azure VM you can test from?

Next, what does this query return for the users in question:

SELECT *FROM sys.database_permissions WHERE grantee_principal_id = user_id('usernamehere')
Tom Burke 0 Reputation points

2025-02-20T22:55:56.6033333+00:00

Yes, we are using Azure SQL Database. I use SSMS to access it, but their are other ways.

Running the query yields:
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc

0 DATABASE 0 0 7 1 CO CONNECT G GRANT
Erland Sommarskog 117.8K Reputation points MVP

2025-02-20T22:58:06.9266667+00:00

OK, that is what it should return, so that looks normal. It was just one of these things I wanted to check.
Tom Burke 0 Reputation points

2025-02-21T15:59:40.8166667+00:00

Yes, the users are created.
This is the resulting error message:
TITLE: Connect to Server

Cannot connect to jkimlawtest.database.windows.net.

ADDITIONAL INFORMATION:

Login failed for user 'Brendan'. (Microsoft SQL Server, Error: 18456)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

BUTTONS:

OK
Vijayalaxmi Kattimani 1,485 Reputation points Microsoft Vendor

2025-02-21T16:03:54.65+00:00

Hi @Tom Burke,

Could you please attempt to log in using the provided login name and password?
Tom Burke 0 Reputation points

2025-02-21T16:16:00.3333333+00:00

yes, same error:
TITLE: Connect to Server

Cannot connect to jkimlawtest.database.windows.net.

ADDITIONAL INFORMATION:

Login failed for user 'ReadOnlyLogin'. (Microsoft SQL Server, Error: 18456)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

BUTTONS:

OK
Vijayalaxmi Kattimani 1,485 Reputation points Microsoft Vendor

2025-02-21T18:06:49.4+00:00

Hi @Tom Burke,

Could you please refer the links of some parallel threads that might help you.

https://learn.microsoft.com/en-us/answers/questions/1281113/login-failed-for-user-(token-identified-principal)

https://learn.microsoft.com/en-us/answers/questions/133709/login-failed-for-user
Tom Burke 0 Reputation points

2025-02-21T19:51:13.16+00:00

Unfortunately a lot of MS documentation is old.
Properties is at the Database level, not the server.
By Security, does it mean permissions?
Still don't see options for Authentication modes.
Erland Sommarskog 117.8K Reputation points MVP

2025-02-21T21:58:41.6433333+00:00
As for being enabled for SQL authentication, this is something you control in the portal on the logical server:

If the checkbox "Support only Microsoft Entra authentication for this server", SQL authentication is not permitted. However, I don't this is the issue in your case, because when I test I get a different error message than from what you do.

I don't know what is going on, but I'd like to rule out that you are not making the mistake to connect to the wrong database. So do this in your database:

SELECT @@servername, db_name() CREATE USER nissenils WITH PASSWORD = 'Friday21!'

Verify that the first query returns the server and database you expect.

And then open a command-line window and run

SQLCMD -S jkimlawtest.database.windows.net -d YourDB -U nissenils -P Friday21!

Also, can you runt this query:

SELECT * FROM sys.database_permissions WHERE state_desc = 'DENY'

Does it return any rows? (Preferably, it should not.)
Tom Burke 0 Reputation points

2025-02-22T00:49:06.83+00:00

Use only Entra box is not checked.
Erland Sommarskog 117.8K Reputation points MVP

2025-02-22T11:14:42.3666667+00:00

Here is one another thing to try. Go to the database in the Portal and select Diagnose and solve problems on the left. You will have to through a number of steps. It may make things simpler to make a new failed login attempt, and then specify the time when problem started as just before that.

I just tried this myself, and eventually I got a few entries which matched the login error I had had - and where the explanation agreed with what I knew about the situation.

Use only Entra box is not checked.

Good. That was I assumed from the error message. But this means that we can take this out of the equation.

2 answers

Vijayalaxmi Kattimani 1,485 Reputation points Microsoft Vendor

2025-02-21T08:39:16.1433333+00:00

Hi @Tom Burke,

Login failed for user 'Tom.Burke'. (Microsoft SQL Server, Error: 18456)

Considering the error message you have shared, we would like to inform you that, the login (whether using SQL Authentication) does not exist. it likely means that the login hasn't explicitly been given access to SQL Server – which may mean it is not a member of an appropriate domain group. It could also mean that you have created a server-level login, mapped a database user with a different name to that login, and are trying to connect using the username, not the login name.

I am able to reproduce the issue and obtain a result.

Please follow the steps mentioned below to log in with a new user.

Step1: Ensure that a server-level login has been created and that you are attempting to log in using the appropriate username.

step2: Create a login with a password explicitly using the below code.

CREATE LOGIN <loginname> WITH password=<password>;

Ex: CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';

Step 3: Please attempt to log in using the provided login name and password.

Please refer the below mentioned link for more information.

https://sqlblog.org/2020/07/28/troubleshooting-error-18456

I hope, This response will address your query and helped you to overcome on your challenges. If this answers your query, do click Accept Answer and Yes for was this answer helpful.

If the issue still persists, please let us know here. We will respond with more details and try to help you.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
PratikLad 80 Reputation points Microsoft Vendor

2025-02-25T10:18:36.3366667+00:00
Hi @Tom Burke,

Login failed for user 'Tom.Burke'. (Microsoft SQL Server, Error: 18456)

As per the error message below are the reasons can cause the issue:

Invalid user ID

Wrong password

Login disabled

Login valid, but server access denied

Login valid, but the default database is unavailable

Login requires a password change

To resolve this, ensure that a server-level login has been created and that you are attempting to log in using the appropriate user which is associated with that login.

Ensure your login is enabled. If it is disable it will get affected for all the users that are associated with it.

ALTER LOGIN loginname DISABLE/ENABLE

When you are connecting to Azure SQL via SSMS add the database name you want to connect in connection properties as below:

And make sure the user you are using should belong to that database only.

Message: AADSTS900021: Requested tenant identifier '00000000-0000-0000-0000-000000000000' is not valid. Tenant identifiers may not be an empty GUID.

The above message you get when you have not added Microsoft Entra ID to the Azure SQL server as below but you are trying to connect the Azure SQL server with Azure Entra Id.

You can refer below documents:

https://sqlstudies.com/2020/05/20/connecting-to-azure-sql-database-requested-tenant-identifier-00000000-0000-0000-0000-000000000000-is-not-valid/

https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

I hope, This response will address your query and helped you to overcome on your challenges. If this answers your query, do click Accept Answer and Yes for was this answer helpful.

If the issue still persists, please let us know here. We will respond with more details and try to help you.
Please sign in to rate this answer.
PratikLad 80 Reputation points Microsoft Vendor

2025-02-26T04:45:56.0666667+00:00

Hi @Tom Burke,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.

In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Tom Burke 0 Reputation points

2025-02-26T16:13:50.3433333+00:00

Connecting to a named database leaves the following error:
TITLE: Browse Server for Database

Login failed for user '****'. (Microsoft SQL Server, Error: 18456)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

BUTTONS:

OK

Erland Sommarskog 117.8K Reputation points MVP

2025-02-26T21:58:23.5866667+00:00

Tom, I gave you a number of things to try for troubleshooting. Did you try any of those? Particularly, did you try the diagnose option in the portal?

Tom Burke 0 Reputation points

2025-02-26T23:48:39.8366667+00:00

I am in the process of the other tips for troubleshooting. I have a number of tasks at work.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

User can not log onto Azure Sql Server

2 answers

Your answer