Cannot connect with Sql Server Studio: error 18456, state 8 -- but password is correct!

Question

Using Sql Studio Express (on a Windows 7-64 machine) to connect to three different Sql Server 2005 servers using Sql Server Authentication, I'm getting Error: 1846, State: 8. According to http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx, this should be password mismatch.

Yet, when I use the same user names and passwords from SQLCMD and from jdbc based clients like Squirrel, it works fine to connect to all three servers. I can also log into them from an XP machine w/ Sql Studio Express using the same user names and passwords. And of course, I have quadruple checked all the normal things like my caps lock is not on, I am not set up with an alternative language keyboard, I am really typing the exact same thing in both cases, etc.

Anyway, why might I be getting a password mismatch error when the password is correct? Might it have something to do with the different versions of MDAC on the machines, and if so, is there a way to revert Windows 7 back to an older version of MDAC?

Answer 1

I would try the following things in this order of difficulty:

Before anything, I would try that 10.19.190.x IP address with the instance name: 10.19.190.x\instance_name, also by servername. I suspect it may be a protocol issue (more on this below), and this may change how it tries to connect.

Second, it simply could be a bug, but since some were cutting and pasting, it may just be a hidden character. I have seen this work for a few people in posts: After you enter the password, move the cursor back to the beginning of the login box, and the hit enter/connect. Are you copying and pasting the pw?

Are there any software firewalls in the way? Temporarily disable them as there may be additional ports in use, which are blocked.

I also have been wondering if one (SQLCMD vs. Studio) is connecting via TCP/IP, and the other by Named Pipes, causing a security failure for one. I would check the SQL Configuration Manager (not the Management Studio) to make sure all the protocols are enabled, and see what is set as the preferred protocol. Enable NamedPipes and TCP, and set Shared Memory to "enabled". I suspect this strongly. Check out this document: http://groups.google.com/group/microsoft.public.inetserver.iis.security/browse_thread/thread/68c216b10e7fa70/69aacf4a582ec20c%2369aacf4a582ec20c?pli=1

Try unticking the enforce password policy in the properties of the sa user. I also suspect this strongly.

Try running this command from a prompt:

rundll32.exe keymgr.dll, KRShowKeyMgr

If there any entries related to SQL, delete them. Try manually adding a credential for SQL. I have seen things about Vista (so I would assume Windows 7 too) stripping certain security tokens, and I could see this being bypassed at a command line.

I would also try resetting the sa password in SQL, and change it right back. Then if the sa account is being used to start SQL Server, change the password in any SQL Server-related services, and restart them (I am trying to synchronize them to match). Related to this are these commands:

    exec sp_password @new = 'sqlpassword', @loginame = 'sa'
    alter login sa
    with password = 'sqlpassword' unlock,
    check_policy = off,
    check_expiration = off 

Also, have you considered configuring login auditing?

http://msdn.microsoft.com/en-us/library/ms175850.aspx

If none of this works, it would be very helpful to see the format you are using for theSQLername to log into the SLQ Manager, and the SQLCMD string you are using. Of course, change anything specific, except it the username is "sa". One more question: One SQL server environment, or multi?

I found some of this here, but weeded through to find only the things that I thought could be relevant: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx?PageIndex=7#comments

Answer 2

Try to use Command Prompt to reset your lost password. I have used it to reset a lost password successfully on SQL Server 2000/2005.

1. Open a Command Prompt (Start -> Run -> cmd)

2. Type the follow commands, and press Enter after each line:

   Osql –S yourservername –E
   EXEC sp_password NULL, 'yourpassword', 'sa'
   GO
   

Answer 3

A long shot, but you might check for an incorrect password cached by Windows.

From Access Stored User Names and Passwords with rundll32.exe :

The Stored User Names and Passwords applet lets you assign user names and passwords to use when needing to authenticate yourself to services in domains other than the one you are currently logged into.

The normal way of running this applet can be difficult to find quickly, so here is a way to launch it using a desktop shortcut using the rundll32.exe program:

Click on START - RUN and type the following (followed by ENTER):

rundll32.exe keymgr.dll, KRShowKeyMgr

Afterward, you should logoff, then back in.

Answer 4

You might try taking your SQL Server install media, and installing a proper SQL Management Studio on your Windows 7 computer, and see if that works any better.