Distributed queries – (Part-1)

Author: Basit A. Farooq

 

Distributed queries are queries that involve data from data sources on multiple computers or multiple instances of SQL Server. SQL Server supports distributed queries through OLE-DB, a library that allows connections to any data sources that has an OLE DB provider. In this two part article series, you’ll learn how to configure linked servers, which are defined connections between servers. You’ll also learn how to issue ad hoc distributed queries.

About distributed queries

A distributed query is one that is executed on one SQL Server, but accesses data stores on other computers. Other computers can include other database management systems.

Running distributed queries

SQL Server 2005 handles distributed queries through OLE DB. As you can see in figure below, SQL Server sends requests through its OLE DB interface to an OLE DB provider for the data source. The OLE DB provider communicates with the actual data source.

 

 

OLE DB providers included with SQL Server

You specify the provider when connecting to a remote server or another instance of SQL Server. SQL Server 2005 has been tested with the OLE DB providers listed in the following table. The Programmatic ID column lists the value you use when setting up the linked server.

  

In addition, there are providers for accessing Analysis Services data stores and XML data stores. You can also add OLE DB providers, but if you do, you need to test them to ensure they work with SQL Server.

Configuring provider properties

You can configure an OLE DB provider using SQL Server Management Studio. The options you configure affect all linked servers that use the provider. To do so:

· In Object Explorer, expand Server Objects, Linked Servers, Providers.

· Right-click the provider and choose Properties.

· Check to enable and clear to disable provider options. You can set the following options:

o Dynamic parameters — allows parameterized queries.

o Nested queries — allows nested SELECT statements in the FROM clause.

o Level zero only — allows only level 0 OLE DB interfaces. An interface defines a behavior.

o Allow in process — provider can run in the same process as SQL Server.

o Non transacted updates — allows updates even if the provider does not support transactions.

o Index as access path — can use indexes to fetch data.

o Disallow ad hoc access — Prevents access using OPENROWSET and OPENDATASOURCE. These statements do not require a linked server.

o Supports ‘Like’ operator — Allows queries using the ‘Like’ operator.

· Click OK.

Security for distributed queries

When SQL Server accesses a linked server, it sends login information to the linked server. The login information can be either:

· Self-mapped — a mapping automatically created when the linked server is defined.

· Manually mapped — a linked server login created on the SQL Server where the linked server object exists.

The following figure shows both types of mappings. In this example:

· When accessing the linked server that can authenticates Smith using the domain account, SQL Server just passes the login information to the server.

· When accessing the linked server that can’t use authentication, SQL Server must pass a login mapped to a login on the linked server.

Supporting self-mapped authentication

To support self-mapped authentication, the client, SQL Server and the linked server must be running TCP/IP. The user initiating the request must be logged on to Windows with an account that:

· Is a valid login on SQL Server and on the linked server.

· Doesn’t have the Active Directory property “Account is sensitive and cannot be delegated” set.

The SQL Server instance that initiates the query must:

· Have a Service Principal Name (SPN) registered in the domain.

· Be running under a service account that is trusted for delegation.

· Have a linked server associated with the remote data source.

· Be configured to support self-mapping. The remote data source must:

· Have a Service Principal Name (SPN) registered in the domain.

Creating an SPN

If SQL Server is running under the Local System account, it creates the SPN automatically. If SQL Server is running under a domain user account, you must register the SPN by using the SetSPN.exe utility.

The SetSPN.exe utility is a Windows Server 2003 utility included in the Support.msi package. To install the support package from the Windows Server 2003 installation CD:

· From the autostart screen, click “Perform Additional Tasks.”

· Click “Browse this CD.”

· Double-click Support.

· Double-click Tools.

· Double-click SUPTOOLS.MSI.

· Click Next.

· Enter your Name and Organization.

· Click Next three times. The installation starts.

· After installation completes, click Finish. The syntax for running SetSPN.exe is:

setspn –A SPN | -D SPN | -L service_account

The options are described in the following table.

For example, to add an SPN for an account named SQLAcct that runs the default instance of SQL Server on a computer named SQL1 in the Outlanderspices.com domain, you run:

setspn -A MSSQLSvc/SQL1.Outlanderspices.com:1433 SQLAcct

Configuring a service account as trusted for delegation

You need to modify the service account in Active Directory to make it trusted for delegation. To do so:

· Launch Active Directory Users and Computers.

· Select the container where the user account is located.

· Right-click the user account and choose Properties.

· Click Account.

· Under Account options, check “Account is trusted for delegation.”

· Click OK.

Configuring self-mapping

You must also configure SQL Server to support self-mapping using the sp_addlinkedsrvlogin system stored procedure.

The sp_addlinkedsrvlogin stored procedure has the following syntax:

sp_addlinkedsrvlogin [@rmtsrvname=] ‘remtsrvname’ [, [@useself=]’useself][, [@locallogin=] ‘locallogin’] [, [@rmtusr=] ‘rmtusr’][, [@rmtpassword=] ‘rmtpassword’]

The parameters are described in the following table.

 

To use sp_addlinkedsrvlogin to enable self-mapping for a remote server named RemSrv, you execute:

EXEC sp_addlinkedsrvlogin ‘RemSrv’, ‘true’

Mapping logins

To map the login georgeV on SQL Server to a login named gVasquez on the remote server, you execute sp_addlinkedsrvlogin as follows:

EXEC sp_addlinkedsrvlogin ‘RemSrv’, ‘georgeV’, ‘gVasquez’, ‘637ls*HPo’

Dropping login mappings

You can use the sp_droplinkedsrvlogin system stored procedure to delete a mapping. It has the syntax:

sp_droplinkedsrvlogin [@rmtsrvname=] ‘rmtsrvname’ [@locallogin=] ‘locallogin’]

For example, to drop the mapping for georgeV, you execute:

EXEC sp_droplinkedsrvlogin ‘RemSrv’, ‘georgeV’

Configuring linked servers

The best way to support distributed transactions is to create a linked server object that represents the data source. You can create a linked server object using SQL Server Management Studio or the sp_addlinkedserver system stored procedure.

Using sp_addlinkedserver

The sp_addlinkedserver system stored procedure has the following syntax:

sp_addlinkedserver [ @server = ] ‘server’ [ , [ @srvproduct = ] ‘product_name’ ] [ , [ @provider = ] ‘provider_name’ ] [ , [ @datasrc = ] ‘data_source’ ][ , [ @location = ] ‘location’ ] [ , [ @provstr = ] ‘provider_string’ ] [ , [ @catalog = ] ‘catalog’

The parameters are described in the following table:

 

For example, to create a linked server to a SQL Server named RemSrv, execute:

EXEC sp_addlinkedserver ‘RemSrv’, ‘SQL Server’