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, 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.
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.
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.