Microsoftıs BackOffice suite of products has become increasingly powerful. With added power comes added complexity, however. There are now eight servers contained within the suite ı Windows NT Server, Microsoft SQL Server, Internet Information Server (IIS), Systems Management Server, SNA Server, Exchange Server, Microsoft Transaction Server (MTS), and MSMQ Server. This makes understanding the integration and interaction between them extremely important for a DBA. The information flow facilitated by these servers is crucial to the functional effectiveness of an organization.
For the IS specialist or DBA, the BackOffice suite of servers is increasingly difficult to manage. Many of the servers included in the suite cover several specialty areas and require coordination among different people and resources within a single company. Those responsible for managing the database systems must now understand how information and configuration are shared to optimize information use and minimize effort duplication. Understanding the integration of the various servers is crucial in order to close potential security loopholes.
A staggering amount of information is required for complete knowledge of the Windows NT system. In this article I will focus on technology that directly relates to disseminating database information. By explaining and diagramming the layers of communication among these servers, the database specialist and MIS manager will better understand the complete server framework. Even someone intimately familiar with the individual servers will benefit from the birdıs-eye view provided.
Ideally, BackOffice provides a centralized administration application so that you can manage all servers simultaneously. Unfortunately, there are no easy answers for the centralization of control within the suite. Even Microsoftıs plan to unify security with the new Windows NT Directory Services (NDS) will not allow the granularity needed for database and groupware control.
The two most important areas of the integration for a DBA include: the overlap where multiple servers need to exchange data and the integrated security technology that must be in place between them. Each server supports numerous formats and protocols, including both proprietary and open standards. However, Microsoft has created a series of specific protocols and connectors that are used by the servers themselves.
The common data access technology of the past, ODBC, now has a competitor in Microsoftıs new OLE DB standard and also in several other new layers of middleware products. For example, you must have several technologies in place to display database information on a Web page. An IIS page can only read and display database data with a combination of Active Server Pages (ASPs), Active Data Objects (ADOs), OLE DB, and/or ODBC. Such access can become even more convoluted if the data access actually occurs through an object stored in the Transaction Server.
The following scenarios show how you can make all your information available to users of any of the BackOffice products. The growth of intranets has made database access via a Web browser one of the most common situations in which multiple servers must operate in unison. Extra details on Exchange to Web and SQL Server to Exchange Server scenarios.
To display data stored in a SQL Server database on a Web page, you need to create a link between IIS and SQL Server. Windows NT Server includes an IIS that serves as both a Web and FTP server. The FTP server could be used to distribute simple static data, but make sure the folder permissions are properly set using normal Windows NT security. For the Web server, use the ASPs. See Figure 1 for an overview of all the technology used to create a Web page from an IIS request.
ASP is a server-based execution engine that runs as part of IIS and executes scripts written in VB Script, JavaScript, or a third-party scripting engine designed to work with ActiveX. Scripts are stored in files with the .asp extension. These scripts have access to any files on the server system and can use ADO to retrieve data from a data source.
If you are familiar with DAOs or Remote Data Objects (RDOs), which are supported by Visual Basic or Visual C++, youıre already familiar with ADO. ADO is the newest revision to the DAO model and supports cursors, recordsets, snapshots, and so on. The new objects were created to simplify access and provide a generalized interface to Microsoftıs new OLE DB technology.
OLE DB will become increasingly important to database specialists if Microsoft succeeds in making it an industry standard access model as it has with ODBC. OLE DB does and does not replace ODBC in its basic design; it was created to enable the querying of relational and nonrelational data sources through a common object framework. Like DAO before it, OLE DB greatly simplifies most access to SQL-based data sources. Although the fastest performance is achieved with custom OLE DB drivers, ADO calls to ODBC through OLE DB add only a little overhead when compared with direct OLE DB access.
Figure 1 shows both forms of OLE DB access, through a custom OLE DB driver to SQL Server and a through ODBC to a generic data source. Retrieved information is stored in a requested recordset for use with the ADO by the executing script. In the future, Microsoft will be extending both OLE DB and ADO to be the standard method of accessing data sources on the Windows platform. As this demonstrates, SQL Server or databases are not the only servers that can be hooked to the Web.
Placing data in HTML format obviously requires the coordination of several servers and a great deal of technology. Using a product such as Visual InterDev, Microsoftıs Web development tool, dramatically reduces the amount of work that must be done by hand. Many of the other BackOffice technologies, such as Exchange Server, have wizards and connectors to simplify multiserver integration.
A new wave is sweeping through the groupware community: Web-enabled groupware access. Starting with the Lotus Domino server, Web connection to a groupware server lets users send and receive email and access public folders from any Web browser. Microsoft allows Exchange Server to be connected to the Web via the IIS server and ASPs. (See Figure 2, page XX.)
To view Exchange email, the user only needs a browser that supports frames, JavaScript, and cookies. Called Outlook Web Access, this connector to Exchange Server creates a display on a Web browser allowing full access to a user's email box. With each Web page request, the Outlook Web Access component queries the Exchange Server in order to build an ASP file to display the information.
Logging in through a Web browser presents the user with a familiar interface. After the user's name and password have been entered, the user has access to services such as sending and receiving messages, adding attachments, and forwarding messages.
Microsoft has made this connection almost seamless. There are two pitfalls to this system, one for Internet Explorer and one for Netscape Navigator. To attach files, Internet Explorer 3 and earlier versions require a special ActiveX control to allow Web file uploads. This control is available on the Internet Explorer Web page (www.microsoft.com/ie). Netscape Navigator, including the version shipped with Communicator, does not support the Windows NT challenge/response security functionality required for secure login of a particular user. A Navigator plug-in is available to provide this functionality.
Although it is theoretically possible to store Exchange message information in a different data source than the Exchange Server engine, Microsoft does not recommend using this option because SQL Server is not optimized for the types of data used by the Exchange Server. Exchange Server is capable of storing VB Script on Outlook forms, but for security reasons, execution of scripts is limited to the client side.
Most of the connectivity between Exchange Server and another server occurs between another server and the Exchange Server. Communicating with the Exchange Server is relatively easy and occurs through the MAPI or extended MAPI interfaces. Using the stored procedure xp_sendmail, you can use SQL Server to post a message to an Exchange public folder or send a message to an Exchange user.
Direct access through OLE technology is possible by using the component for OLE Messaging. This OLE Messaging control is an OLE Automation interface to the Extended MAPI structure. Manipulated through the OLE/Component Object Model (COM) interface, OLE Messaging can be used to control local and remote access to MAPI accounts. OLE Messaging can be used to access public and private folders on an Exchange Server using any OLE-capable technology. This would include IIS through ASP, the Microsoft Outlook client using VB Script, MTS by creating a controlling object, and so on. Microsoft has made Outlook the primary client for accessing the Exchange Server.
(End of online addendum.)
One of the most important recent additions to the BackOffice suite is MTS, which can store and execute ActiveX objects. The server is essentially an object server for use with the Distributed Component Object Model (DCOM) technology. Objects and object sets may be stored on the server for remote invocation.
One of the key integration strengths of MTS is its ability to maintain a transaction across multiple objects. Therefore, an object could be constructed to access several different data sources while maintaining basic atomicity, consistency, isolation, and durability (ACID) properties.
MTS is perhaps the most complicated piece of the integration puzzle. At the same time, MTS may provide the optimal way to centralize information access given the advantages of a multitier architecture. You can build a COM object using Visual C++, Visual Basic, or any other development system capable of creating COM objects or ActiveX DLLs. To use the transaction capabilities, however, you must write components specifically to use the ITransactionContext or the COM IObjectContext interfaces.
You can create an object that can access a data source, providing a multi-tier structure. Transactions occur through the Microsoft Distributed Transaction Coordinator (DTC). You can also use MTS to maximize the connections to the data sources. The ODBC Resource Dispenser built into MTS helps balance requests to data sources across a specified number of connections. Working essentially like a TP monitor, the Resource Dispenser pools ODBC connections. As of press time, MTS does not directly support the OLE DB technology.
For a client application to request a query or update, it would place a call to the object proxy installed on the client system and DCOM would transparently request the object from MTS. (See Figure 2) MTS would find and instantiate the COM object. The object would most likely use one of the object models such as ADO to access ODBC to connect to the data source.
The COM object could, in reality, access nearly any of the other BackOffice servers. For example, it could use the OLE Messaging component to control Exchange Server, or it could even create an ASP file dynamically. Unfortunately, the Transaction Server does not provide load-balancing features among objects. Until such load balancing is available, you wonıt be able to deploy large enterprise applications using MTS alone. When used in conjunction with MSMQ, however, even large-scale solutions are possible.
Microsoft has recently introduced a new server with the Enterprise Edition of BackOffice: MSMQ, previously known as Falcon. MSMQ is a message-oriented middleware server that can queue and perform asynchronous queries and object requests. As a result, a request can be sent from nearly any source to execute an object or a query. The request is placed in a queue and is executed when the resource becomes available.
There will be two versions of MSMQ. The version included with the Standard Edition of Windows NT Server will be limited to 25 concurrent users and will have limited routing and bridge capabilities. The Enterprise Edition allows for unlimited concurrent users and includes cost-based routing features and a bridge to IBMıs MQSeries products. The cost-based routing is a fantastic feature that allows the server to reroute the request to the intended destination if the most commonly used means are not available.
To help you create MSMQ applications, Microsoft has developed a series of ActiveX controls that can be incorporated into projects created with tools such as Visual Basic, Visual C++, or Delphi. A corresponding interface, which receives and processes the messages on the server side, is available for MTS and IIS with ASPs.
An application containing the MSMQ ActiveX component can place a request to an object. The message is then transmitted over the network to the MSMQ server where it is queued until it may be fed to the target server. MSMQ is integrated with MTS so that transaction calls submitted by MSMQ are treated as other transactions created directly with COM calls.
You can use MSMQ to provide asynchronous access to COM and DCOM objects. This means that an object can access a database for a query, insert, update, or delete without waiting for the returned response. This also enables database access on the road, where updates may be queued, but not processed, until the machine is connected to a network with access to the central servers.
The MSMQ server also features significant operational features to ensure that a request is not sent twice and that a queued message is retained until it is established that the server processed the request. The combination of the queuing and load-balancing technology included with MSMQ allows an enterprise to create a large, process-leveling system for OLTP systems that are not time-sensitive.
The MSMQ asynchronous server would be limited if it required every client to use the necessary ActiveX controls to send a request. Fortunately, the server can be used in conjunction with Exchange Server for remote processing without having the MSMQ client controls installed on the remote machine at all. Two controls must be installed into Exchange Server to provide the necessary communication: MSMQ MAPI Transport Provider and MSMQ Exchange Connector. References to the MSMQ queues can be created on the Exchange Server as public folders over the MSMQ Exchange Connector. Messages may be sent from any MAPI-enabled client including Microsoft Outlook.
With the Exchange Connector installed, the content may be sent in a simple Exchange Message. The Exchange Server receives the message in a folder linked to the MSMQ operation queue and sends it the requisite object. The MSMQ MAPI Transport allows MSMQ to encapsulate a return message within a typical MAPI message and return it to the client via the Exchange Server.
The MSMQ Server can be accessed from a Web page through the ASP scripting engines and the MSMQ ActiveX controls. Because of the asynchronous nature of MSMQ, querying a database may not be an ideal solution, but posting information updates becomes quite compelling. If an ASP file were to submit an update, control would immediately return to the page as the update was being queued. Writing a VB Script to create and manipulate the MSMQ ActiveX objects is simple when using the Visual InterDev environment. This provides a perfect mechanism for providing quick response to a Web-based system that allows a large number of simultaneous updates.
With the easy publication of data on a Web-based system (an intranet or the Internet), object database access, or multitiered access systems, understanding security issues is necessary to everyone involved in deploying the BackOffice servers. For example, an object that has permission to access a key financial database may be run on an n-tier server setup. If the object is "captured" and data is illegally retrieved or damaged, whose fault is it? Understanding the security integration of the BackOffice servers will help you deploy not only a safe information system but also a maintainable one where security augmentation is constantly updated.
Table 1 provides an overview of all the servers in the BackOffice suite and most of their key security integration features. The "Missing from integrated security" row describes all the security that must be handled by the independent serverıs administration programs. Microsoft security is only integrated on the most fundamental level for the majority of the BackOffice servers.
Windows NT security resides at the top level of the BackOffice hierarchy. Essentially, all the servers use Windows NT security for their core login. Windows NT allows you to define users and groups for file and permission settings. Although each server must be configured to provide individual user privileges based on specific server features, the user accounts and passwords are held in common at the Windows NT server level. Any changes to the user password are automatically reflected in the login accounts of the other servers.
All the access permissions are stored in the Access Control Lists (ACLs). On a Windows NT server, the ACLs contain all the security preferences for individual folders and registry entries. Any object on the Windows NT system may be listed in the ACL with the corresponding settings. The other BackOffice servers use the ACL as a reference.
The Macintosh services included with Windows NT can also bridge a multiplatform environment. For the DBA, these services include the provision within SQL Server to communicate using the AppleTalk protocol. However, most DBAs are best off simply using TCP/IP services. For folder and file access, it is important to understand that a shared volume does not automatically translate to equivalent privileges on the Macintosh volume. Unix users would most likely use TCP/IP for database services and FTP for file services through IIS.
When a user logs in, Windows NT security uses a challenge/response method over the network. This procedure keeps the password secure. Although the procedure originates through the use of the NetBIOS protocol, Microsoft has incorporated it not only into the TCP/IP protocol, but for use on SQL Server security as well.
SQL Server is well integrated with Windows NT, but it still retains its own security system. SQL Server features three types of security: trusted domain, standard security, and integrated security. Trusted domain centralizes the security login with the domain controller. Standard security provides a complete security system that is entirely separate from Windows NT server login. Integrated security allows all Windows NT users to be entered automatically into the SQL Server security model.
Like many of the other BackOffice servers, SQL Server has specific security needs that the standard Windows NT login security does not support. Separate table- and column-level security may be set through the enterprise manager. One of the primary shortcomings of the SQL Server security framework is the inability to create groups with table-level access privileges. In SQL Server deployments by my company, we were able to write stored procedures that establish new users and set the appropriate access privileges.
To create an automated solution to this problem, you can use the SQL Server object model for control through any language capable of OLE automation. The entire SQL Server engine can currently be controlled through the SQL-DMO object model. Using COM, programs can be created to create new users, set security levels, and so on.
COM is the middleware that will connect all the BackOffice products in the future. With the upcoming release of Microsoft NT Server 5.0, the operating system itself will be object based. Because MTS executes COM objects, Microsoft has begun to add security features to the COM framework.
If you begin providing access to your database through either local objects or distributed objects, you must begin considering the COM security model. COM security on Windows 95 provides no explicit protection to COM objects beyond those supplied by traditional login access. Windows NT, however, allows for specific launch- and call-security on components installed to the client system. Launch security, also known as activation security, provides launch-level security so that objects cannot be instantiated that the user does not have the proper permission to use. Once the object has been created on the client, call security determines which parts of the object created on the server may be accessed.
To set the launch- or call-permission levels, you must modify the Registry. Launch permissions are defined in the LaunchPermissions key. The call-level security allows specific interfaces to be restricted depending on user permissions. Call-level security is much more fine grained, but it must be designed into the actual objects that have been created.
Security settings may be determined for individual objects. The promises of three-tiered distributed systems may finally come true with the widespread use of MTS. Because almost the entire BackOffice suite will be controlled by COM and DCOM, objects can be created to interface with any particular server.
MTS provides both declarative and programmatic security for objects that will be marshaled across the DCOM interface. Individual components installed in MTS are stored in packages, so sets of components may be loaded at one time. A package may consist of only a single object or it may contain many objects that have related functionality. Security for access and individual method calls are set at the package level.
Most security settings are defined by setting roles through the Windows NT security system. At the package level, you can add roles that can represent users or groups defined through traditional Windows NT security. Once a role is added (one for each specified user or group), you can define the level of access down to the interface level of a component. Access can be defined at three levels: the package itself, the component, and the interface.
Three-tier systems now require two levels of security: Users must be authenticated to talk to the objects, and the objects must be authenticated by the database. Therefore, be careful giving permissions to objects that allow access for their designated roles. By providing powerful access outside the database server itself, you increase the risks of unauthorized access to secure data.
Tracking will also be more difficult if unauthorized access occurs. MTS features intelligent management of database connections, so resources are optimized. It makes sense, therefore, to consult the MTS logs for access information in addition to the traditional SQL Server logs.
IIS has an entire set of security preferences that sits on top of the Windows NT foundation. The permissions set on individual folders is inherited by both the FTP and HTTP servers included in IIS. User logins and read/write access are controlled through standard Windows NT security. However, execute permissions for particular folders must be set within IIS itself.
Make sure that any folder that needs to execute ASP files has execute privileges set in the IIS Service Manager application. If the Execute check is not set on the folder, the text of the ASP file will be displayed. This could potentially have serious security implications if authors of ASP files were to hard-code the user name and password information into the file.
Also make sure that user accounts have the Log On Locally permissions (found in the User Manager) set to access the information. The term "Log On Locally" may be confusing, but this setting is required for remote access. Otherwise users will not have remote login access.
IIS uses ASPs to allow execution of either VB Script or JavaScript code. By default, an ASP file has no access to ActiveX or OLE controls. ASPs can only access in-process ActiveX DLLs. This access is provided for memory management and security reasons. It is possible to turn this limitation off by changing a registry setting that is detailed in the IIS documentation.
Managing the FTP server is far simpler than the HTTP options. The FTP server also lets you create a folder that matches a particular Windows NT UserID. When people log into the FTP server, they will be automatically placed within the directories that match their usernames, if they are present. Make sure you create a folder with the name "anonymous" to which any anonymous logins will be automatically directed.
Realize that whenever a connection to your data occurs through the Web, usernames and passwords entered into a standard HTML form are transmitted in ClearText form. This means that the data is unencrypted and may easily be intercepted and decoded. To prevent this from occurring, make sure that any password entry pages are protected by the Secure Sockets Layer (SSL) encryption key. Keys may be obtained from a certificate authority such as VeriSign (www.verisign.com) or may be created for private use within an organization by a certificate server such as Netscapeıs Certificate Server.
ASPs create a session. Unlike traditional database connections, the Webıs HTTP format is by definition sessionless. There is no explicit login/logout, nor is there a connection between one page access and the next. ASP includes an implicit session by maintaining variables and database connections for a given time period based on the common access from a particular IP address. A data source may be opened through ODBC with password entry. A session prevents a user from having to log in repeatedly every time a new Web page is loaded.
Within a session, you can create a recordset cursor to an ODBC connection. The DBA must create a login HTML form, because unlike traditional ODBC generic access, a default form is not presented. If a default login is attempted, an HTML error page will return information about the failed login.
Exchange Server has security integrated with Windows NT. Unlike SQL Server, however, user accounts are not automatically created from the Windows NT user file. When a mailbox is created with a username that exists within Windows NT, the administrator is prompted to link the accounts.
The Exchange Server also provides additional features for checking email and folder access. It allows various public and private folders to be password protected for features such as browse, write access, and delete access, among others.
To use the Exchange Web access module, the browser must support the standard Windows NT challenge/response security, just like the HTTP server for secure folders. Internet Explorer supports Windows NT challenge/response, Netscape Navigator doesn't. Otherwise, ClearText authorization must be used; this is extremely insecure over the Internet.
(End of online addendum.)
MSMQ is fully integrated into the Windows NT security framework. MSMQ currently suffers from the same problem as Exchange Web access: the use of ClearText for passwords. Make sure that MSMQ does not encapsulate secure information for transmission over a public network. MSMQ is generally constructed for use within an organization, rather than targeted to external information access, so security dangers are minimized only if it is used on internal networks.
ODBC will be used for most database access on BackOffice servers. ODBC provides a great deal of the database access to SQL Server. This includes access through the ASPs, ADOs, MTS, and the Microsoft Exchange Server database bridge. In addition, most of the log files, including those available for the IIS, may be written to an ODBC data source for examination and analysis.
ODBC provides no security of its own, but rather provides the simple front end that passes the login username and password through to the data source itself. If youıre using an AS/400 through the SNA Server, you can obtain database access via the ODBC capabilities available from IBM using the AS/400 database connector.
For all the shared models, administration of the various security features remains in each separate server administration program. Adding a single user with access to email, distributed objects, and data sources can mean executing up to eight separate administration programs. From a security standpoint alone, having so many different administrative applications creates numerous opportunities for mistakes (for example, out-of-date accounts remaining active).
Microsoft is moving toward decreasing costs of such problems with the new security objects that will be available in Windows NT 5.0. It is also possible that the Windows Scripting Host will provide a solution by letting you create scripts that will set up all the necessary permissions. Until the time comes when these security settings can be centralized in a single executable or an environment with add-ins, coordination will remain difficult for organizations.
Right now, an additional problem remains in that the objects and object proxies (required for DCOM object calls) must be separately installed on each machine. The Zero Administration Windows (ZAW) kit is Microsoftıs newest technology to bring down the total cost of ownership. ZAW should remove the problem of individual object installation that currently makes administration difficult when using DCOM for distributed computing.
ZAW integrates with SMS server, which most DBAs will leave to the general system administration specialists. However, SMS in conjunction with ZAW will now allow two functions crucial to information systems deployment: configuration of ODBC data sources and installation and registration of ActiveX objects on a remote OLE registry. This allows installation of the MTS remote proxy stubs.
Also, creating the ODBC connection as a File Data Source with ODBC version 2.5 and later simplifies configuration of a data source by pointing to the appropriate server. Using the ZAW kit allows you to install the ODBC references you need for your particular clients across an entire system.
Windows Scripting Host (WSH) included in Windows NT 5.0 and Windows 98 allows a whole new level of COM access. WSH is an engine that executes VB Script or JavaScript to control the operating system itself. Like the older BAT files in DOS, Scripting Host allows a program to control the system for configuration, file access, and other low-level functions. With Microsoft exposing most of the security frameworks to OLE automation (such as the SQL-DMO), perhaps a unified security interface will not be necessary.
BackOffice integrates all the servers fairly well around Windows NT services. This integration provides one of the key advantages of single-vendor solutions over open standards-based vendors. By using a variety of the BackOffice servers, you can achieve data access via mediums as diverse as distributed objects, email, or the Web. Operations can be activated instantly or queued for later processing using the MSMQ server for distribution throughout the rest of the BackOffice servers. This great flexibility is one of the reasons BackOffice is showing such tremendous growth in the information systems sector; however, managing integrated data access and security is not yet as easy as it should be.
 Figure 1. Integration of an IIS Web page request to create an HTML file containing retrieved data. |
 Figure 2. Client access of SQL Server through an object stored on the Microsoft Transaction Server. |
| Windows NT Server | SQL Server | Transaction Server | Exchange Server | SNA Server | Internet Information Server (IIS) | MSMQ | |
|---|---|---|---|---|---|---|---|
| Integrated Windows NT Security | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| Missing from integrated security | n/a | Table and column-level permissions | Role, Package, Component, and Interface | Folder level security | AS/400 login | Folder Execute privileges, SSL Security, Browse access | n/a |
| Server->ODBC Access | n/a | Yes | Yes | Yes | Yes | Yes | n/a |
| ODBC->Server access | n/a | Yes | No | Yes | No | No | No |
| COM Access to the server itself | (in Windows NT 5.0) | Yes, through SQL-DMO | Yes, through MTS object | Yes, through OLE Messaging | No | No | No |