Score:0

MySQL connector/ODBC connection error following a DNS mismatch

cn flag

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

vidarlo avatar
ar flag
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
wiltomap avatar
cn flag
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.
vidarlo avatar
ar flag
The error message says that the user is connecting from `srv-mooc.siveer.fr`...
wiltomap avatar
cn flag
Sorry I have edited my initial post to make it clearer.
vidarlo avatar
ar flag
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!
Manu avatar
us flag
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 ;)
wiltomap avatar
cn flag
Reverse DNS lookup is fine and pointing on the right server name. `srv-bo%` in lower case does not change anything...
cn flag
Is the MySQL server bound to a specific IP range?
wiltomap avatar
cn flag
Yes it does, but it is a 254 addresses classic range, in which both servers (ancient and actual ones) are included.
Score:1
us flag

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

wiltomap avatar
cn flag
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.
Brandon Xavier avatar
us flag
If you're checking the tables manually, you'll want to look at `mysql.host_cache` as opposed to `mysql.host`
wiltomap avatar
cn flag
I don't have such a table... The MySQL version is 5.5.62.
Brandon Xavier avatar
us flag
See edits for 5.5 MySQL (which would have been handy to know beforehand:-)
Brandon Xavier avatar
us flag
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)
Score:0
ng flag

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.

wiltomap avatar
cn flag
`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...
Zhivko Zhelev avatar
ng flag
When you creating the rule to block other connections how you specify host name of the allowed one?
wiltomap avatar
cn flag
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.
Zhivko Zhelev avatar
ng flag
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.
wiltomap avatar
cn flag
No it doesn't. As I wrote in my previous comment, I tried with entering `srv-bo` alone and I get the same error.
Zhivko Zhelev avatar
ng flag
```GRANT ALL PRIVILEGES ON yourDB.* TO srv-mooc@'srv-mooc.siveer.fr' IDENTIFIED BY 'password';``` like this right?
wiltomap avatar
cn flag
I'm using *MySQL Workbench* to define remote accesses. So this is defined within this application interface, inside filtering field.
wiltomap avatar
cn flag
Just updated initial post with screenshots for *MySQL Workbench*.
Zhivko Zhelev avatar
ng flag
yep better, please enter ```hostname``` in command prompt on the windows server
Zhivko Zhelev avatar
ng flag
and is it possible to just create two different users for the db and specify the full hostnames?
wiltomap avatar
cn flag
`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...
Zhivko Zhelev avatar
ng flag
try giving more permissions to this user ```srv-bo```
wiltomap avatar
cn flag
Same result, unfortunately...
Zhivko Zhelev avatar
ng flag
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
wiltomap avatar
cn flag
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...
Zhivko Zhelev avatar
ng flag
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
Score:0
cn flag

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.

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.