Throughout the most recent installments of our series dedicated to SQL
Server 2005 Express Edition, we have been discussing its replication-specific
features, focusing in particular on its role in Merge Web Synchronization
implementations. As we have demonstrated so far, despite several shortcomings
in this area (such as its agentless nature or inability to take on publisher
and distributor responsibilities), our protagonist can operate fairly
efficiently as a subscriber by leveraging Windows Synchronization Manager or
command line executables (such as replmerg.exe) combined with Windows Scheduled
Tasks. As part of our discussion, we have also started investigating in a fairly
detailed manner authentication mechanisms that are available in order to
accommodate connection attempts from remote clients interacting with SQL
Server-based distributor via Internet Information Services. We will continue
coverage of this topic here.
As we have described earlier, while basic authentication offers a
considerable degree of flexibility in a variety of scenarios (including
communication over the Internet), it also requires that users explicitly
specify their credentials (which, in turn, implies the need for additional
security provisions, such as certificate-based encryption). Integrated
authentication eliminates this requirement, but introduces its own set of
restrictions (most notably, dependency on membership of all its participants in
the same Active Directory environment). However, as long as these can be
addressed, you can reap extra security benefits inherent to operations within
boundaries established by Windows domains.
One of the issues that frequently surface in deployments that rely on
integrated authentication is its inability to apply impersonation – allowing a
target server to act on its client’s behalf – if the direct communication
between the two is not possible, but, instead, it involves an intermediary
system (such as an IIS server, in the case of Merge Web Synchronization). To
make such an arrangement possible, it is necessary to resort to delegation. We
have explained its principles and basic setup in the
previous article. It is important to realize that in its most rudimentary
form (the only one available in the Windows 2000 Server release), delegation is
frequently avoided due to the unnecessarily high degree of risk it introduces.
In particular, it allows a computer that is designated as "trusted for
delegation" (based on the setting configurable via the Properties dialog
box of its account in Active Directory Users and Computers management console)
to impersonate any user (as long as that user’s account is permitted to be
"trusted for delegation") when interacting with any other computer in
the same domain. In addition, in its original implementation, delegation is
available only to clients connecting via Basic or Kerberos authentication (with
the latter being fairly rare in Internet-based communication). Both of these
shortcomings have been remediated in Windows Server 2003 (and become available
once you upgrade your Active Directory to Windows Server 2003 domain functional
level) thanks to constrained delegation and protocol transition functionality.
Constrained delegation provides more granularity when defining the scope of
trust assigned to an intermediary computer acting on behalf of its users.
Protocol transition permits an alternative means of accepting their security
credentials (using any IIS-supported authentication methods, including Digest
and certificate-based ones), which are subsequently relayed to the back-end
server via Kerberos protocol. The relevant settings are presented on the
Delegation tab of that computer’s Properties dialog box (within the Active
Directory Users and Computers management console) in the form of three options
buttons. The default (with the exception of domain controller accounts) is
"Do not trust this computer for delegation". The second – "Trust
this computer for delegation to any service (Kerberos only)" – is
equivalent to the Windows 2000 Server "Trust computer for delegation"
entry. If you enable the third one labeled "Trust this computer for delegation
to specified services only", you will be able to specify whether you want
to "Use Kerberos only" or allow "Use any authentication
protocol". Furthermore, at that point, it also becomes possible to
designate target users and computers (along with their individual services and
corresponding TCP/UDP port numbers) to which the computer account is allowed to
present delegated credentials (note that constrained delegation requires that
intermediary and target computers be in the same Active Directory Windows
Server 2003 functional level domain).
In order to take advantage of this option, you need to know the Service
Principal Name representing the target resource that delegated users attempt to
access (for more information regarding this subject, refer to the
previous article of our series), which in our case, is associated with the
DATABASEJOURNALs-ALPHA-SQL user account. Once you have this information
available, click on the Add… command button (on the Delegation tab in the
Properties dialog box of the Web Server computer account in Active Directory
Users and Computers management console), followed by the Users and Computers…
command button in the Add services pop-up window. Select the user account
associated with the SPN of the SQL Server 2005 Service, and confirm your choice
to complete the process. This should produce a single entry in the
"Services to which this account can present delegated credentials:" listbox
with MSSQLSvc as the Service Type, 1433 as its Port, and the fully qualified
domain name of the computer hosting the SQL Server 2005-based distributor in
the "User or Computer" column.
Limiting the scope of delegation is transparent to subscribers, which means
that you should be able to initiate replication by executing the replmerg.exe
command-line utility without any changes to the previously described syntax. (As
before, you can confirm that the connection to the distributor leverages Kerberos
authentication by examining the outcome of content of its sys.dm_exec_connections dynamic
management view while synchronization is in progress):
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]
Protocol transition is a bit more complex to configure, since it requires
altering Web Server and Active Directory settings, as well as adjusting the parameters
of the replmerg.exe on a subscriber. To demonstrate its implementation, we will
modify our sample setup to use Digest authentication. Its basic mechanism is
similar to the challenge/response process employed by the NTLM protocol, since
it involves a piece of random data sent by an authenticating server in response
to its client’s connection request. This data is then encrypted using the
password of the connecting user and returned to the server, which compares its
value with the outcome of its own, equivalent process performed based on
credentials of the same user retrieved from Active Directory. Keep in mind that
in order for this procedure to function as intended, passwords of domain users
must be stored using reversible encryption. In addition, these users and their
authenticating servers must be members of the same Active Directory forest.
We will start our sample implementation by selecting the "Digest
Authentication for Windows domain servers" option in the Authentication
Methods dialog box accessible from the Directory Security tab of the WebSync
Web Site Properties dialog box in the Internet Information Services Manager
console (and making sure that all other checkboxes are cleared). As part of
this step, you also need to specify an appropriate realm value, which should
match the fully qualified name of your Active Directory domain (set, in our
case, to DatabaseJournal.com). Next, ensure that the password of the user who initiates
Web Synchronization from the subscriber is stored in AD database using
reversible encryption. This is accomplished by accessing the Account tab of the
user’s Properties dialog box in Active Directory Users and Computers management
console and enabling the "Store password using reversible encryption"
checkbox in the Account options section. (Alternatively, you can also use
Windows Group Policy if you want to apply this setting to a larger number of
accounts). In order for the setting to take effect, you will need to either ask
your users to change their password or request their resets. Finally, it is
also necessary to revise parameters of the replmerg.exe command line utility,
by setting -InternetSecurityMode
to 0 as well as adding -InternetLogin
and -InternetPassword,
containing the name (without the domain qualifier, which is determined based on
the realm value we assigned earlier via the Directory Security tab of the WebSync
Web Site Properties dialog) and password of the user account initiating the Web
Synchronization. As the result, the command takes the following form:
replmerg -Publisher [ALPHA] -PublisherDB [AdventureWorks] -Publication [ProductDescription(Production)]
-Distributor [ALPHA] -SubscriptionType 1 -Subscriber [OMEGASQLEXPRESS]
-SubscriberSecurityMode 1 -SubscriberDB [AdventureWorksRepl] -InternetSecurityMode 0
-InternetURL [https://www.DatabaseJournal.com/WebSync/replisapi.dll]
-InternetLogin [SyncU$eR] -InternetPassword [SyncP@$$w0rD]
To confirm that the subscriber connects to the Web Server using Digest
authentication, launch Internet Explorer and connect to the https://www.DatabaseJournal.com/WebSync/replisapi.dll?diag
URL, which displays the SQL Websync diagnostic information page (including
AUTH_TYPE entry). As before, you can also verify that connections reaching the
SQL Server 2005-based publisher/distributor utilize Kerberos authentication by
enumerating content of the sys.dm_exec_connections
dynamic management view while synchronization is in progress.
This concludes our coverage of various authentication methods that can be
used in Web Synchronization scenarios involving SQL Server 2005 Express
Edition. In the next article of our series, we will examine the remaining
workarounds that emulate the functionality of replication agents.
