Unable to execute stored procedure in Entity Framework 6

Question

I have an application that uses EF6. I need to execute the stored procedure, so I do the following:

using (SqlConnection conn = new SqlConnection(context.Database.Connection.ConnectionString))
{                      
    SqlCommand cmd = new SqlCommand("dbo.ExampleStoredProcedure", conn)
    {
        CommandType = CommandType.StoredProcedure
    };

    cmd.Parameters.Add(new SqlParameter("@param1", "parameterValue"));
    SqlDataAdapter da = new SqlDataAdapter { SelectCommand = cmd };

    durations = new DataSet();
    da.Fill(durations);                        
}

My connection string from Web.config is:

<add name="MyEntities" connectionString="metadata=res://*/;provider=System.Data.SqlClient;provider connection string=&quot;data source=MyServer\DEV;initial catalog=MyApp;User Id=UserId;Password=password123;Application Name=Connection Secured;integrated security=false;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

When the code above runs I get an exception:

Login failed for user 'UserId'.

The exception also contains the SQL error code: 18456. I confirmed that user has full access to this SQL server, database, instance and stored procedure by logging into SQL server with this SQL account and running the stored procedure in question.

I also tried defining a new SqlConnection object:

 SqlConnection newCon = new SqlConnection("Server=MyServer\\DEV;Database=MyApp;User Id=UserId;Password = password123;");

and executing the stored procedure against it - it worked this time. So I do have a workaround, but would like to use the context.Database.Connection.ConnectionString object to avoid having multiple connection string in web.config file.

Any suggestions?

Check the value of `context.Database.Connection.ConnectionString`. — Kyle, Jun 13 '17 at 19:47
Does your UserID user have proper permissions to Execute stored procedures on the db? — Chad, Jun 13 '17 at 19:49
@Chad Yes, from my question text: "I confirmed that user has full access to this SQL server, database, instance and stored procedure by logging into SQL server with this SQL account and running the stored procedure in question." — myroslav, Jun 13 '17 at 19:53
@Kyle here it is: data source=MyServer\DEV;initial catalog=MyApp;User Id=UserId;Application Name=Connection Secured;integrated security=false;MultipleActiveResultSets=True;App=EntityFramework — myroslav, Jun 13 '17 at 20:00
Have you tried to make entity connection the same as the second one and see if it works ? — Rey, Jun 13 '17 at 20:05
@RajmondBurgaj I have not, the connection string for entities that I have in web.config has been generated when I added my data model in Visual Studio 2015 and I have not modified it in any way. — myroslav, Jun 13 '17 at 20:12
Please just replace it for a moment and see if it works because sometimes it is not well-generated, if thats not the case we can see another option — Rey, Jun 13 '17 at 20:13
@RajmondBurgaj I can't just replace the connection sting in web.config with the one I define in new SqlConnection object. It appears that Entity Framework expects a certain format of the connection string and "Server=MyServer\\DEV;Database=MyApp;User Id=UserId;Password = password123;" is not in that format. — myroslav, Jun 13 '17 at 20:40
Hmm I am not sure for that but I am not using the one entity framework generates. Here is an example what I am using `` — Rey, Jun 13 '17 at 20:48
@RajmondBurgaj That's interesting. I am getting System.ArgumentException: 'Keyword not supported: 'data source'.' when I use the connection string in that format. Are you using dbcontext or objectcontext? According to this: https://stackoverflow.com/questions/1404268/keyword-not-supported-data-source you do need metadata element in the connection string — myroslav, Jun 13 '17 at 21:09
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/146576/discussion-between-rajmond-burgaj-and-myroslav). — Rey, Jun 13 '17 at 21:16
Sorry to let you know I am using code first approach, if you are not using code first approach then you are right. Please lets continue in chat for more — Rey, Jun 13 '17 at 21:20

score 0 · Answer 1 · answered Jun 13 '17 at 21:37

This behaviour is by design for security reasons as this answer to a question suggested. I added Persist Security Info=True to connection string that Entity Framework generated in web.config file and I was able to reuse context.Database.Connection.ConnectionString to execute the stored procedure with ADO.

score 0 · Answer 2 · answered Jun 13 '17 at 23:31

You don't need to (and shouldn't) open a separate connection for this. You can use the same connection that the DbContext uses.

Just open the connection (it will be closed when the DbContext is disposed) and cast it to SqlConnection. eg

        db.Database.Connection.Open();
        var con = (SqlConnection)db.Database.Connection;

Unable to execute stored procedure in Entity Framework 6

2 Answers2