In the most recent installments of our series dealing with SQL Server 2005
Express Edition-related topics, we have provided an overview of Web
Synchronization, which facilitates efficient and inexpensive implementations of
merge replication with remote subscribers, separated from their publishers via
(potentially insecure and unreliable) TCP/IP networks (such as the Internet).
So far, we have presented a simplified scenario that illustrated its basic
setup and discussed several options (related to SSL certificates and creating
subscriptions via stored procedures, without direct connectivity between
replication partners) that addressed some of its limitations. This article will
further elaborate on this subject, by focusing on authentication settings that
are available when configuring Web access for multi-tier SQL Server-based
applications.
As we have demonstrated in our sample implementation, providing access to a
publication via an IIS-hosted Web site involves running the Configure Web
Synchronization wizard (accessible from the context sensitive menu of the
publication located under the Local Publications subnode of the Replication
node in the Object Explorer window of the SQL Server Management Studio on the
publisher). The options available on its "Authenticated Access" page
determine configuration of the target virtual directory on the Web Server (by
mapping directly to selections listed in the Authentication Methods interface
accessible via the Directory Security tab of its Properties dialog box in the
Internet Information Services Manager console). The three possible choices
include Basic, Windows Integrated, and Digest.
Basic Authentication is the method employed in our initial examples. While
it requires that you explicitly provide logon credentials (username and
password) when initiating Web Synchronization, their confidentiality is
protected by SSL encryption (leveraging the certificate you installed earlier).
Among its primary benefits are simplicity combined with flexibility, including
support built into all major client Internet browser and Web server software
and the ability to accommodate subscribers which do not reside in the same
Active Directory forest as the intermediary and target computers. Its
configuration is straightforward (consisting mainly of selecting the appropriate
checkbox and specifying realm and default domain, matching the name included in
the Web Server certificate and designating Active Directory domain where the
accounts of remote users reside) during the Configure Web Synchronization
Wizard (or directly in the Authentication Methods window).
Basic authentication corresponds to the value of 0 assigned to the -InternetSecurityMode parameter of the
REPLMERG.EXE merge replication agent executable. As mentioned, you will also
need to assign values to -InternetLogin
and -InternetPassword
parameters (for a comprehensive overview of the REPLMERG.EXE syntax, refer to
the Replication
Merge Agent article in SQL Server 2005 Books Online).
Integrated Windows authentication, unlike the Basic one, does not rely on an
explicitly provided username and password, but instead transparently applies
credentials of the locally logged on account to a target computer (in our
example, this would be a user initiating Web Synchronization from the
subscriber). With the exception of those rare (and not recommended) cases where
a distributor/publisher and a Web server hosting target virtual directory are
located on the same system, these credentials need to be subsequently relayed
to the SQL Server. In order for the latter part of this procedure to succeed,
you need to ensure that authentication protocol does not use impersonation (whose
scope is limited to direct communication between two computers), but employs
delegation mechanism instead.
In an Active Directory environment, the delegation process leverages the capabilities
built into the Kerberos authentication protocol, which imposes some additional
restrictions on its participants. In particular, it expects that a target
service (our SQL Server 2005 Database Engine) is registered in Active Directory
and associated with a security principal (that provides security context, in
which the service executes on the target computer). In addition, the computer
trusted for delegation (which, in our case, is the system hosting IIS
component) and its target (SQL Server 2005 Enterprise Edition-based publisher)
have to belong to the same Active Directory forest (the same or a trusted
domain) and must be configured for delegation (along with the service account).
The first of these requirements is provisioned automatically as long as the
service is running in the security context of one of the built-in secuirty
principals (such as LocalSystem or NetworkService accounts, which, from the
authentication perspective, are represented by the host computer’s Active
Directory account). However, if you decide to use a domain-based user account
without Domain Admins-level privileges for this purpose, you will need to
register the Service Principal Name associated with it manually (since the
permissions to perform this action are limited to members of Domain Admins
group and services running in the security context of built-in security
principals on domain computers). This is typically done using the SetSPN.exe
utility, included in Windows Server 2003 installations and available from the Download
section of the Microsoft Web site for Windows 2000 Server platform (part of
the Windows 2000 Resource Kit). Assuming that your SQL Server 2005 Enterprise
Edition-based publisher is installed on the computer ALPHA in the
DataBaseJournal.com domain and runs in the security context of the
DATABASEJOURNALs-ALPHA-SQL account using the default listening port, you would
need to execute the following:
SetSPN –A MSSQLSvc/ALPHA.DataBaseJournal.com:1433 DATABASEJOURNALs-ALPHA-SQL
You should receive confirmation stating that the Active Directory object
representing DATABASEJOURNALs-ALPHA-SQL account has been updated. To list all
of its registered SPNs, rerun the SetSPN with the -L switch. Alternatively, you can view this information
via the ADSIEdit console (part of the Windows Support Tools included on the
Windows Server installation media). From its interface, drill down the Domain
node to locate a subnode representing the target account. Activate its
Properties window, locate its servicePrincipalName attribute, and click on the Edit
command button to display its content (if you want to verify a list of SPNs
running in the security context of built-in principals, locate an object that
represents the computer on which the respective service is running).
At this point, you are ready to take care of the latter requirements we
listed (with assumption that our three computers are part of the same domain).
This is handled by modifying the properties of the Web Server computer account,
which is easily accomplished using the Active Directory Users and Computers
management console. Within its interface, locate an entry representing the
server and display its Properties dialog box. If you operate in a Windows 2000
native-mode domain, enable the "Trust computer for delegation"
checkbox on the General tab. In a Windows Server 2003 functional level
environment, you have the ability to configure delegation on a more granular
level (via the feature known as Constrained Delegation), restricting it to
specific services only (which we will cover in more detail in our next
article), with the relevant settings grouped on the Delegation tab of the
computer’s Properties dialog box. For now, we will select the "Trust this
computer for delegation to any service (Kerberos only)" entry, which is
equivalent to the Windows 2000 "Trust computer for delegation"
option.
If the service on a target computer (our SQL Server 2005 Enterprise Edition
Database Engine) operates in the security context of a non-privileged user
account, you will also need to allow it to be trusted for delegation. The
setting controlling this ability ("Account is trusted for
delegation") appears on the Account tab of the user’s object Properties
dialog box in Windows 2000 native-mode domains (at the same rate, ensure that
"Account is sensitive and cannot be delegated" entry remains
disabled). Windows Server 2003 functional level environment offers the same
capabilities for users that apply to computer accounts (which means that users
associated with Service Principal Names would also have the Delegation tab on
their account’s Properties dialog box in the Active Directory Users and
Computers console, where you can enable either full or constrained delegation).
The next step involves assigning the authentication mechanism of the IIS
Server. As mentioned earlier, this can be done by rerunning the Web
Synchronization wizard or by accessing the Directory Security tab on the target
Web site Properties dialog box in the Internet Information Services Manager
console. When using the latter, ensure that all other authentication methods
are disabled. Once this is completed, you should be able to initiate Web
Synchronization from the subscriber. Assuming that our publisher/distributor is
located on the default instance of SQL Server 2005 Enterprise Edition on the
computer ALPHA, that our SQL
Server 2005 Express Edition-based subscription database called AdventureWorksRepl resides on OMEGA, that our publication is called SalesTaxRate(Sales) (based on the table
in the AdventureWorks database), that our target Web URL is https://www.DatabaseJournal.com/WebSync/replisapi.dll,
and that you are logged on to the subscriber with domain credentials that have
sufficient permissions to perform this action, then the following would allow
you to trigger synchronization from the Command Prompt. (Note that the listing
below constitutes a single command and should be typed in without any line
breaks):
replmerg -Publisher [ALPHA] -PublisherDB [AdventureWorks] -Publication [ProductDescription(Production)] -Distributor [ALPHA] -SubscriptionType 1 -Subscriber [OMEGASQLEXPRESS] -SubscriberSecurityMode 1 -SubscriberDB [AdventureWorksRepl] -InternetSecurityMode 1 -InternetURL [https://www.DatabaseJournal.com/WebSync/replisapi.dll]
Note that -InternetSecurityMode
parameter is optional in this case since the value of 1 (which enforces Windows
Authentication) is its default. To confirm that the process is using Kerberos
authentication, you can examine the content of the sys.dm_exec_connections dynamic
management view while the connection is active (in particular, pay attention to
the auth_scheme column).
In our next article, we will explore other authentication options that can
be implemented when using Web Synchronization with SQL Server 2005 Express
Edition serving the role of a subscriber.
