Hi everyone. What effect does the removal of extended stored proceedures have ? Part of a security review been asked if we can remove some XP's. Examples xp_instance_regaddmultistring , xp_instance_regdeletekey , xp_regaddmultistring. As an example. In total there are about 30.
If the application does not use them , are they needed everytime SQL restarts etc.
Need to understand what issues removal will have ?
What are the actual reasons for the suggestion that they are removed? If there is another way to mitigate the risk that this audit claims then I would go for that first - as @Kev Riley says, changing the security would be the first place to look - actually removing the procedures would be well down the list. You could alter them so the have no effect before actually dropping them from your server.
Keep in mind that having the ability to execute these procedures from within SQL Server doesnt mean you can necessarily affect the registry, that still requires the necessary permissions in the server OS.
For the permissions changes check out http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=56104
answered May 04 '11 at 01:37 AM
Removal of some of the extended stored procedures, especially those that relate to the registry can result in a service pack or cumulative update install breaking. Not exactly a good thing, especially when you call Microsoft, you'll find you've rendered your SQL Server into an unsupported state.
Also, the removal of the extended stored procedures is not an effective control (tell your auditors and security personnel this). The reason it's not is that removing the extended stored procedures doesn't remove the vulnerability. The .DLL is still present and in most cases can't be removed because that .DLL provides other extended stored procedures you can't remove. So long as the .DLL remains, anyone with sysadmin membership or CONTROL SERVER permissions (SQL Server 2005 and up) can re-add those extended stored procedures at any time. Also, most of those stored procedures have no permissions on them. Since SQL Server is a DENY by default, that means unless you bypass security checks, you can't execute them. The only ones able to bypass those security checks are members of the sysadmin role and those with CONTROL SERVER permissions. So the only ones capable of executing the stored procedures are the ones capable of putting them back into place. So you don't effectively do anything by removing the extended stored procedures.
answered May 04 '11 at 06:24 AM
K. Brian Kelley
Would it not be better to remove/control permissions to use the XPs rather than removing the XPs themselves.
You could get into a whole heap of issues if you removed them, as you say, they could be used by the system itself, and I'm guessing Microsoft wouldn't be too helpful in providing support if you ran into bugs as a result.
answered May 04 '11 at 01:20 AM
Kev Riley ♦♦
I work with DoD standards for securing SQL Server installations and databases. Although they may not be the experts in security they can be used as a good example to follow. You can review them here: http://iase.disa.mil/stigs/app_security/database/sql.html
Within this checklist the only concern with the "xp_" external procedures is access to them. Overall they do not have to be removed to meet DoD standards, simply restricting access to them is sufficient and acceptable.
However I would restrict access to those that can use them to a very small number of individuals. Then also ensure that those that do have access have a strong/complex password.
answered May 04 '11 at 06:13 AM