Application Roles can only be activated at the adhoc level

Question

I'm trying to execute "sp_setapprole" from within a JAVA application. My application is running on WAS 8.0. using standalone sqljdbc.jar.

My code is directly trying to run sp_setapprole as below.

String sql = "exec  sp_setapprole 'username', 'password';

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

 Connection  connection  = DriverManager.getConnection(url, user, password);

PreparedStatement prepStmt = connection.prepareStatement(sql);

prepStmt .executeQuery()

When i run sp_setapprole, i'm seeing Error 15422 "Application roles can only be activated at the ad hoc level".

When i run the same application on websphere 6.1, which uses embedded ConnectJDBC driver for MS SQL server i dont see any error.

I have tried using both sqljdbc.jar ver 3.0 and 4.0. Both gives the same kind of  error for sp_setapprole.

Is it the issues with the latest sql drivers?

Answer 1

sp_setapprole stored procedure can be executed only by direct TSQL. You cannot be execute it within another stored procedure or from within a user-defined transaction.

Take a look at similar thread:

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/9d39f565-4b19-42ee-ae06-6dad482f7dd5/

---

Kindly mark the reply as answer if they help

Answer 2

I think you should skip the prepareStatement thing.

The key here is how the client API executes the command behind the scenes. In this particular case, the command should be executed in verbatim. When you prepare a statement, the API sends the command to SQL Server, and receives an id through which it can execute the command multiple times. Which can be great if you intended to execute it many times, but this is not what you want here.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

I'm directly executing the sp_setapprole from the prepared statement and not running it inside any other stored procedure.

The same things works fine with Websphere 6.1 embedded ConnectJDBC driver for MS SQL server.

I'm trying to run this call using sqljdbc.jar in a standalone class. Is it anything to do with the latest driver?

Answer 4

If you have questions about the JDBC thing, you should try the Data Access forum.

I don't know more Java than it's south of Borneo, but I can see that making a prepared statement can put you into trouble. I would suggest that you take that out.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Thanks Erland!!!

Replacing PreparedStatement with Statement object work fine. I dont see the error message any more. Bu

t i wonder why prepared statements dont work with sp_setapprole. The error says "Application roles can only be activated at the ad hoc level" which means sp_setapprole is executed within some other Stored Procedure. IS using PreparedStatement creating some kind of a SP and makes sp_setapprole run inside that SP??

---

Sushma

Answer 6

Hi SushmaJay,

As it turns out the JDBC driver you were using, Microsoft's JDBC driver, has no way of turning off Prepared Statements or Statement Pooling. So everything that was sent to the database was wrapped in a sp_prepexec() which the sys.sp_setapprole() detected and didn't like as it can't be wrapped inside another procedure and must execute on its own directly on the database. The solution unfortunately is to go with another JDBC driver.
Please see this similar thread and the above is the answer:
http://stackoverflow.com/questions/6833278/jdbc-set-approle

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

The error says "Application roles can only be activated at the ad hoc level" which means sp_setapprole is executed within some other Stored Procedure. IS using PreparedStatement creating some kind of a SP and makes sp_setapprole run inside that SP??

Yes. I recommend that you use SQL Server Profiler to see what the API is actually submitting behind your back. Many times this is an abstraction you don't need to care about, but there are also situations such as this one when you need to understand what's going to be able to diagnose problems.

When you submit a statement like "SELECT * FROM Orders WHERE OrderID = 11000" from Manangement Studio, that is exactly what SQL Server sees.

However, in a client application it is likely that the order id is a parameter that comes from somewhere. Some inexperienced programmers write this as:

   cmd.Text = "SELECT * FROM Orders WHERE OrderID = " + txtboxOrderID;

This results in the same as above. This is bad for a couple of reasons; one is the risk for SQL injection.

The proper way to write this in C# is:

   cmd.Text = "SELECT * FROM Orders WHERE OrderID = @OrderId)";
   cmd.Parameters.Add("@OrderID", SqlDbType.Int);
   cmd.Parameters["@OrderID"].Value = Convert.ToInt32(txtboxOrderId);

This closes the door for SQL injection, and also utilises the SQL Server cache better. However it results in this the following SQL being sent to SQL Server:

  EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID = @OrderId)',
                     N'@OrderID int', @OrderID = 11000;

As you see, here is a kind of stored procedure call. (sp_executesql is an internal procedure in the engine.) So if you would invoke sp_setapprole as:

   cmd = "EXEC sp_setapprole @username, @password";
   cmd.Parameters.Add("@username", ...

It would fail in the same way as it did for you. In C# the proper way to invole sp_setapprole would be:

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Text = "sp_setapprole";
   cmd.Parameters.Add("@username", ...

In JDBC you use prepared statements. What happens in this case is that the API first submits the query text and the parameter list and gets back a handle. Then the statement is executed through the handle. This can be efficient if you need to execute the same statement many times with different parameters. These executions are preformed by various server-internal procedures akin to sp_executesql, leading to the problem that you ran into.

I don't know if there is something similar to CommandType.StoredProcedure in JDBC, but I would expect it to be, since that is a more efficient way of calling stored procedures.

---

Erland Sommarskog, SQL Server MVP, [e-mail]