Closed gordan-bobic closed 10 months ago
oblitorum
commented
10 months ago Did you enter the username only when adding it as remote instance? If so, have you tried enter 'username@hostname' as the username, like what I did in the below screenshot? Somehow azure requires the username in this format.
Also don't forget to edit the firewall rules to allow access from SSM host, and disable "Enforce SSL connection" because currently SSM doesn't support it.
gordan-bobic
commented
10 months ago Yes, I tried that. MySQL 5.5 client in PMMv1 refused to connect due to the odd handshake headers. MariaDB 10.4 client can connect without any special options. I suspect the golang library chokes on those weird handshake headers in the same way the old MySQL 5.5 CLI did.
oblitorum
commented
10 months ago But I just successfully added an azure mariadb 10.3 as a remote instance on flak, I tried to telnet the mariadb server, it returned the same handshake headers
Connected to ssm-dev.mariadb.database.azure.com.
Escape character is '^]'.
L
5.6.47.0������������!?������������mysql_native_passwordAlthough I was testing on the SSM .dev, but I checked the golang mysql library, it's the same as in SSM .3. Anyway let me try to add it again in SSM .3
gordan-bobic
commented
10 months ago Interesting. I'll try again. So I should put in, say, ssm@% as username instead of just ssm?
oblitorum
commented
10 months ago No, try the Server admin login name shown on the Azure console
gordan-bobic
commented
10 months ago Ah, but we don't want to use the server admin login, we want to use the monitoring user.
I can log in on the command line with just -ussm and the password without specifying @hostname. But SSM with same credentials doesn't work. I can even do it from inside the container using the mariadb cli client we ship.
gordan-bobic
commented
10 months ago Just re-tested, doesn't matter if I add the user@host as defined in the GRANT statement, I always get back this message:
*Server can't fulfill this requestCLI connection via mariadb-client "mysql" executable works.
oblitorum
commented
10 months ago Oh, I see, I'll re-test it with a monitoring user and try to fix that.
gordan-bobic
commented
10 months ago I tried with a limited user with privileges listed in our documentation, both defined with ssm@
oblitorum
commented
10 months ago OK, this is weird, try ssm@<server.name.of.azure.database.server>, for example, I named my azure mariadb server ssm-dev, and then I created a monitoring user ssm-dev with SQL CREATE USER IF NOT EXISTS 'ssm-dev'@'%' IDENTIFIED BY ..., and I filled the form like this and it worked.
And if I input the username only, it fails. So basically whatever you name the monitoring user as, you have to add @<server.name.of.azure.database.server> to make it works...
oblitorum
commented
10 months ago https://learn.microsoft.com/en-us/azure/mysql/single-server/how-to-connection-string
Don't know the details about why mysql client doesn't need the username in that format, maybe it's because it doesn't use DSN connection string, and our Golang program uses DSN connection string, the database driver needs that server name parsed from DSN to comminute with Azure.
gordan-bobic
commented
10 months ago Confirmed! That is absolutely fascinating. Also confirmed this is also how to make the old MySQL 5.5 CLI client work.
This, however, DOESN'T work when using the server's IP, it has to be the hostname, If using the IP, MySQL 5.5 CLI returns this error:
ERROR 9002 (28000): The server name you tried cannot be found: '10.11.12.13'. Please check the Username and retry connection. The Username should be in <username@hostname> format.MariaDB 10.3 client also fails with the same error if using IP address in -h 10.11.12.13 instead of the hostname.
So it looks like there is definitely some proxying similar in function to HTTP name based virtual hosts going on.
So I guess the recent MySQL/MariaDB client knows to automatically add the @hostname to the username when it see the additional version header in the handshake because it infers there's a proxy in the way that requires a hostname to route to the end point correctly.
Can you write a patch for the component we use in SSM to make it auto-add the @hostname part to the username if it detects the additional version number in the handshake that isn't there on raw connections? If this is a MySQL/MariaDB driver library, feel free to push it upstream. In the meantime, let me know what it is and I'll for the relevant repository until your patch is merged and a new release tagged upstream.
oblitorum
commented
10 months ago OK, I figured this out. The reason why recent mysql client works without adding @servername, is because it adds the _server_host Connection attributes in handshake response at connect time, and Azure mariadb/mysql recognizes that and uses that.
The Golang MySQL/MariaDB driver library we use is this one -> https://github.com/go-sql-driver/mysql , unfortunately the connection attributes feature is still in master branch, new release is not yet tagged. We can update this library to the master branch version in our component, but there is a risk to use a non-release version, maybe we should "extract" this feature as a patch only.
gordan-bobic
commented
10 months ago I forked it here: https://github.com/shatteredsilicon/go-sql-driver-mysql
Please cherry pick the patch into the most recent tagged release. You can then re-tag it with an extra .1 in the version tag or something like that.
Can you set up a watch on the upstream repository so you get a notification when the new version gets tagged? Then we can update to it and decommission our forked repository.
oblitorum
commented
10 months ago OK, working on it
It looks like we can't connect Azure managed databases as remote databases (database only, no OS level telemetry), at least not MariaDB. Azure returns a slightly funny MySQL handshake. Non-Azure MariaDB 10.3:
Azure MariaDB 10.3:
Except that, as far as I know, there was actually no protocol change in 5.6.47
This seems to be Azure weirdness and it exports redirection ports instead of direct database access: https://learn.microsoft.com/en-us/answers/questions/1166615/about-issue-with-mysql-version-in-azure
ssm-managed.log output from the container:
If this is a 3rd party library please push a fix upstream. Until it is accepted upstream, please advise what repository I should fork for our temporary hotfix branch.