Forum / Remote Desktop Manager - Support

RDM and AWS Free RDM MySQL as a source.

  • Create an Issue
  • Cancel

Aloha,
I've setup a mysql database on AWS in the free tier. I used these instructions as the way to setup the database.
https://aws.amazon.com/getting-started/tutorials/create-mysql-db/
I am able to connect to the database from RDM. The test connects. But I run into problems when I try to setup the schema.

The error from RDM is:
You do not have the SUPER privilege and binary logging is enabled (you "might" want to use the less safe log_bin_trust_function_creators variable)

The detailed error message is:

MySql.Data.MySqlClient.MySqlException (0x80004005): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at MySql.Data.MySqlClient.MySqlScript.Execute()
at Devolutions.RemoteDesktopManager.Business.DataSources.MySQLConnectionDataSource.ExecuteScript(String script, IDbConnection dbConnection)
at Devolutions.RemoteDesktopManager.Business.DatabaseUpgradeAction.Execute(DatabaseConnectionDataSource dataSource)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.Upgrade(Boolean silent, LogMessageDelegate logMessage)


I found an aws article saying I should add a cutom parameter group and change the parameter

log_bin_trust_funtion_creators

to a '1'. But that is the default setting. So i changed it to a '0' and did not do a thing.

Any suggestions? The aws free tier has only forum support and i only see responses to the likes of "Did you follow the directions?"

Clock3 mths

Hello,

To better assist you, we require more information about the issue

What version of RDM are you using?
Are you starting RDM with specific options ? (32-64 bits run as administrator or standard)
Also the error looks like the user do not have the possibility to create table in MySQL server. Do you have full access to this database?

Regards,



David Grandolfo

signaturesignature

Clock3 mths

13.6.7.0 64-bit I am using the trial version for enterprise.
About says I am "Administrator/SysAdmin/DbOwner"

I created the database instance using the instructions in a link provided in my previous post. My guess is the account I created isn't a full admin or there are option parameters I can set to i can have that capability to do that.

Clock3 mths

I see a few posts in the aws rds forum refering to removing the "definer".

"All you need to do is remove definer from the views/procedures that use them, that will cause the current user to become the definer.

Super privs are not given as a user could interrupt the automation in place with RDS. "


Is there a way for you to give me an sql script that will setup the database that is tailored to the above?

Clock3 mths

Eulogio Apelin wrote:

I see a few posts in the aws rds forum refering to removing the "definer".

"All you need to do is remove definer from the views/procedures that use them, that will cause the current user to become the definer.

Super privs are not given as a user could interrupt the automation in place with RDS. "


Is there a way for you to give me an sql script that will setup the database that is tailored to the above?

Or something like this? Another post from AWS.

"Thank you image i use the sed to replace all the stuff like 'DEFINER=xxx' with DEFINER=CURRENT_USER then it works
like this
sed -ie "s/DEFINER=\`a-z0-9A-Z*\`@\`a-z0-9A-Z*\`/DEFINER=CURRENT_USER /g" you sql file"

Clock3 mths

Odd, we don't have any DEFINER in the create script.

I will send you our SQL script via private message. Make sure you replace _DATABASE_NAME_ with your database name prior to executing the script.

Please let me know if any of the steps fail.

Best regards,

Stefane Lavergne

signaturesignature

Clock3 mths

So I used mysql bench to connect to the database on aws. Loaded the script and ran it after updating the database name in the script. So here's where the error get's generated:
-- Version 1.19

$$
CREATE TRIGGER tr_After_Insert_Connections AFTER INSERT ON Connections
FOR EACH ROW
BEGIN
CALL pr_UpdateConnectionCache();
END;

I've attached a screenshot.

devolutions2.PNG
Clock3 mths

AWS documentation has this explination about triggers.

https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/

I've added a parameter group and set log_bin_trust_function_creators=1


So i'm at a loss why the database cannot be initialized.

Clock3 mths

We are setting up an AWS MySQL for testing and will get back to you soon.

Stefane Lavergne

signaturesignature

Clock3 mths

Make sure you reboot your DB instance.

We just setup a new MySQL AWS instance, created a new RDM database and had the exact same issue you were seeing. Applied the log_bin_trust_function_creators=1 to a new parameter group. Still had the error. Rebooted the DB instance as stated in the KB (https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/) and all works now.

We will add this to our documentation.

Best regards,

Stefane Lavergne

signaturesignature

Clock3 mths

I didn't apply the parameter group to the database. Once i did that it worked. Well the database i had was half initialized. So instead of trying to figure out how to clear the database, I deleted the database. Then started from scratch and applied the new paramerter group with log_bin_trust_function_creators=1, following the KB instructions correctly this time. I then connected RDM, it initialized the schema on the first try. Now to actually finally try the enterprise features.

I have one more question with using the AWS mysql database. I do want to make sure I'm connecting with SSL or some type of encryption. AWS says it's mysql is capable.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html#MySQL.Concepts.SSLSupport

How do I configure that with RDM? This is will be a requirement for us. A secured connection will be required for implementation.

Clock3 mths

Base on this documentation, if you want to use the certificate to connect to MySQL data source. Open the Data source configuration and under the Advanced tab click More Setting.

So this window will appear. If you use a certifiate you need to set the Path for CertificateFile. Don't forget CertificatePassword if required for the certificate. The Store location and Thumprint are also an option.

Also below you have to set SSLMode to Required.

MySQL SSL


Best regards,



David Grandolfo

signaturesignature

MySQL_SSL.jpg
Clock3 mths

Is there a way for RDM to show that the connection to the mysql database is using SSL? If i just set the option SSLMODE to required. I am able to connect to the aws mysql database. I just want to confirm.

I have limited DB skills. If i could set the database to require all connections to be only SSL then I'd do that as another way to confirm SSL is used.

Clock3 mths

Hi Eulogio,

The best way to do this is to create a user with the Require SSL configuration. By doing this you prevent user to login with a non SSL connection.

If you refer to this help topic when you create a user change the TLS Option to Require SSL.

Then test to login again without SSL connection.

Best regards,



David Grandolfo

signaturesignature

Clock3 mths

I was able to create a user using that method. I did set the new user's TLS option with SSL required. I created a new source but with the same database and using the new user. I did not change the options in advanced, so those settings are empty. I was able to add the mysql database and connect and see the existing server entries in RDM. So, based on this I have to assume that the connection from RDM to the mysql database is over SSL?

Clock3 mths

I did find that mysql 5.7 has the global option require_secure_transport

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_require_secure_transport

But on aws, the parameter can't be modified. I've inquired on the forums how I can get around this.

Clock3 mths

Hi,

We have an option under File -- My Data Source information, could confirm if the connection is SSL/TLS secured or not. As showed bellow.

MySQL SSL datasource

Best regards,



David Grandolfo

signaturesignature

MySQL_SSL_datasource.jpg
Clock3 mths

Hi Stefane, are you able to see that in RDM (David's screenshot) with the mysql database you created with aws? Mines does not show that. In fact, DB owner and System DBA have a red x. And that is with the admin account I created on aws that created the instance.

Clock3 mths

Same here, the call we preform to check the security of the connection fails on AWS. I'm investigating.

Stefane Lavergne

signaturesignature

Clock3 mths

My guess is that it's because the admin account itself doesn't have enough privlidges. It's doesn't even have DBA owner or System DBA for itself. Whatever that means.

Clock3 mths

Correct for the red X's. As for the secure or not. The call we were making to the MySQL to verify the security wasn't allowed on AWS. I've gone ahead and changed to check to the following:

SHOW STATUS LIKE 'Ssl_cipher';
On a secured connection you should see something like "AES256-SHA". When the connection is not secured you will have nothing.

This change will be in part of the beta release soon.

Best regards,

Stefane Lavergne

signaturesignature

Clock3 mths

Understood. I'll keep an eye out for it in a future update. Thanks.

Clock3 mths

13.9.6 beta is now available

https://remotedesktopmanager.com/home/download#Beta

Best regards,

Stefane Lavergne

signaturesignature

Clock2 mths

I downloaded 13.9.7 from the beta link at the bottom of the downloads page today. After installing it is unable to pull the data from the database at aws. The data source configuration hasn't changed. The Test Host and Test Schema are both successful. This is the error I receive:


MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'hpuRDMmysql.Repository' doesn't exist
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.GetData(String sql, IDbDataParameter[] parameters, IDbConnection dbConnection)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseSecuritySubDataSource.c19029dddbab3c0622d234ce5edfe80e0(String c6c7922f70bc4ad6a683a9f6baa3f4b7d)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseSecuritySubDataSource.c30b7da8627df6f1681ffc9d66d4adb12(String c6c7922f70bc4ad6a683a9f6baa3f4b7d)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseSecuritySubDataSource.GetRepositories(Boolean includeDefault)
at Devolutions.RemoteDesktopManager.Managers.RepositoryManager.Refresh(Boolean force)
at Devolutions.RemoteDesktopManager.Managers.SecurityManager.LoadOfflineSecurity()
at Devolutions.RemoteDesktopManager.Managers.OfflineManager.PreloadConnections(BaseConnectionDataSource dataSource)
at Devolutions.RemoteDesktopManager.Managers.ConnectionManager.c9118c85c01b8dbb7b1b838e1f6193414(BaseConnectionDataSource c7f28727fc7a5daa94ba08f688b2a5781, Boolean c0f381ad765fb6350e74d3bf32b4f4713)
at Devolutions.RemoteDesktopManager.Managers.ConnectionManager.LoadConnections(BaseConnectionDataSource dataSource)

Clock2 mths

Could you please send us your schema File > Data Sources > Upgrade (tab) > Email Schema to Support

Thanks,

Stefane Lavergne

signaturesignature

Clock2 mths

Sent it. I will install the current version over the beta since I have a demo scheduled in an hour with someone from devolutions.

Clock2 mths

Go your schema, give me a few minutes to analyze it.


FYI - with the beta, MySQL data source is not backwards compatible, once you've upgraded the database v13.7.6 will not work.

Reference: https://forum.devolutions.net/topic30381-rdm--beta.aspx#post119935

Stefane Lavergne

signaturesignature

Clock2 mths

It looks like your database has not been upgraded. So it should work with v13.6.7.

The question is why didn't v13.9.x prompt for the database upgrade? Investigating...

Stefane Lavergne

signaturesignature

Clock2 mths

I just talked to my colleague that is on the demo. He installed the current version of 13.6.7 and is able to connect to the existing aws database I set up for rdm. Maybe my install of 13.9.7 on my machine didn't make any changes to the database?

Clock2 mths