Password mismatch while logging to sql server

问题:

Alright, I have a classic asp application and I have a connection string to try to connect to db.

MY connection string looks as follows:

 Provider=SQLOLEDB;Data Source=MYPC\MSSQLSERVER;Initial
 Catalog=mydb;database=mydb;User Id=me;Password=123

Now when I'm accessing db though front-en I get this error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'me'. 

I looked in the sql profiler and I got this:

 Login failed for user 'me'.  Reason: Password did not match that
 for the login provided. [CLIENT: <named pipe>]
 Error: 18456, State:8. 

What I've tried:

1. checked 100 times that my password is actually correct.
2. Tried this: alter login me with check_policy off (Do not even know why I did this)
3. Enable ALL possible permissions for this account in SSMS.

Update: 4. I've tried this connection string: Provider=SQLOLEDB;Data Source=MYPC\MSSQLSERVER;Initial Catalog=mydb;database=mydb; Integrated Security = SSPI

And I got this error:

Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database mydb requested by the login. The login failed.

回答1:

In my case, I had a connection string working with SQL Server 2008 R2, but after updating to SQL Server 2014, I received the mentioned error. Here is what my original connection string was like:

server=.\SQLEXPRESS;database=mydb;User Id=me;Password=123

Here is the update connection string that works with 2014:

Integrated Security=SSPI;Data Source=.\SQLEXPRESS;Database=mydb;User Id=me;Password=123

回答2:

Change your connection string to:

Provider=SQLNCLI10;SERVER=MYPC\MSSQLSERVER;DataTypeCompatibility=80;Database=mydb;User Id=me;Password=123

It worked for me. Try to use IP if server is remote or (local) if it is on the same machine.

Hope this helps.

回答3:

Try in this format as below.

strConnection ="Driver={SQL Server};Server=serverName;Database=DatabaseName;Uid=userID;Pwd=Password;"

Thanks
Jinesh Jain

回答4:

Try switching up your User Id to

MYPC\me

Maybe adding in the domain for the User ID will help Sql server resolve permissions

回答5:

Can you log in using management studio with this user, and see the database you are trying to log into?

Perhaps there is an character encoding issue with the file that holds the connection string. Is the connection string stored as text in its own file? If so, try opening that file in Notepad++ and check what is selected in the "Encoding" menu at the top. Try changing it to UTF-8 or ANSI.

UPDATE

I just read some comments, and it sounds like you can't log in using management studio. It sounds like the Windows Authentication Mode vs SQL Server Authentication Mode is a good candidate for a root cause.

Try this:

• Login to management studio and connect to your database using sa
• Right-click the database server in the object explorer on the left
• Go to the Security tab
• Make sure you are using "SQL Server and Windows Authentication mode", not just "Windows Authentication Mode"
• If you are currently using "Windows Authentication Mode", change it
• Find your application user in Security > Logins
• Right-click > Properties
• Make sure "SQL Server authentication" is selected
• If its not, you can try to change it, or create a new user, and make sure you select "SQL Server Authentication"

回答6:

The way should be like for example...
Provider=SQLNCLI;Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes;

(Trusted_connection just in case, that's optional I believe)

回答7:

First, do you understand the difference between SQL Server authentication and Windows Authentication? Based on some of the comments, I'm not sure.

You state that you checked your password 100 times; did you log into SSMS using SQL Server Authentication and verify it there? My theory is that you have a SQL Server account with the same name as your Windows account, and that you're connecting into SSMS using the Windows account, and NOT the SQL account.

回答8:

Here is a reference of the various ways you can connect to SQL server 2005, with sample connection strings:

http://www.connectionstrings.com/sql-server-2005

Or to connect to SQL Server 2008:

http://www.connectionstrings.com/sql-server-2008

Please remember that you should have the corresponding client piece installed on the computer that is running the ASP app, that is the SQL Server native client.

Don't know which method are you using but there are two ways OLE DB applications can initialize data source objects:

IDBInitialize::Initialize 

IDataInitialize::GetDataSource (seems like you are using this one)

Both methods initialize the same OLE DB connection properties, but different sets of keywords are used.

If you are using IDBInitialize::Initialize

the keyword for userid is UID
the keyword for password is PWD
NO Provider keyword may be used
Trusted_Connection keyword may be used and should be No

If you are using IDataInitialize::GetDataSource

the keyword for userid is User ID
the keyword for password is Password
the Provider keyword is for SQL Server Native Client and it should be SQLNCLI10
the keyword for aunthentication Integrated Security you can remove it, or use SSPI only if you wish Windows authentication

I made few changes to your original connection string below, try it out:

Provider=SQLNCLI10;Data Source=MYPC\MSSQLSERVER;DataTypeCompatibility=80;Initial  Catalog=mydb;User ID=me;Password=123 

This document describes the connection string keywords for each connection method for OLE DB applications using the SQL Server Native Client when connecting to SQL Server 2008 R2.

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

回答9:

Sure your Computer name is all uppercase? I found some time ago that a name in camelCase or with spaces within can cause such behavior.

If it's not, change it adding at least one letter or the change won't be succesful.