Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

According to a number of researchers at the University of California at Berkley, on January 25, 2003, at 05:30 UTC, the now notorious "Slammer" worm exploited a buffer overflow on systems running SQL Server or MSDE 2000. Even though Microsoft had identified and provided a patch for the problem in July 2002, over 75,000 SQL Server systems were infected^[1], and, in many cases, their data was destroyed. Ever since the Slammer worm hit, Microsoft has spent considerable time and energy working on ways to prevent attacks of all kinds. While SQL Server 2005 is shielded from the Slammer worm and a host of other threats, one additional issue Microsoft's developers addressed in the current versions was SQL Server's 2000's inability to be easily patched when new vulnerabilities arise.

^[1] www.caida.org/publications/papers/2003/sapphire/sapphire.html. "The Spread of the Sapphire/Slammer Worm."

Tip

All versions of SQL Server 2005 now have the ability to be patched from the webjust like Microsoft Office applications and Windows itselfeven the versions you install on the user's desktop.

Slammer took advantage of SQL Server's default setup configuration that exposes port 1433 to the network, as well as exploiting a buffer overflow vulnerability that had been left behind in unpatched systems. Add to that the tendency of Systems Administrators (at least, who that don't care enough about security) to install older versions SQL Server with a blank SA password, and you have a formula for disaster, regardless of any buffer overflow patches that Microsoft inadvertently left behind. Since SQL Server 2000 SP3, SQL Server's default configuration hides itself behind a wall of protection schemes, and it encourages use of strong SA passwords (but they still aren't requiredeven in SQL Server 2005). When you configure SQL Server's protection schemes to permit external connections, you must disable some of these protection mechanismshopefully, not all of them. I recommend that you expose just enough "surface area" to permit your users to access the systemand no more.

IMHO

Exposing SQL Server to your network as opposed to simply exposing it to the local machine significantly increases the surface area of attack for those with malicious intent.

Choosing a Connection Strategy

Before you get started, you need to make sure that your SQL Server is prepared to accept connections. Consider that SQL Server can be installed in a number of ways and on a number of Windows platforms. Each type of installation uses a different mechanism to protect itself from the outside world. Your code won't be able to connect to these servers (at least, not from remote systems) unless you reconfigure SQL Server to expose its ports, enable its protocols, and (most importantly) poke a hole in your firewall(s) to expose the SQL Server ports and protocols to the net. No, that does not mean you need to expose your server to the entire intranet or to the web.

As you start working with Visual Studio, you might not encounter an ADO.NET connection string at all or have to worry about what it contains. That's because Visual Studio can construct one for you, as I'll discuss later in this chapter. However, when something goes wrong (and it usually does), knowing what the ConnectionString contains and the options it's using to connect can be a valuable tool when debugging your application's ability to get connected. The new Data Sources dialog in Visual Studio does just thatit builds a ConnectionString and an ADO.NET Connection object to match the provider you specify, along with a host of other support objects based on the database objects you associate with the DataSource. But I'm getting ahead of myself. I'll discuss the Data Source dialog and all of its options later in this chapter and subsequent chapters.

There are three types of connection strategiesevery version of SQL Server can be configured to support these strategies. No, not all versions of Visual Studio support all of these strategiesI'll show you which are and which aren't supported. Note that each SQL Server instance can be configured independently to expose or hide itself from the network. By default, SQL Server is installed with all of the protocols and ports needed to connect to external clients disabled. However, even when ports and protocols are enabled, that's not enough to let you connect to SQL Server. You'll also need to have proper credentials and be able to penetrate your system's firewall if you want to access an intranet or web server instance. Let's look more closely at the various connection strategies.

By default, SQL Server is hidden from the intranet (your network) and the Internet (the entire world). You'll have to reconfigure it after installation to make it visibleif you dare.

• Local access: When your SQL Server is installed on the same system as your Windows or web-based application accessing SQL Server, you don't need to do much reconfiguring to connect once SQL Server is started. Typically, a SQLExpress instance runs on the same system as the application (but it, too, can be exposed to the network). When connecting to any locally running instance of SQL Server, ADO.NET can use the "shared memory" provider that bypasses the network protocols, and your server does not have to expose the network ports to permit connections. When accessing a local SQL Server instance, there is no need to disable or alter the Windows XP or your own firewallit can keep blocking access to the system's (and SQL Server's) ports.

• Intranet access: When you want to expose SQL Server to clients on an intranet (a local area network, or LAN), you have several more hurdles to jump before a SQL Server instance is going to permit connections. In this case, it's best to use the SQL Server Surface Area Configuration tool to make sure your SQL Server is properly configured. This configuration is used for client/server or ASP installations where the server does not reside on the same server as IIS. To access a networked SQL Server, you'll need to enable one of the external protocols (shared memory won't help for external connections), as well as ensure that the correct TCP/UDP ports are visible through the firewall.

• Internet (web) access: If you must, you can expose your TCP ports to the Internet. This will permit anyone (anywhere in the world) to snoop the ports to "see" your server and attempt to penetrate your only remaining shieldthe password on the administrator's account. While I don't recommend this approach, I'll show you how to open a connection to a SQL Server via its public (or private) IP address and port. Just don't come crying to me when your database is compromised by some guy in Whoknowswhereistan.