Can SqlServer be configured to not execute stored procs submitted as strings?

Question

Scenario A:

SqlConnection con = new SqlConnection(myConnString);
SqlDataAdapter adp = new SqlDataAdapter("EXEC spGetUserInfo 42", con);
DataSet ds;
adp.Fill(ds);

Scenario B:

SqlConnection con = new SqlConnection(myConnString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spGetUserInfo";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", 42));
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds;
adp.Fill(ds);

The question

In a discussion of how to fortify SqlServer against sql injections attacks separately from modifying the underlying application code calling the database, the question was raised whether SqlServer could be configured to simply not execute stored procs written in the manner of Scenario A, only allowing execution requests written in the manner of Scenario B. The theory being that Scenario A would be vulnerable to injection if the underlying app didn't perform input validation, allowing something like "EXEC spGetUserInfo 42; drop database foo; --" to possibly be executed, whereas Scenario B would simply fail to execute when SqlServer fails to convert "42; drop database foo; --" to an integer.

Is it possible to configure SqlServer to not execute stored procs called in the manner of Scenario A?

Answer 1

Edited: I stand corrected, I went back and read the TDS standard here:

http://www.sybase.com/content/1040983/Sybase-tds38-102306.pdf

The TDS_LANGUAGE command does support native parameters over the wire, so that is in fact how SQL Server receives parameterized queries.

As to whether there's a way to force use of parameters and not accept arguments in the CommandText, I don't know of a way to do so.

Answer 2

Not so far as I know, and I have looked pretty hard for a way to do it.

The closest thing that I can think of would be to somehow do something with the network so that only RPC connections to the SQL Server were allowed as the ".StoredProcedure" invocations normally come through RPC, but dynamic SQL requests cannot. However, I do not know enough about network management and server management to know if that is doable.

The other (and more standard) approach is to simply give users/apps only CONNECT access to your database, but not access to ANY of the database's objects. Then create stored procedures in their own SCHEMA and grant their owner/schema normal access to the database objects (tables, views, any other stored procedures, etc.). Finally then, grant the users EXECUTE access to the stored procedures in that Schema. This still would not prevent every possible dynamic SQL scenario, but it does take the danger out of them (and the point out of most of them).