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

Question

Hi ,
I have Azure Sql Server with when i am trying to login via SQL login , i am getting below error
I am able to connect via AD login.
I have validated setting in Azure portal and SQL login is allowed.

I have tried resetting the password but same error .

TITLE: Connect to Server

---

Cannot connect to test.database.windows.net.

---

ADDITIONAL INFORMATION:

Login failed for user '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

---

Answer 1

Hello,

Welcome to Microsoft Q&A,

Using your Azure AD admin login, run this query:

SELECT name, type_desc, is_disabled FROM sys.sql_logins WHERE name = 'User';

If the result is empty:

• The SQL login doesn't exist.
• You must create it with:

CREATE LOGIN [User] WITH PASSWORD = 'YourStrongP@ssword';

Then, grant access to the database:

USE [your_database_name]; 
CREATE USER [User] FOR LOGIN [User];
EXEC sp_addrolemember 'db_datareader', [User];
EXEC sp_addrolemember 'db_datawriter', [User];

If the is_disabled is 1 as a result of Query1, run the below command to enable it

ALTER LOGIN [User] ENABLE;

Please Upvote and accept the answer if it helps!!