SQL Server 2005 Express Edition - Part 20 - Authenticating Merge Web Synchronization

Friday Apr 11th 2008 by Marcin Policht

We have been discussing SQL Server 2005 Express Edition replication-specific features, investigating 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.

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 DATABASEJOURNAL\s-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 [OMEGA\SQLEXPRESS]
 -SubscriberSecurityMode 1 -SubscriberDB [AdventureWorksRepl] -InternetSecurityMode 1
 -InternetURL [https://mobile.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 [OMEGA\SQLEXPRESS]
 -SubscriberSecurityMode 1 -SubscriberDB [AdventureWorksRepl] -InternetSecurityMode 0
 -InternetURL [https://mobile.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://mobile.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.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site