dbcc checkstorage inside rpc

[Former Member]

HI

Im trying to set up regular dbcc checkstorage in our system and encountered a problem.

What it boils down to :

If i make a procedure, eg:

create procedure sp__dbcc_db_pubs

as

dbcc checkstorage (pubs2)

and a call it ,

exec sp__dbcc_db_pubs

everything works great, same as if i just manually enter dbcc checkstorage (pubs2)

But if i call

loopback...sp__dbcc_db_pubs -- loopback is correctly defined, everything else works ok

i get the following error

The entries in dbcc_config for database 'pubs2' are either missing or invalid. Use sp_dbcc_evaluatedb to determine appropriate values for the entries and sp_dbcc_updateconfig to set them.

The same thing happens with checkverify

I have ASE 15.0.3 ESD #4 on 64bit RHEL

Any thoughts why is this happening and how to fix it?

-- I need to do the checkstorage inside an RPC, because the login that is supposed to be running the checkstorage has indirect_sa_role, which i need to activate, as described in http://www.sypron.nl/grant_sa.html .

Thanks

Karel Ditrich

[Former Member]

Hi Karel,

I am wondering if you noticed that in the document written by you mention in your query, he says that "rpc method does not work for dbcc commands". He mentions two reasons for it. The first reasoning sybase_ts_role" would not be valid for 15.0.2 since sybase_ts_role is available to sa_role by default but the second point regarding the trace flag might still be true.

warm regards,

sudhir

This is the excerpt from the link (http://www.sypron.nl/grant_sa.html)

• While the mechanism described in this document is quite powerful, some things cannot be implemented this way. For example, most dbcc commands require sybase_ts_role, which cannot be granted to other roles (although this could be achieved by manually inserting a row into master..sysattributes). Still, the main problem is that any output from dbcc commands will only be visible after traceflag 3604 has been enabled; because this traceflag is session-specific, it cannot be enabled for the executing user's session using the mechanism followed by the stored procedures in this document, because the CIS RPC executes in a different process than the originating user.

[Former Member]

Hi Sudhir

I thought traceflag 3604 is only for seeing more output from the dbcc command, when call checkstorage locally, it runs whether the 3604 is on or not. Rob is mentioning it because you wouldnt see output from the dbcc command, which is not my problem, the command just wont run.

Karel

[Former Member]

Thanks. At least the rpc works for me. So can't recreate the issue that you are getting. Two suggestions:

1. Can you check the output of sp_plan_dbccdb 'pubs2' and see everything is aligned properly? From your comments it looks that dbcc checkstorage works for you without rpc.

2. Try the sp_rpc_setup_check from the same location fto make sure the rpc requirements are aligned properly, although I admit that does not look like the issue.

Notes: I am working with sysadmin privs and on Sybase ASE 15.7 ESD 4 , so not on same version as yours.

warm regards,

sudhir

use sybsystemprocs

go

create procedure sp__dbcc_db_pubs

as

dbcc checkstorage (pubs2)

go

grant execute on sp__dbcc_db_pubs to public

go

exec sp__dbcc_db_pubs

go

output.

Checking pubs2: Logical pagesize is 2048 bytes   

DBCC CHECKSTORAGE for database 'pubs2' sequence 15 completed at Aug  7 2013  1:38AM. 0 faults and 0 suspect conditions were located. 0 checks were aborted. You should investigate the recorded faults, and plan a course of action that will correct them.  

exec sp_configure 'enable cis', 1

go

--reboot database server

exec sp_configure 'cis rpc handling', 1

go

exec sp_addserver testserver_ALIAS, null, testserver

go

sp_helpserver testserver_ALIAS

go

output

testserver_ALIAS testserver     [NULL]     [NULL]     ASEnterprise     no timeouts, no net password encryption, writable , enable login redirection      4     1000   

exec testserver_ALIAS...sp__dbcc_db_pubs

go

Checking pubs2: Logical pagesize is 2048 bytes   

DBCC CHECKSTORAGE for database 'pubs2' sequence 16 completed at Aug  7 2013  1:42AM. 0 faults and 0 suspect conditions were located. 0 checks were aborted. You should investigate the recorded faults, and plan a course of action that will correct them.  

Message was edited by: sudhir dubey

[Former Member]

sp_plan_dbccdb shows that my dbccdb is indeed large enough and yes, dbcc checkstorage works fine without the rpc.

Ive also tried loopback...sp_dbcc_plandb pubs2 as well as loopback...sp_dbcc_evaluatedb pubs2 and both of them have the same output whether called locally or through rpc.

sp_rpc_setup_check has return status 0, so everything is working ok on that front also.

I havent found in patch notes anything that would indicate there was a change in this matter between 15.0.3 and 15.7, but I will upgrade my test server to 15.7 and try again.

However, even if this will work in 15.7, i would prefer making it work in 15.0.3...

[Former Member]

Hello Karel,

I was able to recreate the issue you are having with the same script that I copied above in version 15.0.3 ESD 1.

Looks to be a version issue after all.

Checking master: Logical pagesize is 2048 bytes

DBCC CHECKSTORAGE for database 'master' encountered an error. Some results may not be available. Correct the problems reported and reexecute the CHECKSTORAGE command.

Number (9965) Severity (20) State (1) Server (testserver) Procedure (sp__dbcc_db_pubs) The entries in dbcc_config for database 'master' are either missing or invalid. Use sp_dbcc_evaluatedb to determine appropriate values for the entries and sp_dbcc_updateconfig to set them.

This works without rpc.

Let me know how you fare with 15.7.

warm regards,

sudhir

[Former Member]

Hi Sudhir

Well I finally got around to do it and it works fine in 15.7. I have no idea why this is, but it is another reason for upgrading I can present to my superiors.

Thanks for your help.

Karel

[former_member188958]

Hi Karel,

I don't understand why you think it needs to be run as an RPC (unless you need someone logged into some other ASE to be able to execute it remotely).  The indirect_sa_role method should work just fine with a local procedure.  What happens if that login executes the procedure locally?

-bret

[Former Member]

Hi Bret

It works ok when i execute it locally, but i cant because of the issue described here :

• The problem lies in the fact that the user executing the above version of sp_kill may be able to interrupt execution of the procedure by hitting CTRL-C. When this happens after indirect_sa_role has been enabled but before it has been disabled again, sa_role would remain enabled for the user's session. Obviously, this would create an unacceptable security problem. To overcome this security issue, a slightly more elaborate setup is used. Instead of executing the stored procedure directly, it is executed indirectly as an RPC.

Since some other people know the password for this login, they would be able to get sa_role active this way. So from a security standpoint, it wouldnt matter if the login had indirect_sa_role or regular sa_role.

Or maybe i dont understand the issue correctly, if so then please explain further, since i would also need to convince our IT security that we got it wrong.

Thanks

Karel