Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Master SQL Query Analyzer verification request

I think I have all of the query analyzer results I require.  I just wanted to ensure that the way I present the information is accurate.  If I need to make any changes, please advise. Thanks.

USE master  
Go EXEC spconfigure 'show advanced option', '1'
GO
Reconfigure
GO
EXEC sp_configure 'remote access', '0'
GO
Reconfigure
GO
EXEC sp_configure 'allowed updates', '0'
GO
Reconfigure
GO
EXEC sp_procoptions
GO
Reconfigure
GO
EXEC sp_configuire 'XP_cmdshell', 1
GO
Reconfigure
GO
DENY db_datareader TO public
REVOKE Execute on [dbo].[sp_Mscopyscriptfile] FROM [public] CASCADE
GO
Reconfigure
GO
Use master
GO
Revoke execute on [sp_MSSetServerProperties] to [public]
GO
Revoke execute on [sp_Mssetalertinfo] to [public]
GO
sp_configure 'c2 audit mode', 1
GO
Reconfigure
0
awakenings
Asked:
awakenings
  • 8
  • 4
  • 3
  • +1
5 Solutions
 
SQL_SERVER_DBACommented:
looks good
0
 
ptjcbCommented:
EXEC sp_configure 'XP_cmdshell', 1

Why are you turning on xp_cmdshell? It is a security hole. Kind of odd to work on "hardening" the server and then opening the garage door.
0
 
awakeningsAuthor Commented:
SQL Server DBA,

    Thanks for noticing that!  I am a newbie at this stuff and someone had recommended doing that to turn that off.  It should be a 0 instead of a 1?  Will turning it off have any effect on the system (I may have bad advice).  Can you think if I am missing anything you would recommend?  I'll leave this open for other responses.  Thanks!

Awakenings
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
awakeningsAuthor Commented:
Do I have to have reconfigure everywhere or can I remove that?  I'm trying to look professional.

On that XP command shell, I didn't use a '.  Is that ok?
0
 
ptjcbCommented:
XP_CMDSHELL allows the user to send any operating system commands. It is one of the main tools for hackers when they use SQL Injection.

http://searchsqlserver.techtarget.com/general/0,295582,sid87_gci1125862,00.html
http://64.233.169.104/search?q=cache:Xv6TLOYeXgoJ:www.airscanner.com/pubs/sql.pdf+SQL+server+EXEC+'XP_cmdshell&hl=en&ct=clnk&cd=13&gl=us


http://www.unixwiz.net/techtips/sql-injection.html

You can find many more links on google - XP_CMDSHELL sql injection (40,900 pages referenced)

Most of the other xp_ stored procedures (extended stored procedures) are disabled in our shop.
0
 
awakeningsAuthor Commented:
ptjcb,

    I think many of those extended stored procedures will be deleted.  No one had written a hardening standards and I, being completely ignorant of databases, am writing the hardening standard.  I figured DISA would be a good STIG to work with as I know they are great for operating systems and network devices.  Any other suggestions are certainly welcome.

Thanks,

Awakenings
0
 
awakeningsAuthor Commented:
Folks,

    I'll give this an hour or two for others to comment.

Thanks,

Awakenings
0
 
ptjcbCommented:
a SQL lockdown script - be careful. If you run this you may disable some piece that your application needs. You might enjoy cherry picking the script and using them for yours.

http://www.governmentsecurity.org/archive/t7317.html

You should also have a script available that turns everything back on
0
 
Scott PletcherSenior DBACommented:
I think this should be close; you may have to add a RECONFIG or two between some of the sp_configures:


USE master  
EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'allow updates', 0
EXEC sp_configure 'c2 audit mode', 1
EXEC sp_configure 'remote access', 0
EXEC sp_configure 'scan for startup procs', 0
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE WITH OVERRIDE
GO
--DENY db_datareader TO public  --??
REVOKE Execute on [dbo].[sp_Mscopyscriptfile] FROM [public] CASCADE
REVOKE execute on [dbo].[sp_MSSetServerProperties] to [public]
REVOKE execute on [dbo].[sp_Mssetalertinfo] to [public]


0
 
Scott PletcherSenior DBACommented:
"looks good" is accepted??  When the script has multiple cmds that would never run?  Interesting.
0
 
awakeningsAuthor Commented:
ScottPletcher,

   Oh... I thought he took the time to examine every line and said that most things were fine.  I didn't realize that you had corrected problems he ignored.  Is there any way to change that?  If everything was ok, then I agree with giving him points, but if things aren't ok, then yes... It is a problem.  I thought it didn't matter.  Is there a way to re-assign the points?

Thanks for pointing that out.  I'm a database newbie and don't know anything about this stuff.

Thanks,

Awakenings
0
 
awakeningsAuthor Commented:
ScottPletcher,

    I put a notice in the community support for them to make the changes.  Thanks again for pointing this out.

Thanks,
0
 
Scott PletcherSenior DBACommented:
Excellent points you've made!  Sorry!

I should have made it clear that there were several things that I had corrected and/or added [see below for examples], so don't worry about point awards.

EXEC sp_configure 'allowed updates', '0'  --<< chg'd to "allow updates"
EXEC sp_procoptions  --<< replaced with sp_configure ''scan for startup procs'
DENY db_datareader TO public  --<< commented out this line because SQL has no such cmd
Reconfigure  --<< chg'd to reconfigure with override, since sometimes the override is required,
                     -- and even when it's not, it's allowed and doesn't hurt anything.
0
 
awakeningsAuthor Commented:
ScottPletcher,

    Thanks for being so thorough!  That is what I am looking for.  I may go through everything you have suggested and do one more spot check to ensure everything is ok.  If they allow me to change points, I still will.  Details are very important and I don't want these screwed up because it will come back to haunt me.

Thanks,

Awakenings
0
 
awakeningsAuthor Commented:
ScottPletcher,

     Why did you make the following change?

EXEC sp_procoptions  --<< replaced with sp_configure ''scan for startup procs'

Is there a distinction for 2000 or 2005 SQL databases?

Thanks in advance!

Awakenings
0
 
Scott PletcherSenior DBACommented:
Not that I know of; I'm not familiar with an "sp_procoptions" command either environment.  

I've seen "sp_procoption", but that's used to mark a single proc as a start up proc, not to check for start up procs.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now