|
|
|
|
|
Database Scanner 3.0.1 1 2 3 4 5 6 7 8 9 10 Extended stored procedures represent a significant security risk on your server. These must be carefully managed to ensure your server is secure while not impacting functionality. To have a completely secure environment, you would remove just about every extended stored procedure. Of course when you do this, certain functions in administration tools that rely on those extended procs will no longer work. If you have created your own extended stored procedures, you can add those into a list of allowed procedures. The most baffling are the startup stored procedures. I can allow any, none, or a standard list. The any or none options are pretty straightforward. The one that drove me up a wall was standard list. Standard list of what? Are these the ones that SQL Server runs by default? If SQL Server requires a startup stored proc to run, why should I get errors on this when I say that none are allowed. If I have my own startup procs, why can't I add them to a list as well. It does a good job of listing out all of the extended stored procs. You would have thought they could have made the small effort to provide a list of startup stored procs instead of just giving the extremely confusing "Allow standard list". xp_cmdshell is a very well known security risk. I do allow xp_cmdshell to be run in my environments simply to do administrative tasks that are virtually impossible any other way, but the ability to execute it is severely limited by restricting non-sa's to run it using the SQLExecCommandExec/SQLAgentCmdExec account and then explicitly denying access to that account. I do not allow OLE automation procedures to be run on the server. Not only are they a potential security risk, but they are also a significant stability risk.
For most systems, I leave the OS settings at the default. Everything related to SQL Server should be sitting on an NTFS partition. If you are running things on a FAT partition, then you have a bigger security risk than OS permissions. The account SQL Server is running under will depend upon how your environment is setup. Running under local system is more secure, because you don't have an extra named NT account that can access the server. However some things like replication require the use of a named NT account, so some settings will depend on your environment. I normally run SQL Servers under a named NT account that is a local account. I do not allow SQLMail to be run on my production boxes. This is not because of security, but because of known problems. SQLMail has the habit of randomly causing tasks/jobs to quit executing. You can manually run them, but they will do nothing. They also won't fire off on a scheduled basis. It might be for all of the jobs/tasks or just some of them. You might have 500 servers running just fine and the 501st server will have a problem. They might run for months or years without a problem and then one day just stop running. Stopping SQLMail will cause them to start executing again. Since I run backups, DBCCs, etc. via tasks/jobs, SQLMail is not a risk that I will take on a production system. I will normally use a network monitoring package to provide e-mail notification of server problems. Since some environments require e-mail notification as part of application functionality, I will setup SQLMail on a separate low end server that performs this single function. This is normally a $500 - $1000 box that simply has a proc that runs in a loop and sends mail as it is needed. All of the other servers BCP into a mail table on that server to allow mail to be sent. This isolates SQLMail so that it does not impact functionality or security in an environment.
|
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.
