FIM CM and SQL APIs– The EXECUTE permission was denied on the object

  • Article

 

OK – time for the random Monday morning blog.

So I was working on some FIM CM SQL API testing and was hitting an error calling 'externalSubmitSuspendRequest'

clip_image002

So – based on some limited past experience I thought “Hrmm no DB access, well I do recall that the clmApp role would fix this..” so I granted him this role.

clip_image003

Sure enough – ran it again and the account could get past that error, only to hit another.

C:\Spat.CLM.App\FIMSQLapis - suspend\FIMSQLapis\bin\Debug>FIMSQLapis.exe
{29a06d65-0157-5113-2891-44153e24ffff}
MSBaseCSP
Active
---------------

Unhandled Exception: System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'externalSubmitSuspendRequest', database 'FIMCertificateManagement', schema 'dbo'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at FIMSQLapis.Program.Main(String[] args) in C:\Users\administrator.SPATFIM\Documents\Visual Studio 2008\Projects\FIMSQLapis - suspend\FIMSQLapis\Program.cs:line 168

Now - based on https://msdn.microsoft.com/en-us/library/bb468093(VS.85).aspx   it sounded like all that is needed was clmExternalAPI but it still does not create the external request. Continued errors.

Now sometimes you just need to talk to someone ( sometimes yourself -- out loud ) in order to walk through the logic. This person doesn’t even need to be familiar with the code , or technology – just a sounding board sometimes.

That someone wandered in at just the right time :

Someone: “Whatcha working on , how was your weekend?”
Me: “Weekend was good , working on this access denied error” ( show him SQL traces etc.. )
Someone: “Hrmm interesting. You sure he isn’t denied somewhere else?”
Me: “Huh? Denied no I didn’t den—oh wait. Hrmm… aha!! You sir are a genius!”

Looking at the stored procedure I was calling shows that the clmAp role has an explicit deny on execute. Kapow! Thx Odin.

clip_image004

Posting this here in case someone else is bitten by it.. now off to complete my suspend code….

Spatdsg