Azurestack ASDK: Unable to create Mysql Hosting Servers

Question

Hello,

On one of the Azurestack ADFS deployments, MySQL PaaS is prepared with 'DeployMySqlProvider.ps1' script.
However, after successful execution of the script, adding of MySQL Hosting Servers always fails with the below error

ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

MySQL server is running outside of Azurestack host. Every time I run 'Create' using the Add MySQL Hosting Servers, new user 'MySqlRPLogin' is created in my database. Though this confirms that connectivity is good between stack host and DB server, adding host servers is failing.

I also confirmed remote login to mysql is working by login to MYSQL host from other servers.
Here is the mysql user output on the database.

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysqladmin    | %         |
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

Please suggest if I am missing something here ?

Thanks,
Avinash.

Answer 1

Hi Avinash - we'll take a look at this. A few questions for you:

What version of MySQL are you using? And on what OS?

Are you using the Password Validation plugin? Or any other auth plugins?

Have you tried using a different password for root before adding the hosting server? There might be some odd issue with the password we generate using disallowed characters. Although I would expect a different error.

Answer 2

Hello Jeff,

Thank you for the response. I tested with add two different MYSQLs as hosting servers. One deployed on ubuntu and other on windows server. Both the servers are deployed with MYSQL 5.7.
It is failing with the same 'Access denied' error while adding these MYSQLs as hosting servers.

There are no additional auth/validation plugins created. Basic plugins that are deployed with mysql server/client are used here.

Different passwords are not tried for root user before addition. I used the same working mysql password for 'root' while adding hosting servers as well. Also, the mysql password contains only plain text (no wild characters).

Thanks,
Avinash

Answer 3

One more question. Did you attempt to use this MySQL server multiple times? If so, please try deleting the mysqladmin account before you try to add it again.

Answer 4

Thank you Jeff. Yes, I tried multiple times. Every time I tried, new user entry 'MySqlRPLogin' is created in the database.
I delete this user before recreating again. I deleted 'mysqladmin' user as well and tried adding host server. But the issue remains the same.

MYSQL User info before trying 'create hosting servers':

mysql> select user,Host from mysql.user;
+---------------+-----------+
| user          | Host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

MYSQL User info after failure:

mysql> select user,Host from mysql.user;
+---------------+-----------+
| user          | Host      |
+---------------+-----------+
| MySqlRPLogin  | %         |
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

Failure Event:

Operation Name        Microsoft.MySqlAdapter.Admin/hostingServers/write   
Status                Failed   
Event Timestamp       Mon Aug 14 2017 01:58:40 GMT+0530 (India Standard Time)   
UTC Timestamp         Sun, 13 Aug 2017 20:28:40 GMT   
Caller                [e-mail]   
Authorization         action:Microsoft.MySqlAdapter.Admin/hostingServers/write
role:		      scope:/subscriptions/86E2AFC2-DED9-4553-BA95-B3B7632FCA81/resourcegroups/MYSQL-HOST-RG/providers/Microsoft.MySqlAdapter.Admin/hostingServers/15.218.153.140
Resource Uri          /subscriptions/86E2AFC2-DED9-4553-BA95-B3B7632FCA81/resourcegroups/MYSQL-HOST-RG/providers/Microsoft.MySqlAdapter.Admin/hostingServers/15.218.153.140   
Subscription ID       86E2AFC2-DED9-4553-BA95-B3B7632FCA81   
Event Submission Timestamp      Mon Aug 14 2017 01:59:04 GMT+0530 (India Standard Time)   
Operation ID          3e623b80-31f8-4042-ace5-a8bb6121f570   
SubStatus             Bad Request (HTTP Status Code: 400)   
Correlation ID        2f5f8a49-52e3-4093-90e9-50c527f2b386   
		               Http Request
		               clientRequestId:ba97ac8e-2bf3-423d-a617-b227c16e1d1a
		             clientIpAddress:192.168.200.65
				method:PUT
Level                Error
Resource Group       MYSQL-HOST-RG   
Resource Provider    Microsoft.MySqlAdapter.Admin   
Category             Administrative   
Properties           statusCode:BadRequest
		            serviceRequestId:bbf7cc08-51bc-48bb-a33c-6835a0bc6e66
		            statusMessage:{"error":{"code":"1045","message":"Access denied for user 'root'@'%' (using password: YES)"}}

Thanks,
Avinash

Answer 5

Can you run these two commands on the MySQL command line:

show grants for 'root'@'localhost';

show grants for 'root'@'%';

Answer 6

Thanks Jeff. Here is the Mysql Output:

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> show grants for 'root'@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.01 sec)

mysql>

Thanks,

Avinash.

Answer 7

The resource provider will use root@'%' to create user and grant that user permission for future use.

root@'%' did not have WITH GRANT OPTION, as a result, root@'%' could create user but can not grant permission to that user

Please login as 'root'@'localhost' and execute the following command.

GRANT USAGE ON *.* TO 'root'@'%' WITH GRANT OPTION;

Then remove the user 'MySqlRPLogin'@'%' and add the hosting server again.

Answer 8

Hello,

Thank you very much for sharing the update mysql command. After applying the 'grant' option on the MYSQL host, I am able to create MYSQL Hosting Servers.
And mysql hosting servers are now visible in the portal. However, I am stuck again in creating MYSQL databases. While creation, it ends up with the below error.

statusCode:Conflict
statusMessage:{"error":{"code":"HostingServerNotFound","message":"The 'Microsoft.MySQLAdapter.Admin/hostingservers' with id '/subscriptions/86E2AFC2-DED9-4553-BA95-B3B7632FCA81/resourceGroups/MYSQL-APP-DB/providers/Microsoft.MySQLAdapter/databases/DbFromAzureStack' cannot complete the 'Creating' operation. If the problem persists, please contact support. Cause: The resource with identifier 'Microsoft.MySQLAdapter.Admin/hostingservers' is not found."}}

As mentioned in the docs that sku takes around 1 hour, I tried creation of DB after 12 hours. But creation fails with same HostingServerNotFound error.
FYI, I added two hosting servers are they are visible in the MySQLAdapter RP. Am I missing something ?

Also, I want to know how much storage space (minimum) database that would need for this PaaS functionality.
Is their any limitation with respect to the disk size of the hosting server.

Thanks,
Avinash.

Answer 9

Hello Avinash,

I'd like to setup a Skype call with you and Jeff Goldner to discuss this in further detail.

If you could, please email [e-mail] and make sure to include the thread URL in the subject.

 

We look forward to speaking with you.

We apologize for any inconvenience and appreciate your time and interest in Azure Stack.

If you continue experience any issues with ASDK release, feel free to contact us

  Thanks,

---

Gary Gallanes

Answer 10

Thank you very much.
You can setup a skype call. That would be the fastest/easiest way to fix the issues.

Thanks,
Avinash

Answer 11

Issue fixed after removing hosting servers, uninstalling MYSQL resource provider, and redeploying the MYSQL RP again