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

Question

I haven't changed anything in Azure in over a year. All of a sudden, I can't access my sql server databases. From SSMS 2022 I get error "Login failed for user 'myUser'. (Microsoft SQL Server, Error: 18456)"

From the Azure portal login, I get "The username or password is incorrect. Check your credentials and try again."

I can't access the support tickets or an Azure customer rep.

What can I do?

Answer 1

Hello vmhatup

Typically, the service administrator can use the following steps to add the login credentials:

Log in to the server by using SQL Server Management Studio (SSMS).

1. To check whether the login name is disabled, run the following SQL query in the master database:

      SELECT name, is_disabled FROM sys.sql_logins;
   

2. If the corresponding name is disabled, you might decide to enable it by using the following statement:

      ALTER LOGIN <User name> ENABLE;
   

3. If the SQL login user name doesn't exist, edit and run the following SQL query to create a new SQL login:

      CREATE LOGIN <SQL_login_name, sysname, login_name>
      WITH PASSWORD = '<password, sysname, Change_Password>';
      GO
      
      
   

4. In SSMS Object Explorer, expand Databases.
5. Select the database that you want to grant the user permission to.
6. Right-click Security, and then select New, User.

https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql&wt.mc_id=knowledgesearch_inproduct_azure-cxp-community-insider#unable-to-log-in-to-the-server-errors-18456-40531

Also, please share the details requested in the private message.

Answer 2

I cannot connect at all.

I have not changed anything in the past year for any of this to happen.

How can I open a support ticket?

Answer 3

I cannot connect at all.

I have not changed anything in the past year for any of this to happen.

How can I open a support ticket?

Answer 4

Error 18456 with the Azure portal showing “The username or password is incorrect” indicates an authentication failure for the login being used.

Take these steps, in order:

1. Verify username and password
   • Confirm that the exact login name and password being used in SSMS match what is used in the Azure portal connection dialogs.
   • If the password might have changed or been forgotten, have the server administrator reset it.
2. Check if the SQL login is disabled or missing
   • A service administrator with working access to the server must connect (for example, using another admin login) and run in the master database:

          SELECT name, is_disabled FROM sys.sql_logins;
     

   • If the login MyUser appears with is_disabled = 1, enable it:

          ALTER LOGIN MyUser ENABLE;
     

   • If the login does not exist, recreate it with a new password:

          CREATE LOGIN MyUser
          WITH PASSWORD = 'Strong_New_Password_Here';
          GO
     

3. Map the login to a database user and grant permissions
   • In SSMS Object Explorer, under Databases, select the target database.
   • Create a user mapped to the login and grant a role, for example:

          USE <YourDatabase>;
          GO
          CREATE USER [MyUser]
          FOR LOGIN [MyUser]
          WITH DEFAULT_SCHEMA = [dbo];
          GO
          
          EXEC sp_addrolemember N'db_owner', N'MyUser';
          GO
     

   • Adjust the role to something more restrictive than db_owner if appropriate.
4. If the login still fails with 18456
   • Check the SQL error log for the 18456 entry and note the state code. The state maps to a specific cause (for example, invalid user ID, wrong password, login disabled, no database, etc.). Use the state description to correct the underlying issue (for example, fix the password for state 8, create the login for state 2/5, enable the login for state 7, fix database access for states 38/46).

If no administrator account can connect at all, a subscription or server-level admin with portal access must regain access (for example, by resetting the server admin password in the Azure portal) and then perform the steps above.

---

References:

• Troubleshoot connectivity issues and other errors
• MSSQLSERVER_18456