JDBC set_approle

Question

I am trying to set an application role through a JDBC connection using a prepareCall. It seems to work ok (i.e. syntax wise) but SQL server 2008 returns this error:

Application roles can only be activated at the ad hoc level

I am not firing this from within a stored procedure or anything, just directly from my JDBC connection, as such:

CallableStatement cstmt = con.prepareCall("{call sys.sp_setapprole(?, ?, ?, ?)}");
//setup all the IN and OUT parameters here
cstmt.execute();

Any ideas for why this doesn't work?

Answer 1

As it turns out the JDBC driver I was 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.

Answer 2

Use the following code to execute sp_setapprole, it wors for me!

// substitute userName and password with your own code
try {
  String sql = "EXEC sp_setapprole '" + userName + "', '" + password + "'";
  Statement st = con.createStatement();
  st.execute(sql);
} catch (Exception ex) {
  ex.printStackTrace();
}