Cloud SQL MySQL user with IAM Auth is required to give password

[Nivi]

I created a user in Cloud SQL MySQL with iam auth. The user also exists in GCP and has Cloud SQL Client and Cloud SQL Instance User permissions.

When I try to login with this user, I am prompted for a password. I know that in such cases I just need to hit enter and then I am asked if I want to save the password. Hitting enter one more time lets me in to the DB.

However, with this particular user, hitting enter gives me the following error:

➜  ~ mysqlsh --host=127.0.0.1 --user=USER
Please provide the password for 'USER@127.0.0.1': 
MySQL Shell 9.0.1

Copyright (c) 2016, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'USER@127.0.0.1'
MySQL Error 1045 (28000): Access denied for user 'USER'@'cloudsqlproxy~xx.xxx.xx.xxx' (using password: YES)

I verified the following several times:

1. User exists in the DB and its auth method is IAM.
2. User exists in GCP with Cloud SQL Client and Cloud SQL Instance User permissions.
3. User is granted permissions to view DB contents.
4. Connectivity and networking are properly configured
5. Other IAM users can log in without any issues. 

Any ideas where to look or what might be causing this?

[KikoZam]

Hi @Nivi,

Welcome to Google Cloud Community! 

Based on this MySQL documentation, the error message you’re encountering might be related to a server issue due to a combination of factors, such as how user privileges are configured in the database, insufficient permissions for your username, or issues related to connections to MySQL databases. You also mentioned that you have already verified various possible causes for the error.

Here are some potential workarounds that might help resolve the issue:

• Grant database privileges to an individual IAM User: Check and verify the user's privileges in the database because, by default, when a new IAM user or service is added to a Cloud SQL instance, it is granted no privileges on any databases. Please refer to this documentation for more details.
• Use Cloud SQL Auth Proxy: For IAM database authentication, you can use the Cloud SQL Auth proxy. For long-lived processes or applications that use connection pooling, users can authenticate the proxy with their own credentials rather than a service account, which may provide a more stable connection to the MySQL instance.
• Connect using SSL/TLS: Confirm whether you have the private key for the certificate. If you’ve misplaced the key, you’ll need to generate a new certificate. If you do have the private key, you can refer to this documentation for instructions on how to connect to a Cloud SQL instance using SSL/TLS.
• Adjust SSL/TLS Settings (only if applicable): If your infrastructure cannot accommodate the SSL/TLS requirement, you can disable the “Allow only SSL connections” option for your instance. Go to the GCP Console > SQL > [select your instance] > Connections > Security, and uncheck the “Allow only SSL connections” option. After making this change, remove the existing certificate and try connecting again.
• You might also find this Stack Overflow links helpful, as it discusses a similar issue. Please note that these are old posts but might still work for you:
• ERROR 1045 (28000) access denied cloudSQL
• MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

If the workarounds above don't work, you can contact Google Cloud Support for a more in-depth analysis. When contacting them, please provide comprehensive details and include screenshots. This will help them better understand and address your issue.

I hope the above information is helpful.