Search code examples
c#databaseentity-frameworkpermissionscredentials

Can Entity Framework execute stored procedures without access to sp_executesql?

Good day all,

I have inherited multiple desktop applications which have a heavy reliance upon stored procedures for data operations. These apps were written in VB6, and I am currently attempting to figure out how to port them to .Net 5 or .Net 6.

I understand that Entity Framework Core is capable of executing stored procedures. However, in a video I watched recently, I came to understand that Entity Framework Core executes data functions via the stored procedure sp_executesql, to allow for execution of whatever procedure a developer may have dynamically generated.

However, as the video linked above mentions, this presents a security hole for desktop applications. To fire sp_executesql, users of desktop applications must have credentials which can fire it upon their system. These values may be encrypted, but encryption is not invulnerable.

If I create and utilize database credentials that do not have access to sp_executesql, but do have access to the stored procedures my colleagues have made, will Entity Framework be capable of firing the latter?

Solution

  • You can use a "Raw SQL Query" to execute your stored procedures directly, without requiring sp_executesql:

    var customers = context.Customers.SqlQuery("dbo.sp_getcustomers");

    or:

    var customers = context.Customers.SqlQuery("dbo.sp_getcustomerbyid @p1", customerID);

    SqlQuery returns a lazy-loading IEnumerable<T>.

    As an aside, raw SQL queries are incredibly useful for more than just calling stored procedures. Instead of relying solely on Entity Framework's SQL generation mechanism (which can sometimes produce sub-optimal SQL), you can just execute an arbitrary, well-formed SQL statement of your own choosing, directly.

    Further Reading:
    Raw SQL Queries (EF6)
    Database.SqlQuery Method