removal of XP's

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 ?

many thanks

more ▼

asked May 04, 2011 at 01:14 AM in Default

avatar image

162 11 11 12

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered May 04, 2011 at 01:37 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

Thanks for reply. been advised by pen testers they present a security issue. Even though only owned by sys and no associated permissions present.

May 04, 2011 at 02:27 AM mickyd

This could get political within your work but I'd contest that a little. So long as the permissions are right then there is no appreciable risk that I am aware of. Can they give you an example, explain further? Can they reference any white papers or best practice that supports their point of view?

May 04, 2011 at 02:36 AM Fatherjack ♦♦

Agree with @fatherjack - there are lots of 'practice and patterns' type stuff available from Microsoft about securing SQL Server. I would prefer to follow their recommendations about their product, rather than a 3rd party without validated references.

May 04, 2011 at 04:07 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 04, 2011 at 06:24 AM

avatar image

K. Brian Kelley
1k 1 4 4

Brilliant explanation, thanks @K. Brian Kelley.

May 04, 2011 at 06:26 AM Fatherjack ♦♦

Indeed great help , thanks

May 04, 2011 at 06:43 AM mickyd
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 04, 2011 at 01:20 AM

avatar image

Kev Riley ♦♦
65.7k 48 63 81

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 04, 2011 at 06:13 AM

avatar image

6.4k 21 25 34

Thanks for link , excellent stuff

May 04, 2011 at 06:43 AM mickyd
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 04, 2011 at 01:14 AM

Seen: 1752 times

Last Updated: May 04, 2011 at 02:01 AM

Copyright 2017 Redgate Software. Privacy Policy