0

I have troubles configuring a new MySQL connection with ODBC Data Sources (64bits) utility on a Windows Server 2019. I'm using MySQL Workbench to define remote accesses as follow:

enter image description here

The user is granted a SELECT only access:

enter image description here

I have noticed that the server from which I need to initiate the connection (srv-bo) has a static IP address that had already been used in the past by another server (srv-mooc). The ancient remote server entry persisted in the domain controller DNS, so I deleted it. Since then, the ODBC Data Sources (64bits) utility keeps trying to connect with the ancient server's name (srv-mooc)... It seems that some connection informations are kept in memories somewhere but I can't figure it out.

So far, I have tried to flush the DNS cache on my Windows Server 2019 from which I initiate the connection. I have also tried to set up the connection from another user session and after server reboot. Issue is still there:

enter image description here

Error message:

enter image description here

The user credentials work fine from another Windows Server 2019, so connection details are good.

Any idea to help?

Thomas

Improve this question
9
  • It appears that the user is not allowed to log in from the host it's connecting from. This would explain why it works from another machine. How is the user defined server side? javatpoint.com/mysql-show-users
    – vidarlo
    Feb 28, 2022 at 9:31
  • Thanks. The user is allowed, actually. I have authorized the srv-abc only server from which the user can access to the DB. If I remove this filter and authorize any servers, the issue is not there anymore... It seems indeed that it is a DNS issue.
    – wiltomap
    Feb 28, 2022 at 10:23
  • The error message says that the user is connecting from srv-mooc.siveer.fr...
    – vidarlo
    Feb 28, 2022 at 13:04
  • Sorry I have edited my initial post to make it clearer.
    – wiltomap
    Feb 28, 2022 at 13:29
  • No reason to be sorry :) My questions was to make the question better - and thus make it more likely that you get a good answer :) It's easy to overlook details in what we write!
    – vidarlo
    Feb 28, 2022 at 22:22

3 Answers 3

Reset to default
1
+50

It's quite possible MySQL is maintaining an internal DNS cache of hosts. For a quick and dirty test, try SET GLOBAL host_cache_size=0; on the server (if DNS load is a concern, remember to reset it to a non-zero value after testing).

Edit: For a 5.5 MySQL try a FLUSH HOSTS;

References: https://dev.mysql.com/doc/refman/8.0/en/host-cache.html#host-cache-flushing http://download.nust.na/pub6/mysql/doc/refman/5.5/en/dns.html

Improve this answer
5
  • Good idea! ...but (sorry) the mysql.host table on the remote server is empty (I guess the host_cache_size is already set to 0). On the server from which I initiate the connection, I have no MySQL local instance.
    – wiltomap
    Mar 4, 2022 at 8:24
  • If you're checking the tables manually, you'll want to look at mysql.host_cache as opposed to mysql.host
    – Brandon Xavier
    Mar 4, 2022 at 14:48
  • I don't have such a table... The MySQL version is 5.5.62.
    – wiltomap
    Mar 4, 2022 at 14:57
  • See edits for 5.5 MySQL (which would have been handy to know beforehand:-)
    – Brandon Xavier
    Mar 4, 2022 at 15:28
  • One final comment, host_cache is actually exposed (in versions >= 5.6) thru the performance_schema pseudo DB not thru mysql (it maps in-memory data structures to SQL-like tables)
    – Brandon Xavier
    Mar 7, 2022 at 16:42
0

You can try to edit this file C:\Windows\System32\drivers\etc\hosts in which are stored all local DNS records. Also please check what is the answer of the current DNS server Windows uses, looks like it has a PTR record somewhere.

Improve this answer
16
  • hosts file is the default one I guess (all lines are commented and no srv-mooc). I find no entry at all within Domain Controller DNS. nslookup command returns the name of the right server, not the ancient one. I find no PTR record, anywhere...
    – wiltomap
    Feb 28, 2022 at 15:01
  • When you creating the rule to block other connections how you specify host name of the allowed one?
    – Zhivko Zhelev
    Feb 28, 2022 at 15:23
  • I entered srv-bo% as I need another server named srv-bo-rct to be able to connect. I tried to enter srv-bo without the wildcard, but issue persists.
    – wiltomap
    Feb 28, 2022 at 15:46
  • I'm not sure how this will work. I suggest you to just allow both hosts separately with the full name, this should do the job. Please check the answer.
    – Zhivko Zhelev
    Feb 28, 2022 at 16:02
  • No it doesn't. As I wrote in my previous comment, I tried with entering srv-bo alone and I get the same error.
    – wiltomap
    Feb 28, 2022 at 16:06
0

After loads of searches and precious help in the various comments, I eventually managed to solve the issue by executing a FLUSH HOSTS; SQL query within query editor on the remote server.

See MySQL 5.5 documentation here.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .