SQL logins do not work when connecting using SSMS

  • dfulton 11061

    Valued Member

    Points: 73

    Very strange issue happening here, and I am pretty much out of ideas.  Whenever I use SSMS to try to log onto an instance using my Windows credentials, I have no issues.  When I try to login using a SQL login I recently created, the login dialog box hangs for a few seconds and then closes like it has made as successful connection, except nothing happens.  No error message, nothing.  Another interesting note is that the same thing happens no matter what I put into the User Name and Password field. So I could literally just mash my hands on the keyboard for both User Name and Password and the login box will just disappear with no error message.  The interesting part is that if I try to log into the instance using sqlcmd at a command prompt, everything works fine.  I can connect using the same recently created SQL login and run queries just fine.

    I've checked the SQL Server logs and I don't see any errors showing there.  I've also confirmed that the server is set to mixed authentication.  I've tried running a trace while logging in to see if I could pin down any sort of error, but literally nothing shows there when recreating the issue.  I usually get something to show there if there's an attempted login successful or not.  The issue originally occurred with SSMS ver 17.1.  I've since uninstalled that and re-installed ver 17.3, but it did not help at all.  I've also run Windows Update on the server just in case, but that did not help, either.  All services are up and running and all network protocols and settings appear to be fine as well.

    I'm basically stumped so any ideas would be appreciated.

    Thanks!

  • Sue_H

    SSC Guru

    Points: 90011

    dfulton 11061 - Monday, November 13, 2017 1:24 PM

    Very strange issue happening here, and I am pretty much out of ideas.  Whenever I use SSMS to try to log onto an instance using my Windows credentials, I have no issues.  When I try to login using a SQL login I recently created, the login dialog box hangs for a few seconds and then closes like it has made as successful connection, except nothing happens.  No error message, nothing.  Another interesting note is that the same thing happens no matter what I put into the User Name and Password field. So I could literally just mash my hands on the keyboard for both User Name and Password and the login box will just disappear with no error message.  The interesting part is that if I try to log into the instance using sqlcmd at a command prompt, everything works fine.  I can connect using the same recently created SQL login and run queries just fine.

    I've checked the SQL Server logs and I don't see any errors showing there.  I've also confirmed that the server is set to mixed authentication.  I've tried running a trace while logging in to see if I could pin down any sort of error, but literally nothing shows there when recreating the issue.  I usually get something to show there if there's an attempted login successful or not.  The issue originally occurred with SSMS ver 17.1.  I've since uninstalled that and re-installed ver 17.3, but it did not help at all.  I've also run Windows Update on the server just in case, but that did not help, either.  All services are up and running and all network protocols and settings appear to be fine as well.

    I'm basically stumped so any ideas would be appreciated.

    Thanks!

    Does that only happen from your PC? Any difference from other PCs, any issues if you RDP to the server and try to use SSMS from there?
    It sounds like something that could be more related to the client and not necessarily SQL Server itself.

    Sue

  • dfulton 11061

    Valued Member

    Points: 73

    Hi Sue,

    Thanks for the reply.  This is occurring while I am RDP'd to the server so I'm doing all of my work directly on it.

  • Sue_H

    SSC Guru

    Points: 90011

    dfulton 11061 - Monday, November 13, 2017 2:25 PM

    Hi Sue,

    Thanks for the reply.  This is occurring while I am RDP'd to the server so I'm doing all of my work directly on it.

    Okay...so then this way. What about if you try it from another server or from your PC?
    As these are typically client level issues, you'd want to see if you can narrow down the client having the problems.

    Sue

  • Sue_H

    SSC Guru

    Points: 90011

    Sorry - also should have added...is there a difference when you use the IP address vs the server\instance name to connect with SQL Authentication using SSMS when on the server

    Sue

  • dfulton 11061

    Valued Member

    Points: 73

    I've confirmed this morning that the same issue happens when attempting to connect from a different server.  Windows authentication still works fine, but SQL Server authentication is still a problem.  I've tried in the past using the IP address without success.  I've confirmed this morning that is still the case.

    Thanks

  • Phil Parkin

    SSC Guru

    Points: 243549

    Can you confirm that the SQL instance has been restarted since it was switched to mixed mode?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • dfulton 11061

    Valued Member

    Points: 73

    This instance has always been in mixed mode.  It has been restarted several times since this issue first occurred as well.

    Thanks

  • Sue_H

    SSC Guru

    Points: 90011

    dfulton 11061 - Tuesday, November 14, 2017 8:23 AM

    This instance has always been in mixed mode.  It has been restarted several times since this issue first occurred as well.

    Thanks

    You would also want to check the event logs on the server. And you want to make sure you are capturing failed logins...and I'm guessing the answer is that there is nothing in the event logs and logging failed logins is enabled.
    But since requests don't just drop and do nothing and you have nothing in the error logs, no errors anywhere, nothing shows in a trace - so it's not even trying to go out to any server, you installed a new version of SSMS and these same issues only present with this one server and only with SQL logins on a server using mixed mode and with everything configured correctly,  then you should just open a ticket with Microsoft.
    I would guess it's not likely a bug and more likely configuration or process issue somewhere. When it's not a bug, there would be a charge if you don't have an allotment or agreement for opening tickets with Microsoft.

    Sue

  • Goozoo

    Grasshopper

    Points: 12

    I have the same issue.  did you ever resolve this?

  • Andrey

    SSChasing Mays

    Points: 617

    try to connect from command line using sqlcmd app

     

    example:

    -- locally/remotely

    sqlcmd -S [sql\instance] -U [username] -P [password]

     

    -- remotely

    sqlcmd -Stcp:[sql\instance] -U [username] -P [password]

    * [sql\instance], [username] & [password] should be replaced with appropriate values

  • Goozoo

    Grasshopper

    Points: 12

    Hi Andrey,

    Connecting with sqlcmd works locally and remotely without any issues.  The problem is specifically when using the SMSS client/tool and attempting to connect to the DB using SQL Auth.

    While testing by signing into windows with local windows accounts I found the problem did not exist.  This lead me to understand it was an issue with domain accounts.  I was able to resolve this issue by identifying the domain the account I was using and ensuring it was resolvable via dns.  Note: my servers are in a different domain to the user accounts.  ie logging onto domain1/server1 with domain2/user1 utilizing domain trust.  So once server1 was able to resolve domain2 I was able to logon as domain2/user1 and successfully connect to the Database using SMSS.

    I hope this information helps others because the fact no errors are presented or logged ANYWARE is not helpful.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply