locked
Roaming Profiles and Error 18456 State 8 RRS feed

  • Question

  • Greetings,

    I run a departmental IT network for a small University. We have about 150 lab machines dual booting Ubuntu Linux and Windows 7. For our file server we run Samba 3.5 which handles Windows shares. In general it works great with our Windows machines. In addition to roaming profiles, we've also configured AppData/Roaming, Desktop, etc. to point to the X: drive, which is where the users home share is mounted.

    This semester we've installed Microsoft SQL Server 2012 on a Windows 2012 Server machine, and the SQL 2012 client on our Windows 7 lab build. In doing so we've uncovered a rather nasty issue with user logins. We use SQL authentication, the SQL server is a basic vanilla install.

    When a domain user from one of our Windows 7 clients starts up SQL Management Studio and attempts to login, we get an 18456 state 8 error, which is basically a bad password error. Oddly enough, if the planets align (i.e. psuedo-randomly), it lets the domain user login just fine. It's a problem that basically renders our lab SQL client useless to students. We've checked the server logs, and it does receive data from the client. The only error we get is 'bad password', nothing else. We've used wireshark to verify that the client and server are definitely communicating. There are no firewalls enabled on any machines at the moment.

    We've narrowed down the problem to the fact that AppData/Roaming is on a network drive. It seems as though something about making the Windows/Samba/UNIX jump is causing these issues. If we create local accounts with local Roaming folders, stuff works great. Additionally, if we change a domain account to use a local roaming folder, it works great too. Unfortunately, we are unable to make a global change mid-semester to disable redirection for AppData/Roaming.

    I have two questions. Why would a finicky connection to AppData/Roaming cause a bad password error? That doesn't really make sense. I realize that our setup is a bit different from most deployments out there, and evidence seems to point at Samba as the root cause. But a bad password error? Why does SQL Management Studio need to connect to the roaming folder to send a typed password over the network? Are there files in the Roaming folder are used in encrypting the password or something like that? Could it be that we've uncovered a bug in SQL Management Studio?

    I've done quite a bit of searching around, and it appears that you can't redirect SQL Management Studios AppData to any other folder. The only solution we can find right now is to create local accounts for those users on certain machines, which isn't really acceptable, but it's our only option at this point.

    If anyone has any insight into what may be happening here, please let me know!

    Thank you!

    Zach.


    • Edited by zcbethel Wednesday, February 20, 2013 4:21 PM
    Wednesday, February 20, 2013 4:20 PM

All replies

  • The SSMS Registered Servers window keeps info in the RegSrvr.XML file in C:\Users\<username>\AppData\Roaming\Microsoft\Microsoft SQL Server\110\Tools\Shell folder.

    Is your user trying to connect by using the server name in Registered Servers? If so, try connecting by just entering the server name in the Connect to Server dialog box.

    Also, confirm you can connect by using sqlcmd.exe, just to prove that the problem is an SSMS problem.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, February 20, 2013 9:00 PM
  • sqlcmd.exe worked just fine. I have been typing in the FQDN within SSMS when connecting.

    Interestingly enough, I just ruled out the Samba case by redirecting to a Windows share on Windows 2008 R2 instead. I get the same problem. That surprises me. Has anyone else had success with a redirected AppData/Roaming folder?

    Wednesday, February 20, 2013 9:49 PM
  • On that note, is redirecting the AppData\Roaming folder even a good idea? It seems like it's a supported feature in AD (since it has it's own redirection option), and it does help a lot with logon/logoff times. We have a few profiles in our network where the roaming folders are upwards of 400MB. That's a lot of data to send over the network. On the flip-side, it seems like some applications don't behave well with this change. Do you have any thoughts?

    Thanks!

    Zach.

    Thursday, February 21, 2013 2:55 PM
  • dont' know why ssms needs AppData\Roaming folder with password, but AppData\Roaming folder is for current system setting, just leave it on local system
    Friday, February 22, 2013 6:44 AM
  • In a lab environment such as our own, leaving AppData\Roaming on the local system (i.e., without roaming) is not an option, because our users regularly need to log into different workstations and have access to their application data (not just for ssms, but other applications as well).  On the other hand, AppData\Roaming is is often too large to include as part of the roaming profile, resulting in excessive login/logoff times for many of our users.  Therefore, we know of no other choice but to have AppData\Roaming redirected to each users' network share.

    Why does ssms not work if AppData\Roaming is redirected to a network drive?

    Wednesday, February 27, 2013 6:42 PM
  • Logged error message from SQL Server 2012:

    08/06/2013 16:15:29,Logon,Unknown,Login failed for user '<username>'. Reason: Password did not match that for the login provided. [CLIENT: x.x.x.x]
    08/06/2013 16:15:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8

    We have tried resetting the password on SQL Server 2012 and we are able to get back in just fine using SSMS. However, if I switch PC's or logout of Windows 7 and back in again, and use the same credentials that just worked. The same error message comes up. We have roaming profiles enabled and we are trying to resolve why this keeps happening. Any input would be most helpful. Thanks!


    • Edited by jnlickey Wednesday, August 7, 2013 6:31 PM
    Wednesday, August 7, 2013 1:06 PM