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

wiltomap
  • 73
  • 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? https://www.javatpoint.com/mysql-show-users – vidarlo Feb 28 '22 at 09: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 '22 at 10:23
  • The error message says that the user is connecting from `srv-mooc.siveer.fr`... – vidarlo Feb 28 '22 at 13:04
  • Sorry I have edited my initial post to make it clearer. – wiltomap Feb 28 '22 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 '22 at 22:22
  • can you do a reverse DNS lookup? Don't know if necessary, but may be. Write the srv-bo% in small letters... just in case ;) – Manu Mar 01 '22 at 16:56
  • Reverse DNS lookup is fine and pointing on the right server name. `srv-bo%` in lower case does not change anything... – wiltomap Mar 02 '22 at 11:40
  • Is the MySQL server bound to a specific IP range? – Mike Mar 02 '22 at 20:20
  • Yes it does, but it is a 254 addresses classic range, in which both servers (ancient and actual ones) are included. – wiltomap Mar 03 '22 at 07:09

3 Answers3

1

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

Brandon Xavier
  • 1,942
  • 13
  • 15
  • 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 04 '22 at 08: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 04 '22 at 14:48
  • I don't have such a table... The MySQL version is 5.5.62. – wiltomap Mar 04 '22 at 14:57
  • See edits for 5.5 MySQL (which would have been handy to know beforehand:-) – Brandon Xavier Mar 04 '22 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 07 '22 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.

Zhivko Zhelev
  • 71
  • 4
  • `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 '22 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 '22 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 '22 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 '22 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 '22 at 16:06
  • ```GRANT ALL PRIVILEGES ON yourDB.* TO srv-mooc@'srv-mooc.siveer.fr' IDENTIFIED BY 'password';``` like this right? – Zhivko Zhelev Feb 28 '22 at 16:08
  • I'm using *MySQL Workbench* to define remote accesses. So this is defined within this application interface, inside filtering field. – wiltomap Feb 28 '22 at 16:10
  • Just updated initial post with screenshots for *MySQL Workbench*. – wiltomap Feb 28 '22 at 16:19
  • yep better, please enter ```hostname``` in command prompt on the windows server – Zhivko Zhelev Feb 28 '22 at 16:23
  • and is it possible to just create two different users for the db and specify the full hostnames? – Zhivko Zhelev Feb 28 '22 at 16:40
  • `hostname` command returns `SRV-BO` as expected. Creating a new user filtered from `SRV-BO` only gives the same error. I have also tried to authorize any server by entering wildcard `%` only, without anymore success... – wiltomap Mar 01 '22 at 07:44
  • try giving more permissions to this user ```srv-bo``` – Zhivko Zhelev Mar 01 '22 at 12:35
  • Same result, unfortunately... – wiltomap Mar 01 '22 at 12:52
  • I think something is messed up with DNS settings, you said when you deleted this old record after that problem appear, so please check them again – Zhivko Zhelev Mar 01 '22 at 14:48
  • The issue always occured, either before and after deleting the unwanted DNS entry. I think that I shouldn't have tried to initiate the connection before deleting DNS entry. As I wrote in my post, I think some kind of cache is stored somewhere but not in usual locations or files... – wiltomap Mar 01 '22 at 15:32
  • This is weirs because all dns queries have a ttl(time to live) which can be maximum 3600 seconds and if this time pass it should resolve new hostname... Idea: create a CNAME record from ```srv-mooc.siveer.fr to srv-bo.siveer.fr and see if this gonna work – Zhivko Zhelev Mar 01 '22 at 15:37
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.

wiltomap
  • 73
  • 9