I was challenged recently to describe why Microsoft would have specific characteristics like xp openrowset and cmdshell if this attribute wasn’t full of security holes disabled by default. The jist of the conversation was that these attributes must include security holes that are serious Microsoft wouldn’t have disabled them. Empowering so would be completely negligent.
I’ve been a SQL Server DBA since 1996 and have observed the merchandise of Microsoft evolve over time. I believe it’s safe to say that Microsoft didn’t “get” security early on.
The new approach towards security of Microsoft: “We are going to reconsider our way of security. We’ll analyze our code for susceptibility. We are going to release patches as needed. So that you can keep the footprint small we are going to turn off most attributes by default. Turn it on if you want something. But should youn’t want it, leave it away. This way if your vulnerability is found in a merchandise you aren’t using, you will not be changed.”
I ‘d contend the most of the obvious security “holes” were a consequence of a mix of inferior layout and human error. Especially:
took too long to release patches once the susceptibility were identified and neglected to find susceptibility inside their source code
to keep the most recent patches implemented so leaving their servers exposed to known exploits
Microsoft
By searching out unpatched SQL Server’s this virus spread quickly. There were an incredible number of these servers exposed online, including the run by financial institutions of SQL Server. Sasser did lots of damage. Sasser did this by running remote procedure calls on servers that are unpatched.
The means to fix Sasser fashion strikes is of course is twofold. Microsoft must be ascertained to hunt down vulnerabilities in release patches and their code in a timely manner, and we as DBA’s must be alert in using those patches.
As it pertains to SQL 2005 and attributes like xp cmdshell that is _, we must remember that Microsoft assembled these characteristics allowing database programmer to develop programs that are rich effective at performing complex processing jobs. It’s the intent of NOT Microsoft that individuals keep these features turned off. Keep your program footprint modest and reduce your vulnerability to strike.
You could say the most risk-free SQL Server is one that’s turned off. That wouldn’t be invaluable to anyone, and somebody could walk away with a back-up of the database. They could just yank the hard drives out and place them in tote like yesterday’s filthy tupperware.
Below are some other characteristics which might be turned off by default:
openrowset / opendatasource Permits SQL Server to query an external datasource without having to define a “connected server”
CLR – Common Runtime Language This is among the largest selling features for SQL 2005 which lets you code SQL Financial Accounting procedures offering code from any of the .NET programs
Other characteristics
User accounts that want to perform mass inserts must first be assigned the job of “BulkAdmin”.
You should also not be unaware of how security when mass adding information is handled by SQL 2005. To the file, the user must have access in SQL Server 2005.
We experienced an extremely frustrating and hard to solve trouble when we attempted to perform mass inserts.
This is more generally called the “Two Network Hop” issue. SQL attempts to catch the file from a network share using our certificate (hop 2). The primary domain controller reacts saying “I do not understand what you’re attempting to do”.
The LAN management team who was helping us solve the issue were perplexed regarding why we were having trouble, when they weren’t. That puzzle was solved by me once I understood the LAN system administrators were using remote desktop for connecting to the server afterward Management Studio would open on the server. SQL Server could seize the file because only one network hop was involved.
Database Email Enables the database procedures to send email messages via SMTP
These attributes are simply turned off to minimize the footprint of the SQL Server as I’ve already said. The smaller the footprint, the less code which is running that may be exposed to assault. The attributes are not neither good nor insecure. But should you n’t want them, do not’ run them.
