SQL Server 2005 - A New Approach to Security

I was tested as of late to clarify why Microsoft would have certain highlights, for example, xp_cmdshell and openrowset handicapped as a matter of course if that component was not loaded with security openings. The jist of the discussion was that these highlights must contain genuine security gaps, generally Microsoft would not have handicapped them. Empowering thusly would be out and out careless. This article was composed to clear up a portion of the perplexity encompassing the "little impression" way to deal with security.


I have been a SQL Server DBA since 1996 and have viewed Microsoft's item develop throughout the years. I think it is sheltered to state that Microsoft didn't "get" security at an early stage. That being stated, Microsoft has placed a huge measure of exertion into reexamining security for SQL Server 2005.

Microsoft's new disposition towards security: "We will reevaluate our way to deal with security. We will inspect our code for vulnerabilities. We will discharge fixes as required. We will mood killer most highlights of course so as to keep the impression little. On the off chance that you need something, turn it on. Yet, on the off chance that you needn't bother with it, leave it off. That way if a powerlessness is found in an item you are not utilizing, you won't be influenced."

A few people guarantee that a major security opening in SQL Server 2000 was the capacity to issue direction shell directions utilizing xp_cmdshell. I would contend that the greater part of the unmitigated security "gaps" were a consequence of a mix of poor plan and human mistake. In particular:


a) the default secret key for the framework manager account (sa) was clear

b) the administration account that SQL Server was arranged to utilize was unreasonably regularly an area executive record in this manner heightening the benefits of any record that utilized xp_cmdshell

c) Microsoft neglected to recognize vulnerabilities in their source code and took too long to even think about releasing patches once the vulnerabilities were distinguished

d) DBA's neglected to keep the most recent patches applied along these lines leaving their servers defenseless against known endeavors

A decent contextual investigation with respect to SQL 2000's vulnerabilities is the Sasser infection (mid 2001 I accept). This infection spread quickly via looking out unpatched SQL Server's. There were an amazing measure of these servers uncovered on the web, including SQL Server's controlled by money related establishments. Sasser did a ton of harm. Sasser did this by executing remote technique approaches unpatched servers. You could state that running remote system cancels ought to be turned, yet then you would be viably be disposing of the entire "Customer/Server" come closer from advanced registering.

The answer for Sasser style assaults is obviously is two-overlap. Microsoft must be resolved to chase down vulnerabilities in their code and discharge fixes in a convenient manner, and we as DBA's must be careful in applying those patches.

With regards to SQL 20005 and highlights like xp_cmdshell, we need to recollect that Microsoft assembled these highlights to enable database engineer to create rich applications equipped for performing complex handling assignments. It isn't Microsoft's goal that individuals keep these highlights killed . Or maybe, the manager (DBA) needs to endeavor to turn the highlights on the off chance that they are required. Keep your application impression little and decrease your presentation to assault.


You could state that the most secure SQL Server is one that is killed. That would not be extremely valuable to anybody, and someone could in any case leave with a reinforcement of the database. They could even just yank out the hard drives and put them in pack like yesterday's filthy tupperware.

Document System Objects (FSO) as I would like to think is an incredible option in contrast to utilizing the xp_cmdshell if all you are doing is controlling records. Microsoft made FSO so ALL its scripting dialects (C#, Visual Basic, T-SQL, and so forth) can approach record framework objects without being able to execute discretionary directions.

Here are some different highlights that are killed as a matter of course:

openrowset/opendatasource Allows SQL Server to question an outside datasource without characterizing a "connected server"

CLR - Common Runtime Language This is one of the greatest selling highlights for SQL 2005 which enables you to code SQL methodology that incorporate code from any of the .NET applications

Database Mail Allows the database procedures to send email messages through SMTP

As I have just expressed, these highlights are just killed to limit the impression of the SQL Server. The littler the impression, the less code that is running that might be defenseless against assault. The highlights are neither awful nor unreliable. Be that as it may, in the event that you needn't bother with them, don't' run them.

Different highlights

Mass Insert is a case of an element that isn't "killed" as a matter of course, however you need extraordinary authorization to utilize. Client accounts that desire to perform mass supplements should initially be relegated the job of "BulkAdmin".


You should likewise know about how SQL 2005 handles security when mass embeddings information. In 2000, the record that the SQL Server administration record utilized needed to have authorization to get to the document. In SQL Server 2005, the client giving the direction must approach the document.

We encountered an exceptionally baffling and hard to take care of issue when we attempted to perform mass embeds over a system.

The issue that we experienced is that the present system design isn't "Kerberos Protocol 5" consistent, which counteracts SQL Server from passing on the accreditations of the signed in client. This is all the more generally alluded to as the "Two Network Hop" issue. We sign in to SQL to give an order (jump 1). SQL attempts to snatch the document from a system offer utilizing our qualifications (bounce 2). The essential area controller reacts saying "I don't have the foggiest idea what you are attempting to do".

The LAN organization group who was helping us take care of the issue were confounded concerning why we were experiencing issues, when they were most certainly not. I comprehended that puzzle when I understood that the LAN chairmen were utilizing remote work area to associate with the server then they would open Management Studio legitimately on the server to give the Bulk supplement direction. In every practical sense they were signing into the SQL Server locally (no system bounce included). SQL Server had the option to snatch the document over the system on the grounds that just one system jump was included.

In rundown, utilize the highlights you need, turn off the highlights you don't.

Update: Microsoft Responds!

A couple of days subsequent to posting this article, I got a disturbing email from a Microsoft Engineer who expressed that xp_cmdshell is to be sure hazardous and ought to be de-initiated. After a concise email trade, he explained his position. Things being what they are, he wound up concurring with me that xp_cmdshell is sheltered on the off chance that you design your frameworks suitably.