A Corporate Technology Team Publication May/June 1993 Volume 3, No. 2

In This Issue

News You Can Use 1

Under the Bonnet 2

Conversion of NTFS Long Filenames to FAT Filenames 11

Microsoft SQL Server Administration: Managing the Split in Responsibilities
Between System Administrator and Database Administrator 12

Understanding Windows NT POSIX Compatibility 17

Microsoft Windows NT Beta March 1993
Hardware Compatibility List Update 26

Microsoft OS/2 Version 1.3 Hardware Compatibility List 47

Corporate Technology Team Tech Notes 54

NETNews distributes up-to-the-minute technical information about Microsoft networking software. Copies of this publication are also available on SmartPages. Please send suggestions and comments to Dan Shelly.
NETNews is produced with Word for Windows and Windows 3.1.

Microsoft and MS-DOS are registered trademarks, and Windows, Windows NT, and Win32 are trademarks of Microsoft Corporation in the U.S.A. and other countries.
Other known trademarks are noted in text and are the property of their respective owners.

News You Can Use

By Dan Shelly, Corporate Technology Team

Welcome to the May/June edition of NETNews for 1993. This spring has been extremely busy for the Corporate Technology Team. We didn’t forget you, but creating and delivering Microsoft Windows NT Inside Track training kept us from publishing our March/April issue. The good news is that we are now back and ready to continue delivering technical information to help you better use Microsoft’s networking products. Perhaps even better news is that every issue of NETNews will now be included on Microsoft’s new TechNet CD. So if you are looking for a sure way of receiving every issue of NETNews, you should sign up for TechNet today! The most exciting news of course is that as this issue heads off to press, we are heading off to the launch of Windows NT. So you can look forward to a continued series of articles on various aspects of the Windows NT operating system in the months to come.

As always, if you have any suggestions, comments, or an article that you would like to submit for publication, please feel free to contact us at:

NETNews
c/o Corporate Technology Team, 1/1
One Microsoft Way
Redmond, WA 98052-6399

A special thanks this month to Shawn Aebi who submitted an article on Microsoft SQL Server Administration.

Under the Bonnet

By Glen Clark, Corporate Technology Team

This month we will be looking at the major components in the networking architecture and how they interoperate.

A significant difference between the Microsoft Windows NT operating system and OS/2 1.x and even 2.x is that networking capabilities were built into Windows NT at the ground level. With MS-DOS, Windows (except for Windows for Workgroups), and OS/2, networking was added on top of the operating system. This meant that the NOS (Network Operating System) designers for Windows NT had the opportunity to design their components within the context of an operating system platform which was still being defined. It also meant that the network team did not have to duplicate the efforts (or code) of the kernel team, and vice versa.

The original designers had three things in mind. First, Windows NT should provide integral, application-transparent networking services. Basic file and print sharing and using services should be part of every Windows NT machine. Second, Windows NT should provide a platform for distributed applications. Application-level interprocess communication (IPC) should be provided for the development of client/server-type applications. Third, the designers recognized that the network market was enormous and growing larger. Windows NT should provide an expandable platform for other network components. All of these goals were to be met within the context of the other major goals of Windows NT, such as portability and security.

µ §

Figure 1

To understand networking on Windows NT, we need to understand the architecture. As with other architecture components of Windows NT, the networking architecture is built of layers. This helps provide expandability—for others to add functions and services. We are going to look at the model from the bottom up. The layered architecture used by Windows NT mirrors the OSI reference model quite well. The presentation layer is thin to non-existent, depending on the protocol and system used, however.

µ §

Figure 2

At the bottom of the networking architecture is the network adapter card device driver. Windows NT currently supports device drivers written to Network Device Interface Specification (NDIS) 3.0. NDIS 3.0 is based on NDIS 2.0, which was the standard used by OS/2 NDIS device drivers. NDIS 3.0 conforms to the device driver standards established for Windows NT. There is a C-call interface; drivers have access to the helper routines; and drivers are 32-bit, portable, and multiprocessor safe. By providing a standard interface, NDIS permits the high level protocol components to be independent of the network interface card.

Unlike previous NDIS implementations, Windows NT does not need a PROTMAN (Protocol Manager) module to link the various components at each layer. This is accomplished through the information in the registry and a small piece of code, or wrapper, around all of the NDIS device drivers. The NDIS wrapper provides a uniform interface between protocol stack drivers and NDIS device drivers. It also contains supporting routines which makes the development of an NDIS driver easier.

µ §

Figure 3

Above the NDIS wrapper are the transport protocol device drivers (see Figure 3). Windows NT ships with three transports. NetBEUI provides compatibility with existing LAN Manager, LAN Server, and MS-Net installations. Transmission Control Protocol/Internet Protocol (TCP/IP) provides a popular routable protocol for wide area networks. And Data Link Control (DLC) provides an interface for access to mainframes and printers attached to networks.

The DLC protocol is not a full transport protocol by OSI definitions. Its top interface is at the data link control (DLC or, in IEEE terms, the Link Layer Control (LLC)) layer. DLC is used for fast, simple connection and connectionless conversation. The DLC protocol is used to communicate with network attached printers such as the HPIIISi and to communicate with some mainframe computers. It is not possible to establish a client/server or peer-to-peer type session—for file sharing and using, for example—over the DLC protocol alone.

The NetBEUI protocol is provided with Windows NT to maintain connectivity to existing LAN Manager and MS-Net–based networks. The NetBEUI protocol is fast, with low overhead (number of extra bytes) per frame of data transmitted. The protocol cannot be routed, however. Thus, NetBEUI is most appropriate in single sub-net (continuous network) networks.

µ §

Figure 4

When we talk about NetBEUI it is important to understand that we are talking about the transport layer protocol, not the programming interface NetBIOS (see Figure 4). Earlier implementations on MS-DOS and OS/2 provided the programming interface as part of the transport’s device driver. There is nothing wrong with that, but in the Windows NT implementation we have separated the programming interface (NetBIOS) from the transport protocol (NetBEUI) to increase flexibility in the layered architecture. Separating the two allows us to use the same NetBIOS driver code for multiple transports such as NetBEUI and TCP/IP.

µ §

Figure 5

Finally, we want to look at TCP/IP (Figure 5). TCP/IP is implemented slightly differently from what we have seen with NetBEUI or DLC. Instead of being a single device driver bound directly to the NDIS device driver, TCP/IP resides “inside a wrapper.” This wrapper is called Streams (or the Streams driver). Calls to the TCP/IP transport protocol driver must first go through the upper layer of the streams device driver, and then to the NDIS device driver via the lower end of the streams device driver.

Streams is a significant departure from the way protocol stacks were developed for MS-DOS and OS/2. There are several reasons for the use of the streams mechanism. Streams makes it easier to port existing protocol stacks to Windows NT. Streams also encourages protocol stacks to be organized in a modular, stackable style, thus moving closer to the original vision of the Open Systems Interconnection (OSI) model.

The Transport Driver Interface (TDI) provides a common interface for file system and I/O manager processes to communicate with the various network transports. It is a very “thin” layer. There is little code actually involved with the TDI. The TDI interface is based on 32-bit-wide handles. This increases the connection capacity between upper layers and protocols such as NetBEUI which traditionally only allows an 8-bit-wide handle (LSN - Local Session Number). The previous edition of NetNews contained detailed information on how the TDI is used to break the 255 session barrier.

The first design goal of the networking system is to support file and print sharing and using. This is accomplished by two modules, LANMANWorkstation and LANMANServer. These two components, with the help of several more we will identify, provide most of the functionality of the OS/2 version of LAN Manager available today. Both of these modules execute as 32-bit services.

µ §

Figure 6

The LANMANWorkstation module is really in two pieces (see Figure 6). The LANMANWorkstation component provides the user-mode interface. The other component is the RDR, or Redirector. This component is a File System Driver (FSD) that actually does the interaction with the lower layers of the protocol stack.

Multiple UNC (Universal Naming Convention) Provider (MUP) is an interesting entity that runs in kernel-mode memory. The most productive way of thinking of MUP is as a resource locator. The types of resources it locates are UNC names. A UNC name is a naming convention for describing servers, and sharepoints on those servers, on a network. UNC names start with two backslashes (\\) followed by the server name. All other fields in the name are separated by a single backslash (\). A typical UNC name would appear as:

\\server\share\subdirectory\filename

Not all of the components of the UNC name need to be present with each command. \\server is sufficient to find a server to get a list of its sharepoints.

Unlike the NDIS and TDI boundary layers, MUP is actually a program. NDIS and TDI simply define ways for a component on one layer to communicate with another over specifically defined paths called binds. MUP, too, has defined paths to redirectors or, as the name implies, UNC providers. The problem is that for any UNC name, MUP is not sure which of potentially many different UNC providers the command should go to.

MUP receives commands from applications that contain UNC names. If this is a UNC name that MUP has not seen in the last 15 minutes, it will send the UNC name to each of the UNC providers that are registered with it. This is why MUP is a prerequisite of LANMANWorkstation. One of the first tasks the LANMANWorkstation did when initializing was register with MUP. The redirector with the highest registered priority response that claims it can establish a connection to the UNC, will be passed the command and the security context of the application generating the request.

One might ask, why a MUP? LANMANWorkstation is the only UNC provider. This is true today. Recall, however, that one of the major design goals for networking in the Windows NT environment was to build a platform upon which others can build. MUP is a vital part of allowing multiple redirectors to co-exist in the machine at the same time.

LANMANServer is much like the LANMANWorkstation module. It is a service that runs in the lmsvrcs process. Unlike the workstation component, it is not dependent on the MUP service, since the server is not a UNC provider. It doesn’t attempt to connect to other machines, but it is connected to by other machines. Like LANMANWorkstation, it is composed of two parts: the LANMANServer component and the SRV component. The SRV component handles the interaction with the lower levels and also directly interacts with the other file system devices to satisfy command requests such as file read and write.

In addition to the workstation and server services from LAN Manager, a number of other services were ported over. These are: the Alerter, the Messenger, the Browser, and the Replicator. The Alerter is used to forward alerts generated on the local machine to remote computers or user names. The Messenger receives messages and alerts, and displays them on the screen in the form of a message box. The browser is used to collect information about the machines in this domain or workgroup, and to inform users of these facilities when asked. The information collected by this facility is most obvious in the File Manager when attempting to connect to a new drive. Finally, the replicator service permits the automatic copying of a directory from one machine to another. The source of the data is said to be on an export machine while the target is an import machine. A Windows NT Advanced Server can be either an export machine or an import machine, or both simultaneously. A Windows NT Server can only be an import machine.

So far we have built up to the redirector and server levels, and accomplished almost all of the design goals. One piece remains, however. Above the redirector and server components live the applications. Like our other layers, we want to provide them with a single unified interface to develop to, independent of the lower-layer services. This is done through two mechanisms. We have already looked at the first—MUP. The other is the MPR, or Multi-Provider Router. Applications that make I/O (Input/Output) calls that contain UNC names are directed to the MUP, where the appropriate UNC provider or redirector is located.

The MPR is much like the MUP. This layer takes in WNet commands, finds the appropriate redirector based on the handle, and passes the command to that redirector for communication onto the network. In addition to I/O calls such as Open and Close, Win32 contains a set of APIs called the WNet API. These are APIs that were ported over from Windows 3.1 network calls. Most of these calls deal with establishing remote connections. With these commands and the standard I/O commands, an application can do almost all of the networking functions needed.

The goal of distributed computing is to divide the computing task into two sections. One section runs on the client’s workstation, something that may not take a great deal of computing power but would require a lot of network bandwidth. This section includes operations such as screen graphics, mouse movements, and keyboard functions. The other section of the process requires large amounts of data, number crunching, or specialized hardware. This section includes operations such as database lookups and updates, or mainframe data access. Central to the theme is that there is a connection between the client and the server at a process-to-process level that allows data to flow in both directions. There are a number of different ways to establish this conduit; we are going to discuss the six different mechanisms that Windows NT provides.

The six IPC mechanisms provided by Windows NT are: named pipes, mailslots, NetBIOS, Windows Sockets, Remote Procedure Calls (RPC), and Network Dynamic Data Exchange (NetDDE). Named pipes and mailslots provide backward compatibility with existing LAN Manager installations and applications. This is also true of the NetBIOS interface. Windows Sockets is a Windows-based implementation of the widely used sockets programming interface created by the University of California at Berkeley. RPC is compatible with the Open Software Foundation/Distributed Computing Environment (OSF/DCE) specification for remote procedure calls. NetDDE allows standard DDE connections to be redirected across the network as was possible with Windows for Workgroups.

Named pipes and mailslots are implemented slightly differently than the other IPC mechanisms. They are actually written as file systems (FS). Thus, in the registry you find an MSFS and an NPFS. As file systems they share common functionality with the other file systems, such as security. In addition, local processes can use named pipes and mailslots with other processes on the local machine without going through the networking components. Remote access to named pipes and mailslots, as with all of the file systems, is accomplished via the redirector.

Named pipes are based on the OS/2 API set. Most of the calls, however, have been ported to the Win32-based API set. Additional asynchronous support has been added to the named pipes to make support of client/server applications easier. Because named pipes is a standard file system, it can take advantage of the cache manager. This can be used to improve the performance of certain types of named pipe applications. Specifically, the cache can be used with character mode pipes to buffer “outbound” traffic for a number of characters or a number of seconds. This can improve performance by reducing the number of frames (and network overhead) generated.

A new feature added to named pipes is “Impersonation.” In impersonation the server can change its security identity to that of the client on the other end. This is done with the ImpersonateNamedPipeClient() API. Assume you have a database server system that uses named pipes to receive read and write requests from clients. When a request comes in, the database server program can impersonate the client before attempting to perform the request. So even if the server program does have authority to perform the function, the client may not, and the request would be denied.

The mailslot implementation in Windows NT is not the full OS/2 LAN Manager implementation. Where in LAN Manager there are first- and second-class mailslots, in Windows NT only second-class mailslots exist. Mailslots provide connectionless messaging, basically broadcast messaging. Delivery of the message is not guaranteed, although the delivery rate on most networks is quite high. It is most useful for discovering other machines or services on a network, or for wide-scale notification of a service. The use of mailslots should be contained as much as possible, however. Each mailslot transmitted is received by each machine on the local area network and processed at least to the degree that determines if the message is to be received or not. This can cause workstations to slow down. In addition, applications designed using mailslots are probably limited to local area network implementations only, since most wide area networks do not propagate broadcast messages across bridges or routers.

The use of NetBIOS as an IPC mechanism has been around since the introduction of the interface in the early 1980s. From a programming aspect, however, higher-level interfaces such as named pipes and RPC are superior in their flexibility and portability. The NetBIOS entry in the registry defines a common interface point for multiple possible transport protocol providers.

The sockets interface for TCP/IP was created by the University of California at Berkeley in the early 1980s. Since then it has become a very popular interface for developing distributed applications in the TCP/IP and UNIX environments. Microsoft, in cooperation with several other software vendors, developed the Windows Socket API set to accomplish two things: (1) to migrate the sockets interface into the Windows and Windows NT environments, and (2) to help standardize the API set for all platforms. The Windows Socket interface for Windows NT is a layer above the TCP/IP.

The RPC mechanism is unique in that it uses the other IPC mechanisms to establish communications between the client and the server. RPC can use named pipes, NetBIOS, or TCP/IP Sockets to communicate with remote systems. If the client and server are on the same machine, it can use the LPC (Local Procedure Call) system used to transfer information between processes and subsystems. This makes RPC the most flexible and portable of the available IPC choices.

Much of the original RPC work was started by the SUN computer company and carried forward by the Open Software Foundation (OSF) as part of their Distributed Computing Environment (DCE). The Microsoft RPC implementation is compatible with the OSF/DCE standard RPC. It is important to note that it is compatible but not compliant. Compliance in this situation implies that one started with the OSF source code and worked forward. For a number of reasons, Microsoft-developed RPC started with the OSF specification but not the source code. The RPC mechanism is completely interoperable with other DCE-based RPC systems such as the ones for HP® and IBM®/AIX® systems.

Looking at how RPC works, we first need to understand what RPC is attempting to accomplish. A program can be viewed as having a “backbone” and a series of “ribs” spanning off the backbone. The backbone is the mainstream logic of the program, which should rarely change. The ribs are the procedures the backbone calls on to do work or functions. This is simply another way of looking at “structured” programming. In traditional programs, the ribs were “statically” linked to the backbone—that is, they were linked and stored in the same executable module.

With OS/2 and Windows, the concept of Dynamic-Link Libraries (DLLs) is used. With DLLs the procedure code and the backbone code are in different modules. This allows the DLL to be modified or updated without changes to or redistribution of the backbone modules.

µ §

Figure 7

RPC takes the concept one step further and places the backbone and the ribs on different machines. Doing this raises alot of issues, such as data formatting, integer byte ordering, locating the server that contains the function, and establishing the communications mechanism being used (see Figure 7).

When put together we have RPC. The client application was developed with a specially compiled “stub” library. The client application thinks it is calling its own subroutines. In reality these stubs are transferring the data and the function down to a module called the RPC Runtime. This module is responsible for finding the server that can satisfy the RPC command. Once found, the function and data are sent to the server, where they are picked up by the RPC Runtime module on the server. The server piece then loads the needed library for the function, builds the appropriate data structure, and calls the function. The function thinks it is being called by the client application. When the function is completed, any return values are collected, formatted, and sent back to the client via the RPC Runtime modules. When the function returns to the client application, it either has the appropriate returned data or it has an indication that the function failed in stream.

Finally at the top of the mountain! From the application viewpoint there are two sets of commands that can cause network traffic: any I/O command, such as Open, which contains a UNC name, and WNet commands. UNC commands are sent to the MUP in the Windows NT kernel, where it finds a UNC provider or redirector that can make a connection to the specified UNC name. WNet commands are passed to the MPR, which passes the request to each redirector in turn until one is found that can satisfy the request. One machine gains access to another machine via a redirector. Windows NT ships with a redirector that allows connection to LAN Manager, LAN Server, and MS-Net servers. This redirector communicates to the protocol stacks to which it is bound via the TDI layer. The TDI layer is a boundary layer between the file system modules and the network protocol stacks. Boundary layers are used to provide a unified platform for others to develop “plug-and-play” components. Windows NT ships with three protocol stacks: TCP/IP, NetBEUI, and DLC. TCP/IP is wrapped in the Streams driver. Streams will make porting other protocol stacks to Windows NT easier. Protocol stacks communicate with the network interface card (NIC) via an NDIS device driver. NDIS 3.0 provides another boundary layer that makes interoperability between components at different layers easier. In addition to providing file and print sharing capabilities, Windows NT provides five mechanisms for building distributed applications. Named pipes, mailslots, NetBIOS, Sockets, and RPC can all be used. The most portable is the RPC mechanism. RPC uses other IPC mechanisms to transfer functions and data between client and server machines.

Through this process we have seen how the three main goals of the design for networking within Windows NT have been accomplished. First, file and print services are provided through the default server and workstation components. These default server and workstation modules are the progression of the LAN Manager technology into the Windows NT environment and provide backward compatibility for your existing LAN Manager-based networks. Second, we have seen support for client/server applications in the rich availability of IPC mechanisms such as named pipes, Windows sockets, and of course RPCs. And finally, we have seen the design of an expandable architecture based on standard boundary layers which others can (and are) building upon.

If there are any comments on this article, or suggestions for future articles, please contact me:

CompuServe: 73750,1607
FAX: (206) 883-8101

Address: Glen Clark
One Microsoft Way
Redmond, WA 98052-6399
USA

Conversion of NTFS Long Filenames to
FAT Filenames

By Caribe Malo, Corporate Technology Team

The file allocation table (FAT) filename is an attribute that is created automatically when a file is created, and is resident within the associated file record. The following rules are used to compute the FAT filename. First, all illegal characters in the NT File System (NTFS) name are converted to underscores (_). Then all spaces are removed from the name, as well as all periods up to the last period. The name is then truncated to six characters before the period, and three characters after the last period in the long filename, if there is one. A tilde (~) and a sequence number are then added. Single-digit numbers are used, first starting with 1, then double, and so on, until a unique name is produced. What this means is that a naming strategy is to make the first eight characters of the filename express the filename as uniquely as possible. The last period is important. If you are using files with long names on a shared NTFS volume from a Windows workstation that uses file association to start the appropriate application, you will want to make sure that the extension you expect follows the last period.

TestOneOnOne.TXTG000.00 è TESTON~1.00

Long filename Short filename

If you are not sure what short filename will be produced from the long name, you have the ability to disclose the full file details either from the File Manager or by typing in dir /x from the command prompt. Both of these methods will tell you what short filename will be produced. The Win32 API can also be queried for this information.

Microsoft SQL Server Administration:
Managing the Split In Responsibilities between System Administrator and Database Administrator

By Shawn Aebi, Systems Engineer, Microsoft

This article explains the differences in capabilities between the System Administrator and the Database Administrator in Microsoft SQL Server. It discusses the existing permissions heirarchy and identifies areas where the permissions differ between the administrators.

General Description

There are three types of special users who can administer and control SQL Server: System Administrators, Database Owners, and Database Object Owners.

The System Administrator (SA)

When SQL Server is installed, the SA is the owner of the master database.

The SA has the following privileges by default:

· Installing SQL Server

· Creating devices and databases

· Managing and monitoring the use of disk space, memory, and connections

· Transferring data in and out of SQL Server databases

· Backing up and restoring databases

· Diagnosing system problems

· Tuning SQL Server to achieve best performance

The SA account operates outside the protection system—no permission checking is done for SA operations. The SA can and should be considered the super-user, given permission to use, alter, and delete devices, databases, and objects throughout the system. This unique permission set is required to allow for repair of structures.

The Database Owner (DBO)

The SA grants the DBO authority to create the database with the GRANT statement. The DBO has full privileges within the database.

The DBO can:

· Create and alter their database (if given permission by the SA)

· add users to their database

· use the GRANT statement to extend permissions within the database

· set up groups of users

The DBO cannot perform any of these operations on other databases unless explicitly given similar authority by the SA.

The Database Object Owner (DBOO)

Database objects are tables, indexes, views, defaults, triggers, rules, and procedures. The user who creates a database object is the DBOO. The DBO must grant permission to the user to create these objects.

Login and User Accounts

Users of SQL Server databases need both a login ID (to access the server) and a user ID (to access each database). The procedure to add users to SQL Server databases and to grant permissions can be performed entirely by the SA or split between the SA and the DBO according to the following rules:

· Only the SA can add a login ID.

· Either the SA or the DBO can add a user ID.

· Multiple people can be assigned DBO privileges through the use of aliasing.

· Only the SA or DBO can grant statement permissions to other users.

Command	Description	Authority
sp_addlogin	adds a login ID to the SQL Server registry, add password and default db	SA only
sp_adduser	adds a user ID to the database	SA/DBO
sp_addalias	ties a login ID to an alias	SA/DBO
sp_addgroup	adds a group to a database	SA/DBO
sp_dropuser	drop a user ID from the database	SA/DBO
sp_droplogin	drop a login ID from the database	SA only
sp_dropalias	drop a user ID from an alias	SA/DBO
sp_dropgroup	drop a group from the database	SA/DBO
sp_changedbowner	change the DBO	SA only
sp_defaultdb	change a login ID default database	SA/DBO/Users
sp_password	change a users password	SA/DBO/Users

The following table groups Transact-SQL commands by responsibility:

Responsibility/commands	Ability to extend/limit permissions
System Administrator:
CREATE DATABASE	YES
DISK INIT	NO
DISK MIRROR	NO
DISK REFIT	NO
DISK REINIT	NO
DISK REMIRROR	NO
DISK UNMIRROR	NO
KILL	NO
RECONFIGURE	NO
SHUTDOWN	NO
Database Owner:
ALTER DATABASE	NO
CHECKPOINT	NO
CREATE DEFAULT	YES
CREATE PROCEDURE	YES
CREATE RULE	YES
CREATE TABLE	YES
CREATE VIEW	YES
DBCC	NO
DUMP DATABASE	YES
DUMP TRANSACTION	YES
GRANT	NO
LOAD DATABASE	NO
LOAD TRANSACTION	NO
REVOKE	NO
SETUSER	NO
Database Object Owner:
ALTER TABLE	NO
CREATE INDEX	NO
CREATE TRIGGER	NO
DELETE	NO
INSERT	YES
READTEXT	YES
SELECT	YES
TRUNCATE TABLE	NO
UPDATE	YES
UPDATE STATISTICS	NO
WRITETEXT	YES

Note that each of these groups has the capability to drop objects which they are allowed to create. That is, SAs can drop devices, DBOs can drop databases, and DBOOs can drop tables and indexes. These permissions cannot be extended or withdrawn.

Public commands
sp_configure	All can run, only SA can change
sp_dboption	All can run, only SA/DBO can change
sp_monitor	All can run
sp_helpdb	All can run
sp_logdevice	All can run, only SA/DBO can change
sp_addsegment	All can run, only SA/DBO can change
sp_who	All can run
SET commands	All can run
BEGIN/COMMIT/ ROLLBACK TRANsaction	All can run
bcp	With INSERT permission, all can run
isql	With access to program, all can run

System Tables

There are 13 system tables which are monitored by the SA, and 13 system tables created for each database, which can be monitored by the DBO.

Master-Only System Tables

SYSDATBASES	SYSLOGINS	SYSPROCESSES	SYSCONFIGURES
SYSCURCONFIGS	SYSDEVICES	SYSCHARSETS	SYSUSAGES
SYSLOCKS	SYSMESSAGES	SYSLANGUAGES	SYSSERVERS
SYSREMOTELOGINS

Database System Tables

SYSOBJECTS	SYSUSERS	SYSLOGS	SYSSEGMENTS
SYSALTERNATES	SYSCOLUMNS	SYSCOMMENTS	SYSDEPENDS
SYSINDEXES	SYSPROCEDURES	SYSTYPES	SYSKEYS
SYSPROTECTS

In summary, for the projects we are currently investigating, SQL Server offers a powerful step in authority between System Administrators and Database Administrators.

· System Administrators must install SQL Server and perform initial structuring (create the OS/2 volumes, database devices).

· System Administrators must create the login IDs for anyone logging on to that SQL Server.

· All Database Administrators must have user ids in the master database.

· Database Administrators can build their own databases, assign their own users, and set their own permissions.

· Database Administrators can dump and load their databases and associated logs.

· Most tuning can be done by the Database Administrator. Memory, fillfactor, and procedure cache size are controlled with the sp_configure command which only the SA can run.

· Only System Administrators can kill processes or shut down the server.

· After each database creation or drop, it is recommended that the master database be dumped. This can only be done by the system administrator.

Assigning Primary and Secondary Responsibilities

What follows is a matrix which each project team should build for themselves. There should be an understanding, either oral or written, of who will take actions for certain tasks. This will save countless finger pointing and speed up troubleshooting time. In many cases, the SA and DBA could be the same person. However, in tightly secure environments or on large projects, this boundary needs to be carefully constructed and audited.

Responsibility Matrix

Task	SA	DBA
Server setup, configuration, maintenance of server parameters	P	S
Create/Drop Devices	P	S
Create/Drop Segments	S	P
Create/Drop Logins	X
Create/Drop/Alter Databases; add users	S	P
Create/Drop Database Objects (tables, indexes, views, procs)		X
Grant/Revoke DB Objects/Statement Permissions		X
Database Load/Refresh		X
Database Backup
Master DB	X
Other DB	P	S
Database Recovery
Master DB	X
Other DB	S	P
Database Tuning	S	P
Server Tuning	P	S
Server/DB Monitoring	P	S

P = Primary, S = Secondary, X = Exclusive

I hope that you have found this summation of the varying SQL Server administrative roles informative. Most of this information comes from the Microsoft SQL Server System Administrators Guide. I have found this to be an excellent source of information from which to learn more about the administrative roles and responsibilities for SQL Server.

Understanding Windows NT POSIX Compatibility

By Ray Cort, Corporate Technology Team

This article will discuss the following topics:

· What is POSIX?

· Implementation in Windows NT

· Application conformance

· Verification and conformance

· Windows NT POSIX files

· Running POSIX applications

· Restrictions on POSIX applications

What is POSIX?

POSIX stands for Portable Operating System Interface for computing environments. POSIX began as an effort by the IEEE community to promote the portability of applications across UNIX environments by developing a clear, consistent, and unambiguous set of standards. However, POSIX is not limited to the UNIX environment. It can also be implemented on non-UNIX operating systems, as was done with the IEEE Std 1003.1-1990 (POSIX.1) implementation on Virtual Memory System (VMS), Multiprogramming Executive (MPE), and the Conversion Technology Operating System (CTOS). POSIX actually consists of a set of standards that range from POSIX.1 to POSIX.12.

As the following table shows, most of these standards are still in the proposed state. This article deals with the Windows NT implementation of a POSIX subsystem to support the international ISO/IEC IS 9945-1:1990 standard (also called POSIX.1). POSIX.1 defines a C language source code-level application programming interface (API) to an operating system environment.

Family of POSIX standards

Standard	ISO Standard?	Description
POSIX.0	No	A Guide to POSIX Open Systems Environment. This is not a standard in the same sense as POSIX.1 or POSIX.2. It is more of an introduction and overview of the other standards.
POSIX.1	Yes	Systems API (C Language)
POSIX.2	No	Shell and tools (IEEE-approved standard)
POSIX.3	No	Testing and verification
POSIX.4	No	Real-time and threads
POSIX.5	Yes	ADA language bindings to POSIX.1
POSIX.6	No	System security
POSIX.7	No	System administration
POSIX.8	No	Networking Transparent file access Protocol-independent network interface Remote Procedure Calls (RPC) Open system interconnect protocol-dependent application interfaces
POSIX.9	Yes	FORTRAN language bindings to POSIX.1
POSIX.10	No	Super-computing Application Environment Profile (AEP)
POSIX.11	No	Transaction Processing AEP
POSIX.12	No	Graphical user interface

POSIX Implementation in Windows NT

The POSIX subsystem is implemented in Windows NT as a protected server. POSIX applications communicate with the POSIX subsystem through a message-passing facility in the Executive known as a Local Procedure Call (LPC).

µ §

Figure 1: POSIX subsystem in Windows NT

The POSIX subsystem, as well as each POSIX application, runs in its own protected address space which protects it from any other application that might be running on Windows NT. POSIX applications are preemptively multitasked with respect to each other and to other applications running in the system.

POSIX Conformance

For a system to be given a certificate of POSIX.1 conformance, it must meet the following requirements:

· The system must support all of the interfaces as defined in the ISO/IEC 9945-1.

· The vendor must supply a POSIX.1 Conformance Document (PCD) with their implementation as specified in ISP/IEC 9945-1.

· The implementation must pass the appropriate National Institute of Standards and Technology (NIST) test suite.

Application Compliance to POSIX.1

Many people talk about a “POSIX-compliant” application, but what does that really mean? For POSIX.1, there are four categories of compliance, ranging from a very strict compliance to a very loose compliance. The various categories are outlined in the following subsections:

Strictly-Conforming POSIX.1 Applications

A strictly-conforming POSIX.1 application requires only the facilities described in the POSIX.1 standard and applicable language standards. This type of application accepts the following:

· Any behavior described in ISO/IEC 9945-1 as unspecified or implementation-defined

· Symbolic constants

· Any value in the range permitted by ISO/IEC 9945-1

µ §

Figure 2

This is the strictest level of application conformance. Applications at this level should be able to move across implementations with just a recompilation. At the time of this writing, the only language interface that has been standardized for POSIX.1 is the C language interface. (As shown in Figure 2, a strictly-conforming POSIX application can use 110 calls from the standard C libraries.)

Applications Conforming to ISO/IEC and POSIX.1

An ISO/IEC-conforming POSIX.1 application is one that uses only the facilities described in ISO/IEC 9945-1 and approved conforming language bindings for ISO or IEC standards. This type of application must include a statement of conformance that documents all options and limit dependencies, and all other ISO or IEC standards used.

µ §

Figure 3

This level of conformance is not as strict as the previous one for two reasons. First, it allows a POSIX.1 application to make use of other ISO or IEC standards, such as Graphical Kernel System (GKS). Second, it allows POSIX.1 applications within this level to require options or limit values beyond the minimum. For example, such an application could require that the implementation support filenames of at least 16 characters. The POSIX.1 minimum is 14 characters.

Applications Conforming to POSIX.1 and <National Body>

A <National Body>-conforming POSIX.1 application differs from an ISO/IEC-conforming POSIX.1 application because this type of application may also use specific standards of a single ISO/IEC organization, such as American National Standards Institute (ANSI) or British Standards Institute (BSI). This type of application must include a statement of conformance that documents all options and limit dependencies, and all other <National Body> standards used.

µ §

Figure 4

For example, you could have a <National Body>-conforming POSIX application that used calls from a BSI-standard set of calls.

POSIX.1-Conformant Applications That Use Extensions

A conforming POSIX.1 application using extensions is an application that differs from a conforming POSIX.1 application only because it uses non-standard facilities that are consistent with ISO/IEC 9945-1. Such an application must fully document its requirements for these extended facilities.

µ §

Figure 5

This is the lowest level of conformance; almost any C program could satisfy this with the appropriate documentation.

This current release of Windows NT supports Strictly-Conforming POSIX.1 Applications, and ISO/IEC-Conforming POSIX.1 Applications. Windows NT supports the latter because only 110 of the 149 functions of standard C are part of POSIX.1, and standard C is itself an ISO standard (ISO/IEC 9899).

Conformance Testing

Windows NT is in the process of being verified for POSIX.1 conformance, and will be submitted to NIST for the Federal Information Processing Standards Publication (FIPS) 151-2 certification. FIPS 151-2 incorporates POSIX.1 as a reference standard and also requires a number of the optional features defined in POSIX.1 to promote application portability among conforming implementations. An implementation that conforms to FIPS 151-2 also conforms to POSIX.1. Note that conformance is specific to the manufacturer, hardware platform, and model number on which the implementation is tested.

Running POSIX Applications

POSIX applications can be started from a Windows NT console window (command prompt); File Manager; Program Manager; or by invocation from within another POSIX application.

The POSIX Files

The following files are required to support the POSIX subsystem and run POSIX applications:

· PSXSS.EXE, the POSIX subsystem server

· PSXDLL.DLL, the POSIX dynamic-link library

· POSIX.EXE, the POSIX console session manager

File Systems

POSIX requires a certain amount of functionality from the file system, such as the ability for a file to have more than one name (or hard links), and case-sensitive file naming. Neither FAT nor HPFS supports these features, which is another reason why a new file system was required for Windows NT. NTFS supports both hard links and case-sensitive naming. If you want to run in a POSIX-conforming environment, you need at least one NTFS disk partition on your computer.

You can run POSIX applications from any Windows NT file system. If the application does not need to access the file system, the application will run with no problems. However, if the application does require access to the file system, there is no way to guarantee that it will behave correctly on a non-NTFS disk partition.

Bypass Traverse Checking

By default, when you install Windows NT for the first time, the user right “Bypass traverse checking” is granted to everyone. This right allows a user to change directories through a directory tree even if the user has no permission for those directories.

If you want to run in a POSIX-conforming environment, you must disable this privilege for your account by using either the User Manager or User Manager for Domains tool as follows (you must be an administrator to do this):

1. Select the account.

2. Choose User Rights from the Policies menu to display the following dialog box. Be sure the Show Advanced User Rights check box is marked.

3. Specify the Bypass traverse checking right.

4. Choose Remove.

Figure 6

Printing

The POSIX subsystem itself does not directly support printing, but Windows NT supports redirection and piping between subsystems. If your POSIX application writes to stdout, and you have connected or redirected either your serial or parallel ports to a printer, you can redirect the output of a POSIX application to that printer. For example, the following sequence of commands will send the output of a POSIX application that writes to stdout, to a network printer.

NET USE LPT1: \\MYSERVER\PRINTER

POSIXAPP.EXE > LPT1:

Network Access

The POSIX.1 specification does not have a requirement for access to remote file systems, but as with any of the other subsystems, the POSIX subsystem and POSIX applications have transparent access to any Win32 remotely-connected file system.

Communicating with Other Subsystems

Windows NT supports a common command processor that can execute commands from any of the subsystem. Furthermore, Windows NT supports the piping of input and output between commands of different subsystems. So, it is possible to do the following:

Windows NT supports a common command processor that can run commands from any of the subsystems. In addition, Windows NT supports piped input and output between commands of different subsystems. For example, you could type the following command to list the contents of the current directory, then pipe the results through the more command to the console:

ls -l | more

The ls utility is implemented in the POSIX subsystem and generates output as shown in Figure 7.

µ §

Figure 7

Figure 8 illustrates how a POSIX application interacts with other components of the Windows NT operating system.

µ §

Figure 8

A certain amount of functionality can be gained by using a single command shell of Windows NT. From a programming point of view, while not elegant, it is possible to put a Win32 graphical front-end on a POSIX application by using the redirection of stdin and stdout.

Restrictions on POSIX Applications

With this release of Windows NT, POSIX applications have no direct access to any of the facilities and features of the Win32 subsystem, such as memory-mapped files, networking, graphics, or dynamic data exchange.

Further Information

For further information on the POSIX standards, contact either or both of the following resources.

For information on POSIX.1 (ANSI/IEEE 1003.1-1990, ISO/IEC 9945-1:1990):

Publication Sales
IEEE Service Center
P.O. Box 1331
445 Hoes Lane
Piscataway, NJ 08855-1331

For information on other POSIX standards:

IEEE Computer Society
Attention Assistant Director/Standards
1730 Massachusetts Avenue Northwest
Washington, DC 20036

Microsoft® Windows NT^™ Beta March 1993
Hardware Compatibility List Update

The following computers and peripherals have passed Windows NT compatibility testing as of May 1993. This list is a subset of the hardware we expect to support in the final product and was current at the time it was published. If your hardware is not listed here, contact your hardware manufacturer for more information. We have not tested every computer and/or device in all possible configurations.

Updates to this list will appear in Library 1 of the WINNT forum (GO WINNT) or Library 17 of the MSWin32 forum (GO MSWIN32) on Compuserve Information Services.

x86 Architecture Uniprocessor Computers

Any computer 100% compatible with those in the following list: