ODBC connects with local IP but public IP produces 18456 "Login Failed for User XXX" (same user that works in local IP.

Question

I am using SQL Server Management Studio 18.
I configured ODBC connections to my SQL Database with the local IP and with the Public IP of the server. The local IP ODBC works fine within the network. But the public IP ODBC is able to access the database but produces the 18456 "Login Failed for User XXX" although I am using the same SQL login user than the local IP ODBC connection. This happens in any computer in or out the network. I have tried different users (including SU) with the same result. It is as it could access the database but not the user information (only with the public IP ODBC Connection).
The server firewall has inbound and outbound rules for the SQL port (TCP) and an inbound rule for the SQL Browser. The user computer firewall has inbound and outbound rules for the SQL port (TCP).
The ports are open in the router (both UDP and TCP) for the SQL port. I also opened the 1434 port in UDP as I saw in a post, but it did not make any difference.
Strangely, the SQL Server log does not show the refused connections, so I cannot know the detail of the error.
I sense that it is a configuration problem of the user that allows local connections but not public connections, but I do not know how to fix that. Any ideas? Please be patient as I am kind of new to SQL Server.
Thank you in advance

Answer 1

Hi,

The state refers to like:

"Error Number: 18456"  
  
"Severity: 14"  
  
"State: 1"  

If you can see this information, or you can see this article, make sure the SQL Browser services are started, and open port 1433(you write 1434, if written wrong), you can test if can connect the public IP by SQLCMD, like sqlcmd -S public IP, 1433 -U sa

You can refer to this article to get some configuration tips:
https://www.experts-exchange.com/articles/5229/How-to-allow-ODBC-to-connect-to-SQL-via-and-external-IP.html

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Fernando Arbelaez ，

The error message that is returned to the client deliberately hides the nature of the authentication error, the key to this error message is the "State", this state will map to an authentication failure condition, please check out your error log to find this information

Please check out this doc to compare the error state:
MSSQLSERVER_18456

Please enable SQL Server and Windows Authentication mode in properties->security, in connections->Allow Remote Connections

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you for your reply. The SQLState in the error message says '28000'. Strangely the SQL Serve logfile (in Server Management Studio) does not register the error. Should I look for the log somewhere else?
SQL Server and Windows Authentication mode and Allow Remote Connections are enabled. Otherwise I think I would not be able to connect via the local IP ODBC connection.
Any other suggestion? Could it be something related to the user qualifications?
Any help would be greatly appreciated.

Answer 4

Thanks again for your time. Yes, 1433 ports are open in both tcp and udp. I opened 1434 in UDP just in case, because they mentioned that in a post.
I executed the command with the same error. It asks for password and when I type it (the same user and password that works with the local ip) then it produces the same error.
The article you mentioned seems right what I need. I will have a deep look and get back in case I have any question.
Thanks again for your support.
Regards,

Answer 5

I finally found the problem. The IT people opened the right port but directed it to the wrong internal IP.... It took me some time to realize that. Once they fixed it, it worked perfectly. I nevertheless implemented all the recommendations in the article.
Thank you again for your help