Today we have released an updated Community Technology Preview of Microsoft Source Code Analyzer for SQL Injection.  

We made the following improvements based on community feedback:

1. Included a GUI to view warnings generated by the tool.
2. Downgraded the requirements to Microsoft .NET Framework 2.0 from 3.0.
3. Improved the ASP parser and analysis engine in various ways.

The updated tool can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA. Please read the Readme.html file for the complete list of warnings generated by the tool along with code samples that will generate the warnings.

Please provide feedback and discuss issues related to the tool in the SQL Server Security forum at http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=92&SiteID=1

Thanks,

We’ve long recommended that customers use the Windows Firewall to protect SQL Server installations. Starting with Windows XP/SP2, and continuing with Windows Vista, the firewall has been enabled by default on Windows client operating systems. Windows Server 2008 marks the first time this protection has been extended to a Windows Server OS.

For those of you migrating from Windows Server 2003 or earlier to Windows Server 2008, if you have not previously heeded the advice to enable the firewall, you may be surprised by connectivity failures caused by the firewall (for any version of SQL) and you will need to take action to enable the connectivity you want.

Don’t panic! J Choosing the right firewall strategy isn’t as hard as it may seem, and it will pay dividends over the long run.  We have a books online entry that has lots of good information on how to use the firewall. That document is available at

http://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx. 

We strongly recommend you read that before making changes to your firewall strategy.  For detailed information about the firewall, see

http://technet.microsoft.com/en-us/network/bb545423.aspx

I use the term “firewall strategy” intentionally, because there are some tradeoffs that only you can make. You might want to simply “configure the firewall” to make it all “just work,” and you could do that, but it might expose you in ways you don’t intend. To highlight that point, I will refer you to a recent survey by David Litchfield of NGS Software. Quoting from the executive summary:

The survey found that there are approximately 368,000 Microsoft SQL Servers directly accessible on the Internet and around 124,000 Oracle database servers directly accessible on the Internet.

That’s a lot of servers directly exposed to the Internet, and I doubt strongly that level of exposure is intended. So we’re hopeful that you will spend some time making choices you are comfortable with, and that the change to enable the firewall on Windows Server 2008 will lead to a level of exposure that more closely matches your real business needs. And only you can determine your real business needs.

I should note that exposure by itself does not imply that there is any particular vulnerability. Indeed, only 4% of the exposed SQL servers were running a vulnerable version of SQL Server, and those few vulnerable servers appear to have gone unpatched for many years now (our competitors fared much worse in this regard, candidly). But in the event of a newly-discovered vulnerability in SQL Server those 368,000 servers could become 368,000 targets overnight (or faster), and we all want to avoid that.

So, our first piece of advice: review your existing firewall strategy, including your host and network firewalls, to ensure that none of your servers are unintentionally exposed to the internet or to untrustworthy insiders.

Step 0: Establish the scope of your scan, and appropriate policies. Will you be performing a simple audit of connectivity, or a full penetration test? What knowledge will the auditor have of your systems ahead of time? Who is authorized to have the results? Who is tasked with making changes based on the audit?

 ISO-27002 discusses “ guidelines and general principles for initiating, implementing, maintaining, and improving information security management in an organization” and is worth reviewing if you don’t have established policies in place. Additional resources include

·         The CERT OCTAVE Method

·         The Open Source Security Testing Methodology

·         The Center for Internet Security

·         The Information Systems Audit and Control Association IS Auditing Procedure

 Doing an audit carelessly can be costly and embarrassing. Review the resources above, and plan well.

Step 1: Locate your SQL Servers. Not only are SQL Servers in the datacenter serving enterprise-wide functions, but SQL Servers power desktop and department-wide applications outside the data center as well. And SQL Server comes in free editions like SQL Server Express, and SQL Server Express Advanced, which end users can download and install on their desktops, and which are bundled in other products like Visual Studio.

You can use SQL Browser to search for SQL Servers from different places on your network. Because Browser broadcasts to other instances of Browser in order to locate SQL Servers, there are some limitations.

·         If the Browser service on a remote machine is not running, or is blocked by a firewall, this method won’t work of course

·         If the firewall is enabled on the machine from which the scan is performed, that may interfere with gathering complete results. If a server takes more than 3 seconds to respond, that response may be blocked by the client-side firewall. See the UnicastResponsesToMulticastBroadcastDisabled property of the Windows firewall for more information.

·         Because SQL Browser uses broadcasts, it can only “see” SQL servers in the same broadcast domain. So, you may have to do this from different places in your network.

·         On clustered systems, be aware that browser is not a clustered resource, and does not support failover from one cluster node to the other.

For more information on Browser, see http://msdn.microsoft.com/en-us/library/ms181087.aspx.

Beyond using SQL Browser, you can perform a network scan looking for services listening on default ports for any of the SQL components. These default ports are

This is just the default ports for the most common scenarios, not an exhaustive list of possibilities. For more detail, see http://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx.

Step 2: review the host firewall configuration to ensure it complies with your policy. For the Windows Firewall with Advanced Security, the basic checks are to ensure that there are appropriately configured exceptions for the services you intend to offer, and otherwise, ports in the firewall are blocked. In today’s highly networked world, with complex schemes to embed one protocol within another, and advanced scanning techniques designed to penetrate network firewalls, a host firewall is more important than ever.

Review the documents below for detailed information on using the firewall with various components of SQL:

Beyond just a simple “port open” or “port closed” decision, you should consider at least the network scope (all systems, local subnet, or a specific list of IP addresses), whether or not IP Sec is enabled, and what profile you are configuring. Review “Configuring the Windows Firewall to Allow SQL Server Access” for more information.

Step 3: Review your network firewall configuration to ensure that access to your network is correctly configured. Here, configuration choices vary widely and there are many makers of network firewall systems, including the Microsoft Internet Security and Acceleration (ISA) Server.

The ISA team has provided online training to help you design a perimeter firewall defense, covering the various classes of network firewalls, security, scalability, performance, and other topics. That material is available at

http://www.microsoft.com/technet/security/learning/perimeterdefense/all/default.mspx.

Step 4: Verify that your defenses are operating as expected. This may be the most difficult part because the job of the network is to deliver packets, and it may succeed despite your best efforts to stop it.

The basic idea is to attempt to establish connectivity to SQL Server from a location from which you believe the server is not accessible. Even if you have a machine that is providing a service to the whole world by design, it’s unlikely that you want to provide all services available on that machine to all comers. So make sure that you are checking for all the services the machine might offer.

It can help here to draw your network as a set of zones, and enumerate the connectivity policy within and between zones. A zone might be the “demilitarized zone” which traditionally provides a buffer between your public and private networks, or it may be “the sales zone,” where access is permitted to sales data.  “The sales zone” does not consist of a list of authorized sales people, but rather a list of networks from which those sales people need access (after providing appropriate credentials of course).

As part of that process, you should identify zones from which a certain type of access is not permitted, and conduct a scan from that zone. This should fail, of course. It’s possible that all the networks you own are designed to permit connectivity to your database, and if that is the case, it means you will have to perform the scan from outside your network.  A few things to keep in mind if you need to do that:

1.       Don’t surprise the lawyers: be sure you have permission from the owners of both networks to perform such a scan.

2.       Don’t surprise the network team: a scan can be resource intensive, and risky. Don’t do it without the approval of your network team.

3.       Don’t surprise the security guys: be sure your own security team, and the security team of the network you are using, is aware of and prepared for the scan. When you set somebody’s pager off in the middle of the night because they think your network is under attack, we call that “an unfortunate event on your annual review.”

There are a variety of network scanners available to you to perform this scan. The Microsoft utility PortQry can help you do simple scans. The PortQry utility is available for download from the Microsoft Download Center.  You may wish to consider third party tools like nmap, SuperScanner, Nesus, and Foundstone Enterprise for large scanning needs.

We have included some advice from our Application Consulting and Engineering  team specifically about scanning:

·         Use a moderate scanning speed when conducting UDP scans on the network, especially across WAN links; hosts that do not have a service on the port to which you are trying to connect will respond with “ICMP Port Unreachable”, and there have been instances where this can cause a denial of service  on some routers

·         Most scanning engines support tuning paramaters; try to break the network up logically and scan in smaller chunks to reduce possible outages and negative impact

·         Use things like NetBIOS domain browse lists whenever possible to reduce the generation of traffic and “touching hosts”

·          

If you’re conducting a complete network penetration test at the same time, things can get more complicated, but we’re not going to cover all of that here.   Review the documents above, and plan for your pen test.

Step 5: Remedy the problems.  No one technology may provide all the filtering capability you need. You may have to make tradeoffs and use a combination of network and host-based firewalls to achieve the protection you want.

-Shawn Hernan, SQL SIA Security, with much input from many people.

This posting is provided "AS IS" with no warranties, and confers no rights.

Two days ago, we released Microsoft ® Source Code Analyzer for SQL Injection, June 2008 CTP which can analyze SQL injection vulnerabilities in Active Server Pages (ASP) code. In this blog, we will describe simple steps to help you start using the tool quickly.

1. Download the tool from http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA. Msscasi_asp_pkg.exe is a self extracting binary that copies the tool binaries in a specified folder.

2. Please install Microsoft .NET Framework 3.0 before using the tool.

3. This is a command line utility, so launch a command window and go to the directory that contains the tool. The tool comes with six switches (documented in the readme file), you can just use the /Input=[fullpathtoaspfile] switch to analyze a particular ASP page. If you have ASP pages that include files from virtual directories then you need to use the /IncludePaths switch to provide absolute paths to the include files. Similarly if you have global.asa file, you can use the /GlobalAsaPath switch.

4. Reviewing the output messages

• If the tool finds any potential problems in an ASP page then it generates one of the six warnings: 80400, 80403, 80406, 80407, 80420 or 80421. The 80400 warnings indicate high-confidence first-order  SQL Injection vulnerabilities and are most likely bugs that should be addressed immediately. Please read the documentation (readme.html) for more information on how to triage the other warnings.
• If you see no output then the tool has successfully analyzed the file and didn’t find any potential issues. If you believe the tool missed bug that it ought to find then please inform us in the SQL Security MSDN forum.
• We developed a new ASP parser as part of the tool development, so it is possible that we may not be able to parse all ASP constructs properly. Again, please report any issues in the SQL Security MSDN forum and we will try to address them in our next release.
• You might see some errors on “cannot find the file [virtualdir]\include.inc”. The tool cannot currently resolve virtual directories. Please use the /IncludePaths switch to provide absolute paths for the include files so that the tool can successfully analyze the ASP web pages.

5. Scanning the entire directory.

The tool analyzes one ASP file at a time. You can use the following VBScript code to process an entire folder containing ASP web pages.

ON ERROR RESUME NEXT

If WScript.Arguments.Count = 0 Then

   WScript.Echo "Usage: " + WScript.ScriptName + " sourcedirectory"

   WScript.Quit(0)

End If

ProcessFolder WScript.Arguments(0)

Sub ProcessFolder(ByVal folderspec)

   Dim fso, f, f1, fc, s, sf

   Dim strInputFile

   Set fso = CreateObject("Scripting.FileSystemObject")

   Set f = fso.GetFolder(folderspec)

   Set fc = f.Files

   For Each f1 in fc

        If StrComp(LCase(Mid (f1,Len(f1)-3,4)), ".asp") = 0 Then

            strInputFile = f1.Path 'f.Path + "\" + f1

            ASPScan (strInputFile)

        End If

   Next

   Set sf = f.SubFolders

   For Each f2 in sf

          ProcessFolder f2.Path

   Next

End Sub

Sub ASPScan (ByVal strInputFile)

    ON ERROR RESUME NEXT

    Err.Clear

      Dim WshShell, oExec

      Dim strCommand

      Dim sTime, strBinary

      GenerateSQLInjectionFile = true

      Set WshShell = CreateObject("WScript.Shell")

    strBinary = GetShortFolderName (GetScriptPath()) + "\" + GetShortFileName ("msscasi_asp.exe")

    strCommand = "cmd.exe /c " + strBinary + " /input=""" + strInputFile + """ /Nologo >>" + GetShortFolderName (GetScriptPath()) + "\output.txt"

      Set oExec = WshShell.Exec(strCommand)

    sTime = Now  

      Do While (oExec.Status = 0)

             WScript.Sleep 1000

      Loop

      Set oExec = Nothing

      Set WshShell = Nothing

End Sub

Function GetScriptPath ()

    Dim strPath

    strPath = WScript.ScriptFullName

    strPath = Mid (strPath, 1, InstrRev(strPath,"\")-1)

    GetScriptPath = strPath

End Function

Function GetShortFolderName(ByVal filespec)

   Dim fso, f, s

   Set fso = CreateObject("Scripting.FileSystemObject")

   Set f = fso.GetFolder(filespec)

   GetShortFolderName = f.ShortPath

End Function

Function GetShortFileName(ByVal filespec)

   Dim fso, f, s

   Set fso = CreateObject("Scripting.FileSystemObject")

   Set f = fso.GetFile(filespec)

   GetShortFileName = f.ShortName

End Function

Create a VBScript file (.vbs) with the above content, place it in the folder where the tool is located and execute the script providing absolute path of the folder containing ASP code. The script will generate the file output.txt with the concatenated tool output in the folder where the tool and script files are located. Please modify the script according to your needs, for example, if your ASP code uses virtual file includes or if you have a global.asa then you will need to pass /IncludePaths and /GlobalAsaPaths parameters to the tool in ASPScan function.

6. Annotating the code – Annotations are pretty simple. If you have any generic input validation routines, then annotating those functions with ' @@embed __sql_validate(paramname) within the function body will eliminate false positives with 80406, 80407 and 80421, remember to replace paramname with the function parameter that is being validated. Similarly if you have functions that are called from various places and have 80420 or 80421s warnings then annotating those functions with ' @@embed __sql_pre_validated(paramname) can give you accurate information on the vulnerable code paths.

7. Follow the code path – All the vulnerable code paths have the same characteristics: End User controlled data is used in the SQL statement construction. The information provided in the code path is verbose, but you can simply look at the line numbers to see if any user controlled data is executed as part of a SQL statement.

8. Fixing the issues – Using parameterized SQL is the best solution to mitigate SQL Injection issues. The Readme documentation contains sample code for parameterized queries. The above steps will help you use most of the capabilities of the tool, which are described further in the documentation.

We are interested to know what has worked for you and what has not. Please provide us feedback in the MSDN forum to help us improve the tool.

Thank You

This posting is provided "AS IS" with no warranties, and confers no rights.

Today Microsoft has released a Community Technology Preview of a new source code analyzer that can help ASP developers find SQL Injection vulnerabilities in their code.

Three weeks ago Microsoft released guidance (http://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx) on protecting ASP and ASP.NET web sites against SQL injection attacks. At the same time, Microsoft took an action item to develop new tools that could help web developers find these SQL injection vulnerabilities automatically. Microsoft Source Code Analyzer for SQL Injection is one of the tools developed as part of this effort.  It is a static dataflow analysis tool to help find SQL Injection vulnerabilities in Active Server Pages (ASP) code. In particular, the tool attempts to find the vulnerabilities outlined in the guidance article “Preventing SQL Injections in ASP” (http://msdn.microsoft.com/en-us/library/cc676512.aspx) published three weeks ago.

The tool can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA. Please read the Readme.html file for the complete list of warnings generated by the tool along with code samples that will generate the warnings. The documentation also discusses warning mitigation.

Please provide feedback and discuss issues related to the tool in SQL Server Security forum at http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=92&SiteID=1

Thanks,

The Microsoft Source Code Analyzer for SQL Injection Team

(Bala Neerumalla, Henning Rohde and Avi Gavlovski)

This posting is provided "AS IS" with no warranties, and confers no rights.

  We have received some feedback regarding the “SQL Server 2005 Encryption – Encryption and data length limitations” article, but unfortunately the owner of this blog is no longer a member of our team and we really don’t have access to it in order to answer to your feedback properly.

  I would like to invite anyone who would like to contact us to give us feedback or ask any question regarding this article to use this page instead of the original article.

  Thanks a lot,

-Raul Garcia

  SDE/T

xp_cmdshell is essentially a mechanism to execute arbitrary calls into the system using either the SQL Server context (i.e. the Windows account used to start the service) or a proxy account that can be configured to execute xp_cmdshell using different credentials. Because of its nature, xp_cmdshell is very flexible, actually I would say it is too flexible as it allows users to execute any arbitrary command using the system (or proxy) context without any good way to limit this flexibility, pretty much opening the door for abusing it.

    In many cases, people enable xp_cmdshell and grant access to it to non-sysadmin principals in order to perform one or two operations on the system without realizing that the user with access to it can execute any arbitrary command, and in some cases, effectively escalate his/her privileges to sysadmin or even box administrator - obviously a situation that is less than desirable.  Xp_cmdshell is really difficult to control effectively, and even auditing its usage may still allow the attacker to abuse its power for some time until the trail of this abuse is found, and at that point the damage may already be done.

  Generally speaking, you must avoid using xp_cmdshell, and if possible, you should remove any dependency on it.  A good alternative may be to use CLR and create an external access/unsafe assembly (preferably using digital signatures to establish the trust) that executes exclusively the operation needed and under the complete control of the application developer. Among the advantages are:

·         Besides the external access/unsafe trust mechanism (i.e. via signatures), there is no dependency on server settings

·         Sysadmin can revoke this trust relationship as needed without affecting other applications

·         Better granularity and isolation as the non-SQL operation is well defined by the application

  But remember that CLR is not a silver bullet, given the level of trust you may be giving the application (perform certain operations on behalf of SQL Server service account or another privileged user in the OS), you need to make sure that the application is well written and doesn’t have security vulnerabilities (such as code injection, buffer overflows, etc.) and that it is well scoped (i.e. it doesn’t allow execution of arbitrary commands or opening arbitrary files in the system, etc).

  If CLR is not an option because as a DBA you don’t want to enable CLR on the system then another option would be to create a T-SQL stored procedure with the command that needs to be invoked on the OS and use EXECUTE AS or digital signatures as a mechanism to temporary escalate the caller to a sysadmin member. When using this approach please be sure to avoid any user-controlled input (such as using parameters for the command to be executed); or if you need to programmatically create the command, make sure to validate and escape the input properly.

 I am including a few links below as references on SQL CLR and digital signatures. I hope this information will be useful.

·         CREATE ASSEMBLY (T-SQL)

·         Database Engine .Net Framework programming

·         CLR programmability Samples

·         SQL Server 2005 CLR integration blog

·         Laurentiu Cristofor’s blog

o   SQL Server 2005: procedure signing demo