Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678

The Command Shell Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 9:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
If you want to know if a sysadmin has enabled SA, I would ensure an audit is in place, with logging to a file. Set permissions for your sysadmin groups (Windows level) and the service account for SQL Server to write only. Set read permissions for the audit groups.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1441328
Posted Thursday, April 11, 2013 4:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,612, Visits: 32,208
opc.three (4/8/2013)
If nuclear blasts were the only reason to leave xp_cmdshell disabled then you would have a great point. Unfortunately, that is simply too narrow a view.


The circle continues so you've compelled me to say it again. If someone has SA privs, they can get to the command line whether xp_CmdShell is enabled or not. If someone doesn't have SA privs, they can't get to the command line whether xp_CmdShell is enabled or not. I believe it's a narrow view to think otherwise.

You're turn.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1441518
Posted Thursday, April 11, 2013 8:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 7,135, Visits: 12,749
Jeff Moden (4/11/2013)
opc.three (4/8/2013)
If nuclear blasts were the only reason to leave xp_cmdshell disabled then you would have a great point. Unfortunately, that is simply too narrow a view.


The circle continues so you've compelled me to say it again. If someone has SA privs, they can get to the command line whether xp_CmdShell is enabled or not. If someone doesn't have SA privs, they can't get to the command line whether xp_CmdShell is enabled or not. I believe it's a narrow view to think otherwise.

You're turn.

I remember, gee, must be about 2 years ago now, on a similar thread when I tried to end the round-and-round with an "I'll agree to disagree." For whatever reason that idea did not seem amenable to you that day and I think you literally responded with something like "no, wait...", and proceeded to build more of a case in that followup post. At the time I had a suspicion that you thought I had some wrong thinking that you could simply correct with more dialogue. Hopefully it is clear at this point that we are just two people with different views on the same subject. It's funny too because I think we agree on almost all other topics where we have compared notes so in a way it's a shame we have spent so much time on this one.

I have made several points surrounding security, auditability and application design explaining why one should not choose to use xp_cmdshell and I stand by those points. You stand by your points regarding members of the sysadmin Role, having your code be code consistent within backups, only needing to know one language with a sprinkle of cmd and Power shell, so here we are. Nowhere new, really, but maybe a bit more educated about each other's position. Hopefully others have benefited from our various dialogues as well. At present, as with before, and all along since that day 2 years ago really, I'll agree to disagree.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1441540
Posted Thursday, April 11, 2013 9:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,612, Visits: 32,208
Well said on all counts, Orlando. I did try to end our conversation a couple of posts back. Time for us to move on.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1441546
Posted Thursday, April 11, 2013 9:39 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:42 PM
Points: 635, Visits: 2,215
Jeff Moden and opc.three

Now that you two have finally agreed to disagree, I'm going to throw oil on the fire.

Vendors are not your enemy!

I (and our team) were responsible to upgrade our company's SW on our hosted servers and on the clients hosted implementations of our software. Our development staff hard coded the use of mixed mode and use of SA to create the added logins and roles needed to run replication and the rest of it.

More than once we ran into an IT staff that said "We can't give you the SA password." We had a choice, go around you and change the SA password, or wait for you to give it to us. We didn't care if you changed it after the upgrade.

The problem is that if you don't trust your vendors you are not logical. We don't want to harm the end-user or anyone else. We want the upgrade to work.




----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.
Post #1441559
Posted Thursday, April 11, 2013 11:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,612, Visits: 32,208
Jim P. (4/11/2013)
More than once we ran into an IT staff that said "We can't give you the SA password." We had a choice, go around you and change the SA password, or wait for you to give it to us. We didn't care if you changed it after the upgrade.

The problem is that if you don't trust your vendors you are not logical. We don't want to harm the end-user or anyone else. We want the upgrade to work.


You've just proven why some vendors actually are the enemy and don't deserve the trust nor the business. It's definitely not a logical fear. It's a time proven observation. Even with your best intentions at heart, it's not your box and you're not ultimately responsible for it or the data it contains. Yet you saw fit to break into the box as "SA" instead of working with the person who actually is responsible for the box. You could have even cost that person their job for doing their job.

Such actions on you and your team's part not only break the policies of nearly every company I've ever worked for or ever hope to work for, they break SOX, SEC, and ISO policies/regulations, as well. Had you been an outside vendor, your actions would have broken many State and Federal laws.

The logical way to have done this would have been to take the proper steps where the people you went around could have been alerted to properly sanctioned company actions. You and your upgrade team could have been granted "SA" privs without knowing the "SA" password instead of taking matters into your own hands.

I do, however, thank you tons for proving my point that no one other than the DBA's should ever be granted "SA" privs.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1441572
Posted Friday, April 12, 2013 8:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
Regarding vendors who support a client owned server, or clients who lease a vendor hosted server, there can be an account setup with elevated permissions that you enable or disable for them as needed. However, they don't really need sysadmin membership just to deploy objects or run DBCC commands. That requires only db_ddladmin or db_owner membership. They don't need permissions that grant control of the server.

As for things like 3rd party applications or monitoring tools, they don't need sysadmin privilege at all. They just need a "poweruser" account with: data reader, maybe writer, view server state, showplan, view schema definition, and that should cover it. If their software is designed in a such a way that it just won't perform it's normal daily functions without membership in 'SA', then that calls into question whether the developers really know what they're doing. You don't want someone or something with less SQL Server knowledge than yourself running roughshod over your server.

When an organization is evaluating 3rd party applications, that should be a primary consideration:
How much privilege do they require over the server for their software to be functional?
Post #1441738
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse