Tools for Testing Connections

It’s always helpful to have a few tools on your belt for testing client connectivity. SSCM is a tool that is usually easily accessible, and you can use its Connect to Server dialog to select a protocol to test (as described earlier in this chapter, in the section “Client Data Access Technologies”). You can also use SQLCMD with the -S parameter to connect to a particular server. This is the syntax:its Connect to Server dialog to select a protocol to test (as described earlier in this chapter, in the section “Client Data Access Technologies”). You can also use SQLCMD with the -S parameter to connect to a particular server.

This is the syntax:

SQLCMD -Sprotocol_prefix:ServerName,PortNumber -E
 

In this syntax, protocol_prefix takes one of the following values:

  • np (for named pipes) 
  • tcp (for TCP/IP)
  • lpc (for shared memory)
  • via (for VIA) 


In the following example, -E indicates the use of a trusted connection:

SQLCMD –Stcp:.\SQL08,1435 -E

When all else fails,youcanuse telnet to test the openness of a port on the firewall. Here’s an example:telnet to test the openness of a port on the firewall. Here’s an example:telnet to test the openness of a port on the firewall. Here’s an example::telnet to test the openness of a port on the firewall. Here’s an example:
telnet IP_Address Port_Number


Summary
This chapter covers a lot of ground regarding client-side (and even a bit of server-side) communication with SQL Server 2008. Some of the sections are admittedly dense enough to bear rereading, and you probably have questions about your specific setup. You can always refer to the sections presented in this chapter to pick up tips on how to best configure and troubleshoot the varying environments you may encounter. And you can (and should) use the extremely helpful Usenet groups that are devoted to the subject (for example,
microsoft.public.sqlserver.clients or microsoft.public.sqlserver.programming). Now that your client configuration is complete, you can move on to next section“Security and User Administration,” to learn how to securely administer the Database Engine

Using MDAC

MDAC contains the OLE DB provider for SQL Server (SQLOLEDB) and the ODBC driver for SQL Server. MDAC is officially part of the operating system, and, as mentioned earlier, MDAC and SNAC are distributed and developed on separate tracks: MDAC with the operating system and SNAC with SQL Server. They do interrelate, however, in that applications that use SNAC can make use of the core services provided by MDAC, including support for connection pooling, client-side cursors, ADO support, and memory management. As mentioned earlier, to make use of the latest SQL Server 2008 functionality, you need to use SNAC.


If, at any time, you want to discover which version of MDAC is installed on a machine, you can simply check the value of the following Registry key (using regedit.exe or from code): HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Version
Note also that the planned MDAC version 10.0 release has been killed and superseded by SNAC.


  • If you choose to upgrade from MDAC to SNAC, it’s important to note some key differences between the two that could affect your applications: Return values from SQL Server 2008 to MDAC applications are implicitly type converted, as shown in Table 10.2.implicitly type converted, as shown in the next pic.​​

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.


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 certificate 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 (as seen in the pic below). Then you select the first radio button (Automatically Select the Certificate Store) and then click Next. Finally, you click Finish.

Client Installation and Configuration-Part two

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


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


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.


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.


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 (SQL Native Client)

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.


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:


includesqlncli.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. 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 connection 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 the below picture.

Next, you launch SSCM, right-click the SQL Native Client 10.0 Configuration node, and then select Properties. The Flags tab appears (as seen below ) in the Properties window.

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.


As you can see, the default port, 1433, is set up to connect to the more commonly configured 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 (in the below picture), 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.)


  • Warning and error messages and message handling differ between MDAC and SNAC.
  • SNAC requires that T-SQL parameters begin with the @ character; MDAC does not.
  • SNAC, unlike MDAC, is not compatible with Visual Studio Analyzer or PerfMon. 


For further details, see the Books Online topic “Updating an Application to SQL Native Client from MDAC.”


Using ODBC with MDAC You can configure an ODBC connection by using a connection string or DSN that specifies the Microsoft ODBC driver for SQL Server. For connection strings, you use the keyword-value pair Provider={SQL Server}. To use a DSN, you run the Data Sources (ODBC) applet, as mentioned earlier. When choosing a driver, you select the one simply named SQL Server.


Using OLE DB with MDAC You can access SQL Server 2008 databases by using the Microsoft OLE DB provider for SQL Server (SQLOLEDB). In connection strings or property values, you use the Provider keyword and the value SQLOLEDB.
 

Unlike with SNAC’s OLE DB provider, with SQLOLEDB you can access both SQL Server data and data from non–SQL Server data sources. Also, SNAC is not dependent on any particular version of MDAC because it expects that a compatible MDAC version will be present on the operating system, as enforced by its own installation requirements.


Using JDBC

Microsoft released a freely downloadable, JDBC 4.0-compliant, Type 4 driver for use with SQL Server 2008. It can be used from all types of Java programs and servers via the J2EE connection API. The following is the basic syntax for a JDBC connection string:


jdbc:sqlserver://ServerName\InstanceName:port;property=value[;property=value]


For complete details on using JDBC, check out Microsoft’s JDBC product documentation at http://msdn.microsoft.com/en-us/library/ee229547(v=SQL.10).aspx. You might also find the newsgroup microsoft.public.sqlserver.jdbcdriver helpful. 


 General Networking Considerations and Troubleshooting

This section provides guidelines for solving some common connectivity issues. You can perform the following steps as a first line of defense when your connections fail:

  1. Check whether the server is configured (via SSCM, as detailed earlier in this chapter, in the section “Server Network Protocols”) to accept remote connections.
  2. Ensure that the SQL Browser service is started.
  3. Determine whether clients are specifying the correct port (for using fixed ports with named instances) in the server alias or connection string.
  4. Check whether the client’s network protocols are enabled and configured to correctly handshake with those of the server. They should use SSCM on both sides, as explained earlier in this chapter, in the section “Client Configuration Using SSCM.
  5. Be sure you have permission to connect on the server’s endpoints.
  6. When using encryption, be sure the server and client certificates match (that is, check their Common Name (CN) and any other relevant attributes) and are installed and configured correctly on both sides. (See the section “Connection Encryption,” earlier in this chapter.)
  7. Make certain that your firewalls are configured to permit the required network traffic. (See the following section, “Firewall Considerations.”)
  8. Check to see whether your users have permission to log in to the server and access the specified database.
  9. Make sure that your clients’ choices of providers support the SQL Server 2008 features they are trying to use.
  10.  Make sure the provider, driver, DSN, server alias, or other connection mechanism is still valid and hasn’t been altered or removed from the system.
  11.  Network administrators are no longer added to the SQL Server sysadmin role by default. If the user trying to connect is a network administrator, he or she must be granted explicit permission with SQL Server 2008. See the topic named “Database Engine Configuration - Account Provisioning” in Books Online for more information.


Firewall Considerations

For clients to successfully connect through a firewall, it must be configured to allow the following:

  • Bidirectional traffic on UDP Port 1434—This is required only for communications to and from the SQL Browser service; when SQL Browser is not in use, you can close this port.
  • Bidirectional traffic on any TCP port used by SQL Server—Be sure to open port 1433 for default instances and also open any fixed ports assigned to your named or default instances. (TCP high port numbers must be opened only when dynamic ports are used by named instances. 


Using dynamic port numbers for named instances is not recommended.) You can determine the ports currently in use via SSCM. When using Windows Firewall, you can easily open these ports. To do this, you run Windows Firewall from the Control Panel, and on the main screen that appears, you click the Exceptions tab. Then you click the Add Port button and enter the required names (either SQL Server or SQL Browser, for example) and port numbers, one at a time, on the Add a Port screen that appears (see the next picture).

 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 following statement to your C# code file:
using System.Data.SqlClient;

For VB .NET, you use this:
Imports System.Data.SqlClient

And for JScript .NET, you use this:
import System.Data.SqlClient;


Note that the .NET provider supports a variety of connection string styles, including ODBC, OLE DB, and OLE DB/SNAC, and you can mix and match some of their respective connection string keywords. For example, Database and Initial Catalog mean the same thing to ADO.NET, and so do Server and Data Source. But don’t let this fool you: Under the covers, only the .NETprovider is always in use. (This is probably why changing the value passed to the Provider keyword seems to have no noticeable effect.) Applications built on .NET Framework 1.0 and 1.1 can access SQL Server 2008 databases without issue. The only caveat is that those earlier versions of ADO.NET can’t make use of certain SQL Server 2008 features, such as asynchronous command execution, cache synchronization, bulk copy, and the new data types. (However, implicit conversions such as from varchar to xml and from UDTs to varbinary allow their use as T-SQL input from .NET Framework 1.1 applications.) ADO.NET 2.0 applications, however, have access to the full gamut of new functionality in SQL Server 2008. 


The following is an example of two connection strings (in different styles) that both turn on the MARS feature for ADO.NET 2.0 applications: 


The following is in ODBC style:

Driver={SQL Native Client 10.0}; Database=AdventureWorks2008;

Server=MyServer/SQL08;

Encrypt=yes; Trusted_Connection=yes; MARS_Connection=yes


The following is in OLE DB style:


Provider=SQLNCLI10; Database=AdventureWorks2008;

Server=MyServer/SQL08;

Encrypt=yes; Trusted_Connection=yes; MultipleActiveResultSets=true


 Notice the use of the keywords MARS_Connection (MultipleActiveResultSets also works) and Encrypt (which requests connection encryption from the server).


The SQLCLR Context Connection

When you need to connect to SQL Server 2008 from within a managed stored procedure, function, or trigger (known as SQLCLR code), which is possible only with .NET 2.0 or greater, you use a special type of connection, known as a context connection. This feature prevents you from having to open a new connection because the code itself is already running within the context of an open connection. The connection string for context connections is extremely easy to use (”context connection=true”), as the C# example in the next section.


using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

      [Microsoft.SqlServer.Server.SqlProcedure]

     public static void ContextConnectionTest()

     {

           using (SqlConnection Context =

                  new SqlConnection(“context connection=true”))

           {

                  using (SqlCommand TestCommand =

                         new SqlCommand(“SELECT TOP 10 * FROM Person.Person”, Context))

                  {

                         using (SqlDataAdapter Adapter =

                                 new SqlDataAdapter(TestCommand))

                         {

                                 using (DataSet MyData = new DataSet())

                                 {

                                       Adapter.Fill(MyData);

                                   }

                           }

                    }

                }

           }

}


 For more information on building SQLCLR client libraries, see Chapter, “SQLCLR: Developing SQL Server Objects in .NET”


Online learning

​fREE

SQL DBA SCHOOL

Founder Abubeker Refaw