Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

enter image description here

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?

share|improve this question
    
Using SQL Studio Express to manage databases on a SQL Server 2005? Which Version of SQL Studio Express do you use? Only 2005 or higher is possible to connect to a sql2005-Server. –  Ice Jun 10 '11 at 15:58
    
@Ice - good question. Yes, the non working install is Sql Server 2005 version, specific version "Microsoft SQL Server Management Studio Express 9.00.2047.00", the 64 bit version from here microsoft.com/download/en/…. On the XP machine where it is working properly, it is the same version. –  jlarson Jun 10 '11 at 16:08
    
It would be nice if you posted some screen shots. –  KCotreau Jun 22 '11 at 2:05
    
Have you tried connecting from any other 64 bit clients? Is the SQL Server in question 64 bit? Are the SQL Servers fully updated? –  Kirk Jun 22 '11 at 3:50
    
@Kirk - I've only tried with jdbc clients, none of which are explicitly 64-bit. And the server is NOT 64 bit, and are probably not fully up to date (but they aren't under my control). @KCotreau - Screenshot posted above –  jlarson Jun 22 '11 at 18:30

4 Answers 4

up vote 3 down vote accepted

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

share|improve this answer
    
I don't know what happened to the bounty (SE bug?), but I re-added it. I'll try out your suggestions a bit later, though I already tried the KRShowKeyMgr, see harrymc's answer below. –  jlarson Jun 23 '11 at 17:22
    
Somehow missed his answer...was too focused on other things. –  KCotreau Jun 23 '11 at 17:29
    
@KCotreau - ok, thanks for your time. Here are some responses: 1) what do you mean by instance name (database name? server name? something else? how do I find that)? 2) I am certain I am typing the password correctly (at least most of the time) because I have no trouble logging in via other clients. I've tried pasting, typing, same thing.. 3) I believe the fact I can connect via other clients excuses software firewalls from being the issue (I know of none anyway except the built in Windows FW which is appropriately opened) (continued below) –  jlarson Jun 24 '11 at 16:06
    
@KCotreau continued: 4)I have no reason to believe either SQLCMD or Studio is connecting any way except TCP, everything is default, and I could see TCP traffic flowing on WireShark (though I could not interpret the stream), similar amounts to when I connected via other clients 5) I am not allowed to tinker with the SQL servers setup in these cases so that leaves out Shared Memory, modifying "sa", etc. 6) as mentioned KRShowKeyMgr led nowhere 7) This is happening to several SQL Servers, and in all cases jdbc based clients work, SQL Studio doesn't (and same exact error). Suspect a Studio bug. –  jlarson Jun 24 '11 at 16:13
    
Well, not being able to tinker, I have a strong feeling you will not solve the problem because changes will most certainly have to be made. Why don't you give that list to your IT people, and have them troubleshoot based on it, and maybe they can help you resolve it. To answer the one question I can: Server name is exactly what it seems like...that IP address you are connecting to probably has an actual name, and when you install SQL, the databases run under an "instance". You can find this info by executing this select statement on the server: select @@servername + '\' + @@servicename –  KCotreau Jun 24 '11 at 17:30

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
    
share|improve this answer
    
I have not lost the password -- as mentioned, I can connect AOK from another machine with the same password, and from the same machine using a different SQL Client. –  jlarson Jun 14 '11 at 13:33

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.

share|improve this answer
    
thanks, I never knew about this. However, the program comes up with an empty list, so it must not be involved. –  jlarson Jun 19 '11 at 23:10
    
Have you tried to run SQL Studio as an administrator ? –  harrymc Jun 20 '11 at 8:48
    
yes, I've tried that, no luck... –  jlarson Jun 20 '11 at 15:02
    
Is it possible that you have a cASe sEnsiTiVitY problem with the database name and/or user+password ? Errors with the database name are also logged as login failures. –  harrymc Jun 20 '11 at 15:22
    
@harrymc - i wasn't aware of that. but I've crosschecked the case with the database and user names, and getting those 100% correct (as well as the password, which I am certain I'm getting correct) still isn't helping... –  jlarson Jun 20 '11 at 18:22

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.