1

I'm trying very hard to go from Entity Framework 5 to 6. After solving a SimpleMembership issue that was in my Web.config I have the following error :

Login failed for user 'sa'

Anytime I try to reach a tab (on my page) that requires SQL Data display.

Here is my connectionStrings (the relevant one being DefaultConnection) : 

<connectionStrings>
     <add name="DefaultConnection" connectionString="packet size=4096;Data Source=MMSDEVNEW\SQL2008;Initial Catalog=CRAV34;User ID=sa;Password=*****;" providerName="System.Data.SqlClient" />

     <add name="CRAV34Entities" 
     connectionString="metadata=res://*/Models.CRA.csdl|
                                res://*/Models.CRA.ssdl|
                                res://*/Models.CRA.msl;
                                provider=System.Data.SqlClient;
                                provider connection string=&quot;data source=MMSDEVNEW\SQL2008;
                                                                 initial catalog=CRAV34;
                                                                 persist security info=True;
                                                                 user id=sa;
                                                                 multipleactiveresultsets=True;
                                                                 App=EntityFramework&quot;" 
     providerName="System.Data.EntityClient" />   

 </connectionStrings>

I read that this may be a permission issue or changing the security properties from the SQL Server Management Studio to "SQL Server and Windows Authentication mode". All of this has been done. The login and password are valid in the connectionString.

What could possibly cause this issue?

Improve this question

3 Answers 3

Reset to default
1

If you are sure the SQL Server is in "mixed mode", and that the password is correct for the "sa" account, I can only suggest it to be that the "sa" user doesn't have access to the named database.

In SQL Management Studio, expand the database branch, then security, then users, and check if the "sa" user appears in the list.

The best way to diagnose this error is to try and connect to the database server using SQL Management studio, logging in AS the user in the connection string.
If you can log in, see if you can see your target database in the tree on the left.
If you can, try and open the database node and see if you can see all the tables.
If you can, see if you can run a select query against one of those tables.

If you can do ALL of the above, then it's NOT an issue with SQL server, but rather your connection string.
However, I've never seen that error come up when it's NOT a SQL configuration issue.

Try it with SQL Management Studio from the same machine you're code is running and it should give you more information about what's wrong.
Edit your post / reply to this answer if this doesn't solve it, explain what you tried and what the result(s) were, and I'll update the answer to reflect what I'd try next!

edit 1:

Try changing your connection string to the following:

packet size=4096;Server=MMSDEVNEW\SQL2008;Database=CRAV34;User ID=sa;Password=*****;

edit 2:

If this connection string is valid, it could actually be the second one that is causing a problem, as EF would likely choose that one unless explicitly told to use the "default" one. As this second connection string doesn't have a password specified, it might be this that's causing the error. Can you try it again with a password specified?

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I have tried the three steps on the SQL Management Studio, all three were successful. The user "sa" appears in the "Login" folder in security. All the query executions work perfectly and the data displayed is right. Either I'm missing out on something, or my connectionString is false.
I still receive the same error sadly. Are we certain this error comes from either the DB rights or the connectionString?
I think what's trying to be said here is that the password is not specified in the second connection string and this could be the relevant one unless you have code that is telling EF to use the default connection. Hope this helps.
This seemed to be the issue ! Thank you very much, both of you. Please post an answer so I can validate it. (If I can, with the little reputation I have)
I can accept without any problem, but I can't upvote before I get 15 reputation. :-) Thanks again.
|
1

Just add Integrated security=true in your connection string 

    <add name="DbConnectionString" connectionString="Data Source=LENOVO\SQL2K12;Initial Catalog=EFCodeFirst;User ID=sa; Password=xxxxx;Integrated security=true" providerName="System.Data.SqlClient" />
Improve this answer

Comments

0

If you are using Integrated security=true, it could be the account you are running under does not have permission.

Try opening Visual Studio running as a different User. ( the user has permission on the database). Use Shift and right click on the pinned Visual Studio menu.

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.