ptg
274
CHAPTER 10 Client Installation and Configuration
NOTE
By default, all network protocols except for VIA are enabled on the client during
installation.
That’s all there is to installing SNAC!
Redistributing SNAC with Custom Client Applications
If you build an application that relies on SNAC, you need to be aware that it can be redis-
tributed in two ways:
. As part of any SQL Server 2008 installation or upgrade
. As a custom application installation dependency
When you are building MSI files for an application, it is important that you register
sqlncli.msi as a package dependency (and, of course, to install it as well, if it is not
present on the destination machine). This helps ensure that SNAC will not be accidentally
uninstalled from the destination machine without first flashing a warning to users, indi-
cating that any application that relies on it will break. To do this, you execute the follow-
ing command early in your application’s installation process:
msiexec /i sqlncli.msi APPGUID={unique identifier for your product}
NOTE
The program name for SNAC found in the Add or Remove Programs Control Panel
applet is Microsoft SQL Server 2008 Native Client, not SQL Native Client, as it is com-
monly known.
Client Configuration
Client configuration is a many-leveled beast, consisting of operating system tasks such as
installing protocols, application tasks such as choosing or coding to a specific Application
Programming Interface (API), provider, or driver, and maintenance tasks such as configur-
ing network settings, building connection strings, and so on. The following sections cover
a broad range of these tasks, focusing on the most common. Many examples utilize
TCP/IP both because it is the default protocol for remote clients and because it is the most
widely used.

No chapter can cover all the possible ways of connecting, but this one is designed to give
you the tools you need to get set up right from the start and to navigate your way in case
specific issues arise.
The first client configuration tool we look at is SSCM.
Download from www.wowebook.com
ptg
275
Client Configuration
10
Client Configuration Using SSCM
The Client Network Utility available prior to SQL Server 2005 has been decommissioned,
and all its functionality is now built into SSCM. This includes the capability to create
server aliases, to enable and prioritize network protocols, to control the various SQL Server
services, and more.
NOTE
One thing Microsoft is keen on including in Books Online is that neither Setup nor
sqlncli.msi installs the actual network protocols themselves, nor do they enable
them at the operating system level. This means that if you do not have TCP/IP
installed and you need to start using it, you have to first set it up by using the Network
Connections Control Panel applet (if you’re using Windows, that is).
You can launch SSCM directly from its Start menu icon, or you can access it in the
Services and Applications node of the Computer Management console. When you
have SSCM up and running, to access its client-side functionality, you expand its top-level
node (SQL Server Configuration Manager (servername)) and then you click the SQL
Native Client 10.0 Configuration node. Below it, you click the Client Protocols node
to reveal the enabled state and priority order of each protocol, in grid format, in the right
pane (see Figure 10.4).
From this screen, you can right-click any of the protocols to change their enabled state,
view Properties pages, or change the default connection order (except that of shared
memory, which is always tried first and whose order cannot be changed). The following is

the default connection order for clients connecting without the benefit of a server alias,
connection string, or other means:
FIGURE 10.4 SSCM’s Client Protocols screen.
Download from www.wowebook.com
ptg
276
CHAPTER 10 Client Installation and Configuration
FIGURE 10.5 The TCP/IP Properties screen.
. Shared memory
. TCP/IP
. Named pipes
(As the grid shows, VIA is disabled by default.) When you are connecting remotely, TCP/IP
is the first protocol attempted because shared memory is local only.
NOTE
When a client does not specify a connection protocol, SNAC automatically tries each
protocol in the list in sequence, according to the Order column. The first protocol to
connect successfully wins.
If the winning connection is subsequently rejected by the server for any reason, no
other protocols are tried.
Note also that local clients using MDAC 2.8 or lower cannot connect using shared
memory, and they are automatically switched to named pipes if they attempt to do so.
Let’s examine one of the protocols. To start, you need to double-click TCP/IP under the
Name column to open the TCP/IP Properties screen (see Figure 10.5).
The values stored here are used by TCP/IP clients as default connection values, and they are
applied only when a specific server alias or other configuration mechanism is not in use.
They are also used by the SQL Server 2008 client tools when shared memory is not available.
Download from www.wowebook.com
ptg
277
Client Configuration

10
FIGURE 10.6 Alias properties for a new named pipe server alias.
As you can see, the default port, 1433, is set up to connect to the more commonly config-
ured default instances of SQL Server. By editing the values on this page, you can change
the default port number, enabled state, keep-alive values, and other settings (when
editing other protocols). You should edit and enable the protocols according to your
specific needs.
Server Aliases
A server alias is a name that is used like a server name that represents a group of server
settings for use by connecting clients. Server aliases are very handy because of the way
they simplify connection parameters: clients need only specify the alias name, and SNAC
pulls the rest of the information (such as the IP address, TCP port number, and pipe
name) from SSCM at connection time.
To create a server alias, you right-click the
Aliases node under SQL Native Client
Configuration and choose New Alias. On the Alias - New screen that appears (see Figure
10.6), you specify the alias name, protocol (except shared memory, for which you cannot
create an alias), and server name. (local, ., and localhost also work for local connections
over TCP/IP or named pipes.)
When you make your protocol selection, the grid rows change to dynamically reveal the
settings particular to that protocol. When you are finished, you click OK, and your alias is
ready for use.
Download from www.wowebook.com
ptg
278
CHAPTER 10 Client Installation and Configuration
FIGURE 10.7 Importing a certificate on the client computer using the Certificate Import
Wizard.
Connection Encryption
With SQL Server 2008, it is easy to set up Secure Sockets Layer (SSL) encrypted

client/server communication over all protocols. The SNAC net-library handles the tasks of
encryption and decryption on both the server and client ends. (Note that this process
does cause a slight decrease in performance.) Setting it up requires both server-side and
client-side configuration changes; this section covers only the client-side changes in detail.
SQL Server 2008 enables encryption using two types of certificates:
. Certificates generated by and obtained from an external certification authority such
as VeriSign
. Certificates generated by SQL Server 2008 (known as self-signed certificates)
The bit strength of the encryption (40-bit or 128-bit) depends on the bit strength of the
operating systems of the computers involved in the connection.
To set up the server for encryption, your administrator registers a certificate on the server
operating system (using the Certificates Management console) and then installs it in the
Database Engine.
If an externally signed certificate is not installed on the server, SQL Server uses its built-in
self-signed certificate. (A server administrator may also create and save a self-signed certifi-
cate by using SQL Server 2008 via the new CREATE CERTIFICATE and BACKUP CERTIFICATE
T-SQL syntax.) It is also up to the server to decide whether encryption is required or
optional for connecting clients.
The client’s half of the job is to have installed what is known as a root-level certificate that
is issued by the same certification authority as the server’s certificate. To install a root-level
certificate, you right-click the certificate itself (a .cer or .crt file) and select Install
Certificate to launch the Certificate Import Wizard. You click Next on the welcome screen
to reach the Certificate Store screen (see Figure 10.7). Then you select the first radio button
(Automatically Select the Certificate Store) and then click Next. Finally, you click Finish.
Download from www.wowebook.com
ptg
279
Client Data Access Technologies
10
FIGURE 10.8 Forcing clients to request an encrypted connection using SSCM.

Next, you launch SSCM, right-click the SQL Native Client 10.0 Configuration node,
and then select Properties. The Flags tab appears (see Figure 10.8) in the Properties window.
You set the Force Protocol Encryption property value to Yes. This causes clients to
request an SSL-encrypted connection when communicating with the Database Engine. If
the server does not respond in kind, the connection is killed.
The Trust Server Certificate property gives clients a choice in how they deal with
server certificates:
. To use a self-signed certificate, you set the property value to Yes. This option
prevents SNAC from validating the server’s certificate.
. To use an externally signed certificate, you set the property value to No, which causes
SNAC to validate the server’s certificate.
SSMS can also connect over an encrypted connection. When connecting using the
Connect to Server dialog, you click the Options button and then click the Connection
Properties tab. Then you choose your database and protocol and, at the bottom left, check
the Encrypt Connection check box.
Client Data Access Technologies
The question of which data access technology to use with SQL Server 2008 is a common
one, with a seemingly easy answer: you use SNAC because it has all the latest and greatest
functionality, all rolled into one. (You learn how to use SNAC in the sections that follow.)
Download from www.wowebook.com
ptg
280
CHAPTER 10 Client Installation and Configuration
A more correct answer is that your choice depends on which software technologies your
clients currently use and what their specific needs are.
Your data access options consist of providers and drivers, whose functionality is often
encapsulated inside code libraries known as net-libraries (such as SNAC’s sqlncli10.dll).
In addition to these net-libraries, supporting services such as MDAC’s OLE DB Core
Services are also available, providing useful functionality not found in the net-libraries,
such as connection pooling. (ADO.NET also functions as a service, to a certain degree.)

NOTE
The Microsoft Data Access Components (MDAC) has a new name that started with the
Vista operating system. The data access components are now called Windows Data
Access Components or Windows DAC or WDAC. References to MDAC in this chapter
also apply to the Windows DAC.
Provider Choices
A provider is software used for accessing various data stores in a consistent manner
conforming to a specification, such as OLE DB. A provider may contain an API. Clients
that use providers are known as consumers. SMSS and SQLCMD, for example, are
consumers of the SNAC OLE DB provider.
You can choose from the following providers:
. SQL Native Client OLE DB provider—This is the latest OLE DB provider, and it is
built into SNAC; it is also known as SQLNCLI. COM applications might want to
switch to this provider to access the latest functionality; doing so also provides
access to SQL Server 7 and 2000 databases.
. .NET Framework data provider for SQL Server—This data provider is built in to
the System.Data.SqlClient namespace in the .NET Framework. Managed code
applications should use it to access the latest SQL Server 2008 functionality from
.NET 3.5 applications. .NET 1.0, 1.1, and 2.0 applications do not have access to all
the latest SQL Server 2008 functionality through this provider.
. Microsoft OLE DB provider for SQL Server—This OLE DB provider, known as
SQLOLEDB, is specialized for accessing SQL Server data and is distributed with MDAC.
COM applications may continue to use it to access SQL Server 2008, or they can
switch to SQLNCLI for the latest functionality.
. Microsoft OLE DB provider for ODBC—This deprecated OLE DB provider, known
as MSDASQL, is distributed with MDAC. ADO applications can continue to use it to
access SQL Server 2008, but SQL Server does not support the latest SNAC-specific
OLE DB functionality.
Microsoft has also made available a few implementation-specific OLE DB providers, such
as the OLE DB provider for DB2, a COM component for integrating IBM DB2 and SQL

Server 2008 data.
Download from www.wowebook.com
ptg
281
Client Data Access Technologies
10
Driver Choices
A driver in this context can be defined as software that conforms to a standard such as
Open Database Connectivity (ODBC) and provides an API for accessing a specific type of
data store. osql.exe is a good example of an application that uses an ODBC driver (the
SNAC driver).
These are the available drivers:
. SQL Native Client ODBC driver—This is the latest ODBC driver, and it is built
into SNAC. COM applications might want to switch to this driver to access the latest
functionality.
. Microsoft ODBC driver for SQL Server—This is the ODBC driver distributed with
MDAC for accessing SQL Server databases. COM applications can continue to use it
to access SQL Server 2008, or they can switch to the SNAC ODBC driver for the
latest functionality. This driver also provides access to SQL Server 7, 2000, and 2005
databases.
. Java Database Connectivity (JDBC) driver—The JDBC driver was built
specifically for accessing SQL Server data from Java code.
CAUTION
Although it is still possible to connect to SQL Server 2008 by using DB-library and
Embedded SQL, Microsoft has deprecated them both, and they will not be supported in
future editions.
Connecting Using the Various Providers and Drivers
Now that you know what your options are in terms of providers and drivers, the following
sections detail them one by one, with a special focus on putting the features in SQL Server
2008 to work.

Using SNAC
SNAC is a net-library that contains both the latest OLE DB provider and ODBC driver for
using the rich features in SQL Server 2008 databases. It is compatible for accessing SQL
Server 7, 2000, and 2005 databases as well.
The code for SNAC is contained in the single dynamic link library sqlncli10.dll, and it
serves as provider, driver, and API for applications that call its underlying COM functions
from unmanaged code (that is, from C or C++).
The bottom line with SNAC is that if you’re building applications that need to exploit the
latest features of SQL Server 2008, you need to use its APIs. If you don’t, your application
will continue to work without SNAC, but those new features will not be available.
Download from www.wowebook.com
ptg
282
CHAPTER 10 Client Installation and Configuration
FIGURE 10.9 Using the Data Sources (ODBC) tool to configure MARS with a SNAC ODBC DSN.
NOTE
A large number of connection keywords are available for use with SNAC connections. A
few of them are illustrated in the examples that follow, but for a complete reference,
see the Books Online topic “Using Connection String Keywords with SQL Native Client.”
Using OLE DB with SNAC Applications that call the COM APIs for OLE DB need to have
the connection provider value changed from SQLOLEDB to SQLNCLI10. You also need to use
the SNAC header file, as in the following example:
include “sqlncli.h”;
sqlncli.h contains the latest function prototypes and other definitions for use with
SNAC. This file is named the same as it was in SQL Server 2005, but it is installed in a
different location.
NOTE
The SNAC OLE DB provider is OLE DB version 2.0 compliant.
Using ODBC with SNAC To connect to SQL Server 2008 using ODBC, you use a connec-
tion string or a DSN that is accessible to the client application at runtime. The ODBC

driver used with SQL Server 2000 (simply called SQL Server) can still be used but is not
the best option for SQL Server 2005 or 2008. To get the latest SNAC functionality, you
must use the driver called SQL Native Client 10.0 (for example, DRIVER={SQL Native
Client 10.0}).
To create a SNAC ODBC DSN, you run the Data Sources (ODBC) applet found in your
operating system’s administrative tools. You create a system, file, or user DSN, and you
need to be sure to select the SQL Server Native Client 10.0 driver on the Create New Data
Source screen that appears. On this screen, you click the Advanced button to enter any
SNAC-specific connection string keyword-value pairs, as shown in Figure 10.9.
Download from www.wowebook.com
ptg
283
Client Data Access Technologies
10
You finish the wizard by entering the configuration data as you normally would, and you
can use you new DSN just as you would any other. For more information on building
COM applications that utilize SNAC, see the Books Online topic “Creating a SQL Native
Client ODBC Driver Application.”
Using ADO with SNAC Of course, the first recommendation is that if you’re still using
ADO, you should switch to ADO.NET if you can. If that isn’t feasible, you can still access
SQL Server 2008 from your ADO applications. But you should do so only if you need the
new features; in this case, you need to start using the SNAC OLE DB provider in your code.
To do so, you first install SNAC, and then you update your connection strings (or DSNs) to
use the new SQLNCLI value for the Provider connection string keyword. Then you set the
DataTypeCompatibility keyword to 80. Here’s an example (in Visual Basic 6 code):
Dim MyConnection As New ADODB.Connection
Dim MyFirstOpenRecordset As New ADODB.Recordset
Dim MySecondOpenRecordset As New ADODB.Recordset
Dim ConnString As String
Dim SelectResultsCount As Integer

Connstring =
“Provider=SQLNCLI; DataTypeCompatibility=80; Database=MyAppsDB;” & _
“Server=.\SQLEXPRESS; AttachDBFileName=c:\MyDBs\MyAppsDB.mdf;” & _
“MARS Connection=true; Integrated Security=SSPI;”
MyConnection.ConnectionString = ConnString
MyConnection.Open
‘ Using 2 open recordsets on one connection puts MARS to work:
Set MyFirstOpenRecordset =
MyConnection.Execute(
“SELECT TOP 10 * FROM MyTable”,
SelectResultsCount,
adCmdText
)
Set MySecondOpenRecordset =
MyConnection.Execute(“SELECT TOP 10 * FROM MySecondTable”, _
SelectResultsCount, adCmdText)
‘ and so on
Note the use of the AttachDBFileName connection string keyword, which instructs SQL
Server 2008 to attach the specified Microsoft data file (MyAppsDB.mdf).
Using the .NET Framework Data Provider for SQL Server
.NET applications that use the System.Data.SqlClient namespace rely on the .NET
Framework data provider and ADO.NET. To use this provider, you simply add the follow-
ing statement to your C# code file:
using System.Data.SqlClient;
Download from www.wowebook.com