Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 24, 2008

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

By Marcin Policht

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 DATABASEJOURNAL\s-ALPHA-SQL account using the default listening port, you would need to execute the following:

SetSPN –A MSSQLSvc/ALPHA.DataBaseJournal.com:1433 DATABASEJOURNAL\s-ALPHA-SQL

You should receive confirmation stating that the Active Directory object representing DATABASEJOURNAL\s-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 [OMEGA\SQLEXPRESS]
 -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.

» See All Articles by Columnist Marcin Policht



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM