0

I have tried every conceivable permutation for hours to try to connect to a remote SQL Server 2017 using SSMS. I keep getting:

Logging failed for user xxx. (Microsoft SQL Server Error 18456)
  • I know SQL Server is running on the remote machines since I can log in locally there using SSMS.
  • SQL Browser is running.
  • TCP/IP is enabled. There are no dynamic ports in IP All.
  • I know the firewall is not a problem since I can telnet to port 1433 from the remote machine, and I added the rule to allow access.
  • I have "Enable Remote Access" turned on in SQL Server.
  • I have both Windows and SQL Server Authentication enabled.
  • I have user mappings for the given databases.
  • I can connect locally using SSMS with the Sql Server Auth user/passwd that I am trying to use remotely.

When logging in from the remote client in SSMS:

  • I use an IP address for the server (although I can also browse to the server as I have UDP 1434 open).
  • I use SQL Server Authentication.

I looked at Event Viewer on the SQL Server machinee to see if it gives me more clues as to why the log in is failing, but it is worthless.

I saw this, but it is of no help.

What on earth could be left that is the problem?

Ivan
  • 7,448
  • 14
  • 69
  • 134
  • 1
    "I use SQL Server Authentication." <-- Well, you seemed to have covered all the transport layer issues. Did you ensure the credentials you are using are valid and have appropriate authorization? – Ross Bush Dec 31 '19 at 02:28
  • I can connect locally using SSMS with the Sql Server Auth user/passwd, but can't on the remote machine using SSMS and using the same user/passw. This is maddening. – Ivan Dec 31 '19 at 02:39
  • Try the MS [connectivity problem solver](https://support.microsoft.com/en-us/help/4009936/solving-connectivity-errors-to-sql-server). – SMor Dec 31 '19 at 02:52
  • Try the sql server error log instead of the event viewer. Also, see if this helps --> https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver15 – Ross Bush Dec 31 '19 at 03:00
  • Ok, that helps, the logs say, "Login is disabled, and the password is incorrect." State 7. But that is nonsense. I can log in locally with that user, and the same password! – Ivan Dec 31 '19 at 03:23
  • Make sure you are not using windows authentication when you log in locally. – Ross Bush Dec 31 '19 at 03:38
  • Right, I made sure I log in locally with SQL Server Authentication. See last bullet point in OP. No progress. – Ivan Dec 31 '19 at 04:06
  • I am beginning to believe that maybe using the IP adress instead of a FQ server name is not cool? Problem is, when I browse to the server, and I use the name in SSMS, that doesn't work at all. – Ivan Dec 31 '19 at 04:08
  • I finally got it to work. I don't really understand why it started working. The only thing I did differently is I used the View->Register Server method, browsing to the server, not using an IP, tried the SQL Server Auth method with user/pwd, and it works. – Ivan Dec 31 '19 at 16:55

0 Answers0