MySQL Password Reset Guide for Azure MySQL Flexible Server

Understanding One-Time Password Expiration and Mandatory Reset

This guide explains the complete process of forcing users to reset their password on first login using MySQL's password expiration feature. It addresses the seemingly counterintuitive requirement that users must login with their expired password to change it.

🔑 Why Users Must Login with an Expired Password

The Security Model Explained

This might seem confusing: "My password is expired, but I need to use it to login?"

Yes, this is by design. Here's why MySQL works this way:

Authentication vs Authorization: The expired password still authenticates who you are (proves your identity), but it doesn't authorize you to do anything except change your password.
Restricted Session Mode: When you login with an expired password, MySQL creates a special "sandbox" session where:
- Your identity is verified ✅
- You're connected to the server ✅
- You can ONLY run ALTER USER to change your password ⛔
- All other commands are blocked with ERROR 1820 ⛔
Security Benefits:
- Ensures only the legitimate user can reset their own password
- Prevents unauthorized password resets
- Maintains audit trail of who changed the password

Technical Implementation

According to MySQL documentation, clients must support the client.pwd_expire_ok capability, which:

Allows authentication to pass even with an expired password
Enables the password change workflow
Is supported by modern MySQL clients (mysql CLI, MySQL Workbench, most drivers)

🔐 Admin: Creating Users with Expired Passwords

Step 1: Create User with Forced Password Reset

-- Create user with a temporary password that expires immediately
CREATE USER 'john.doe'@'%' 
  IDENTIFIED BY 'TempP@ssw0rd123!' 
  PASSWORD EXPIRE;

What this does:

Creates the user account
Sets an initial password
Marks the password as expired immediately
Forces password change on first login

Step 2: Grant Necessary Privileges

-- Grant required privileges (will be usable AFTER password reset)
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'john.doe'@'%';

Step 3: Verify Setup

-- Confirm password is expired
SELECT 
    user,
    host,
    password_expired,
    password_last_changed
FROM mysql.user 
WHERE user = 'john.doe';
-- Expected: password_expired = 'Y'

👤 User Experience: Step-by-Step Password Reset Process

What Users Need to Know

You will receive:

Your username (e.g., john.doe)
A temporary password (e.g., TempP@ssw0rd123!)
The server address

Important: You MUST use this temporary password to login, even though it's expired!

Step 1: Connect Using the Expired Password

mysql -h your-server.mysql.database.azure.com -u john.doe -p
Enter password: [type your temporary password here]

What happens:

✅ Connection succeeds (you're authenticated)
⚠️ You enter a restricted session
🔒 You can ONLY change your password

Step 2: Understand the Restricted Session

If you try ANY command other than password reset:

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> SELECT 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

This is normal! MySQL is protecting the system by ensuring you can only reset your password.

Step 3: Reset Your Password

Run this command IMMEDIATELY after connecting:

ALTER USER USER() IDENTIFIED BY 'YourNewSecureP@ssw0rd789!';

Password Requirements for Azure MySQL:

Minimum 8 characters
Must contain 3 of 4 character types:
- Uppercase letters (A-Z)
- Lowercase letters (a-z)
- Numbers (0-9)
- Special characters (!@#$%^&*()_+-=[]{}|')

Step 4: Verify Success

After password change, test that normal access is restored:

mysql> SELECT USER(), CURRENT_USER();
+----------------------+----------------------+
| USER()               | CURRENT_USER()       |
+----------------------+----------------------+
| john.doe@clienthost  | john.doe@%           |
+----------------------+----------------------+

mysql> SHOW DATABASES;
-- Now shows your authorized databases

🖥️ MySQL Workbench vs Command Line

MySQL Workbench Experience

⚠️ CRITICAL WARNING: Do NOT Use "Test Connection" Button!

When dealing with expired passwords in MySQL Workbench:

❌ NEVER click "Test Connection" - This will fail with expired passwords
✅ ALWAYS click "OK" to connect directly - This triggers the password reset workflow

Why This Happens: The "Test Connection" feature has limited support for the client.pwd_expire_ok capability and cannot handle the expired password sandbox mode properly. It will show an error even though your credentials are correct.

Correct Workflow:

Enter connection details (host, username, expired password)
Click OK button (NOT Test Connection)
Workbench detects expiration and shows dialog: "Your password has expired"
Enter new password in the prompt
Workbench handles the ALTER USER command automatically
Connection proceeds normally

If You Accidentally Click Test Connection:

You'll see: "Your password has expired. To log in you must change it using a client that supports expired passwords"
Solution: Ignore the error and click OK to connect anyway
The connection will work and prompt for password reset

Command Line Experience

Manual Workflow:

Connect with expired password → Success
Try any command → ERROR 1820
Must manually run ALTER USER USER() IDENTIFIED BY 'newpassword'
After password change → Full access

⚠️ Critical Client Requirements

Your Client MUST Support Expired Passwords

Why this matters: Not all MySQL clients can handle expired passwords properly.

Technical Requirement: The client must support the client.pwd_expire_ok capability to:

Allow authentication with an expired password
Enable the restricted session for password reset
Handle ERROR 1820 appropriately

✅ Verified Compatible Clients

Client	Support Status	Notes
MySQL CLI (mysql)	✅ Full Support	Version 5.7+
MySQL Workbench	✅ Full Support	Automated dialog for password reset
MySQL Connector/J	✅ Full Support	Java applications
MySQL Connector/Python	✅ Full Support	Python 3.x
MySQL Connector/NET	✅ Full Support	.NET applications
phpMyAdmin	⚠️ Partial	May require configuration
DBeaver	⚠️ Varies	Check version and settings
HeidiSQL	❌ Limited	May not handle expired passwords

If your client doesn't support expired passwords:

You'll receive: ERROR: Your password has expired. To log in you must change it using a client that supports expired passwords.
Solution: Use MySQL Workbench or command line client to reset the password first

🎯 Best Practices for Password Management

For Administrators

Secure Password Distribution
- Never send passwords via email or chat
- Use a secure password manager or encrypted channel
- Consider using separate channels for username and password
Clear Communication
- Inform users BEFORE creating their account that they'll need to reset the password
- Provide this guide or similar documentation
- Specify which client tools they should use
Temporary Password Standards
- Use complex temporary passwords even though they'll be changed
- Include expiration in your security audit logs
- Consider setting a naming convention like Temp_[Year]_[Random]!

For Users

Immediate Action Required
- Reset your password as soon as you receive credentials
- Don't try to "test" access with other commands first
- Have your new password ready before connecting
Password Selection
- Don't reuse the temporary password
- Choose a unique password not used elsewhere
- Consider using a password manager
Connection Security
- Always connect over SSL/TLS
- Verify the server certificate if possible
- Never save passwords in plain text files

📊 Monitoring and Auditing

Admin: Check Password Status

-- Comprehensive password status report
SELECT 
    user,
    host,
    password_expired,
    password_last_changed,
    DATEDIFF(NOW(), password_last_changed) as days_since_change,
    account_locked,
    CASE 
        WHEN password_expired = 'Y' THEN 'ACTION REQUIRED: Password expired'
        WHEN DATEDIFF(NOW(), password_last_changed) > 90 THEN 'WARNING: Password older than 90 days'
        ELSE 'OK'
    END as status
FROM mysql.user
WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema', 'azure_superuser')
ORDER BY password_expired DESC, password_last_changed ASC;

Track Failed Login Attempts

-- Enable audit logging for security monitoring
-- (Requires audit plugin configuration)
SELECT 
    event_time,
    user,
    host,
    command_class
FROM mysql.audit_log
WHERE command_class = 'connect' 
  AND argument LIKE '%Access denied%'
ORDER BY event_time DESC
LIMIT 10;

🚨 Troubleshooting Guide

Common Issues and Solutions

Issue	Error Message	Solution
Can't connect at all	`Access denied for user`	Verify username, password, and host permissions
Stuck in restricted mode	`ERROR 1820` on every command	Use exactly: `ALTER USER USER() IDENTIFIED BY 'newpass';`
Password change fails	`Your password does not satisfy the current policy requirements`	Ensure password meets complexity requirements
Workbench test fails	`Your password has expired. To log in you must change it using a client that supports expired passwords`	DO NOT use Test Connection button - click OK instead
Client doesn't support	`Your password has expired. To log in you must change it using a client that supports expired passwords`	Switch to MySQL CLI or Workbench (use OK button)
Account locked	`Access denied... account is locked`	Admin must run `ALTER USER ... ACCOUNT UNLOCK`
Wrong authentication plugin	`The selected authentication method ... does not support password expiration`	Check user's authentication plugin with admin

Emergency Recovery Process

If a user cannot reset their password:

-- Admin intervention required
-- Option 1: Reset password without expiration
ALTER USER 'john.doe'@'%' IDENTIFIED BY 'NewTempPassword2024!';

-- Option 2: Unlock and reset
ALTER USER 'john.doe'@'%' ACCOUNT UNLOCK;
ALTER USER 'john.doe'@'%' IDENTIFIED BY 'NewTempPassword2024!' PASSWORD EXPIRE;

-- Option 3: Check authentication plugin
SELECT user, host, plugin FROM mysql.user WHERE user = 'john.doe';
-- If not mysql_native_password or caching_sha2_password, may need to change plugin

📝 Complete Working Example

Scenario: Onboarding a New Developer

Admin Actions:

-- 1. Create the user with an expired password
CREATE USER 'dev.sarah'@'%' 
  IDENTIFIED BY 'Welcome2Azure2024!' 
  PASSWORD EXPIRE;

-- 2. Grant appropriate permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON projectdb.* TO 'dev.sarah'@'%';
GRANT EXECUTE ON projectdb.* TO 'dev.sarah'@'%';

-- 3. Verify setup
SELECT user, host, password_expired FROM mysql.user WHERE user = 'dev.sarah';

User Actions (Sarah):

# 1. Connect with temporary password
mysql -h project-mysql.mysql.database.azure.com -u dev.sarah -p
Enter password: Welcome2Azure2024!

# 2. MySQL responds with connection but restricted mode
mysql> 

# 3. Immediately change password
mysql> ALTER USER USER() IDENTIFIED BY 'MyS3cur3D3vP@ssw0rd!';
Query OK, 0 rows affected (0.01 sec)

# 4. Verify access
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| projectdb          |
+--------------------+
2 rows in set (0.00 sec)

This guide follows MySQL official documentation and security best practices. The password expiration mechanism is a built-in MySQL feature designed to enforce password resets while maintaining security through authenticated sessions.

JosiahSiegel/mysql_password_guide.md