Administrating and Developing with Informix

Suppose you have some server code which needs to check whether a user has read or write access to a file...

A UNIX programmer might say: "Use stat() to get the file permissions and check them against the user and group id. I'm going to lunch. Don't wait up".

A Windows programmer on the other hand had better skip lunch and start coding.

Here are four possible ways to check whether a user has the requested access to a file on Windows. The first three use GetFileSecurity() to get a file security descriptor and varying methods toobtain the user's security credentials.

1. Use LogonUser to get a Token handle, and validate with AccessCheck()If you have a Token representing a logged on user you can call the AccessCheck() functionto validate the user access rights against a file security descriptor. If your server code has the user's password and other credentials (not defined in the example code), you could get the Tokenusing LogonUser() (error checking removed for brevity). Assuming the desired access is encoded in the flags variable:

I have found this to be the most reliable method of checking a user's file access rights, with thedisadvantage that your server needs to have the user logon details.

2. Use OpenProcessToken() to get a Token handle and validate with AccessCheck()If you have the process ID, pid, of the user's front-end process you can avoid logging on by getting a handle to the token using OpenProcessToken() as follows (the rest of the code would be the same):

// get the Token associated with the processOpenProcessToken(hProcess, TOKEN_QUERY, &hToken);CloseHandle(hProcess);

One problem I've experienced with this method is that OpenProcessToken() can fail with Access Denied on some machines and not others. I have yet to identify the root cause.

3. Use GetEffectiveRightsFromAcl() with a user SIDOne way to verify a user's access rights without a token is to get hold of the user SID using LookupAccountName() and call GetEffectiveRightsFromAcl(). Once the file security descriptor has beenobtained as above the rest of the code would do this:

A potential problem with this is that LookupAccountName() can take a long time to execute if you have a remote user from a trusted domain in a network infrastructure with many domains.

Another problem I've seen with this is GetEffectiveRightsFromAcl() failing with return code 5. I have an open support call with Microsoft concerning this problem.

Update 2/13/07: Thanks to some help from a Microsoft Escalation Engineer the specific problem has been identified as when any local user (i.e. not a domain user) calls GetEffectiveRightsFromAcl() and passes it the ACL of an unprivileged local user, the file ACLs include those for groups which contain domain groups, and at a domain level the Network access: Allow anonymous SID/Name translation setting is disabled (default setting) the function will return "Access Denied". Because of this reason using GetEffectiveRightsFromAcl() is not the recommended method to determine whether a user has access rights to a file.

4. Launch a process as the user and test accessIf you don't want to mess with all the access functions, you could could simply create a process as the user using LogonUser() and CreateProcessAsUser()and try opening a file with the required permissions. This isn't a very efficient method, and executing a command leaves your code open to malicious command injection, but it works.

ConclusionThe file access rights implementation on Windows, and its programming interface, is in my opinion a pile of pants. It probably seemed like a good idea at the time to create an access model with so much flexibility, but a flexible security system is often a misconfigured security system. Suggestionswith simpler alternatives to the above are welcome.

The term DLL hell is usually invoked with reference to conflicts between DLL versions, missing DLLs and multiple copies of DLLs. In the 14th century the poet Dante portrayed metaphysical hell as having multiple layers or circles, and in my opinion the analogy readily extends to DLL hell.

A process running on 32-bit Windows has 2GB of address space by default. Into this space it needs to fit any operating system and application DLLs it loads, as well as any shared memory segments it attaches to. A DLL can have a default load address set at link time, and the operating system DLLs are usually set to load in the top 256 Mb of process address space, above 0x70000000. Incidently, a great tool to view DLL load addresses and process address space on Windows is Process Explorer from sysinternals.com.

Applications which attach to IDS shared memory segments, such as oninit, onstat, onbar need a contiguous free block of address space as large as the segment they are attaching to. So if onstat connects to the Resident segment and onstat -g seg shows it to be 1GB in size (due to the number of buffers configured for example), the onstat address space will need a 1GB contiguous gap where no DLLs are loaded. The first place it will try and attach the resident segment is the value of the onconfig parameter SHMBASE which is where oninit attaches it. The default SHMBASE value on Windows is 0xC000000.

The problem starts when when a DLL has a base address somewhere in the middle of the process address space. This fragments the address space and reduces the maximum size of shared memory segment that could be attached. If base address is not set at link time, DLLs have a default address of 0x10000000. A DLL loaded there would certainly cause a problem as it's only 64 Mb higher than default SHMBASE. If a process loads multiple DLLs which have a default load address of 0x10000000, one will be loaded there and the rest will be dynamically rebased to wherever the OS sees fit.

There are currently two defects for XBSA DLLs loaded by onbar which are set to the default load address and hence onbar returns errors in larger IDS shared memory configurations:

CA Storage Manager XBSA DLLs also have this problem - in the past we've had to rebase their DLLs to make them play nicely with onbar.

These defects are currently open, though Technical Support can work around the problem for you by rebasing the DLLs manually using the Windows Platform SDK rebase tool.

What can be more frustrating is when an operating system DLL has a load address outside of the recommended system DLL range of 0x70000000 to 0x7FFFFFFF. Windows currently has several bad DLLs which can cause problems for IDS:

If you are experiencing problems with onbar or other IDS utilities on Windows which go away when the number of buffers is reduced you may be in this particular circle of DLL hell. Depending on the problem the solution could be to request a patch from Microsoft, or seek help rebasing XBSA DLLs from tech support. Now would be a good time to familiarize yourself with Process Explorer to assist with the troubleshooting. A good way to determine the base address of a DLL is to use the Windows Platform SDK utility dumpbin.exe. E.g. to see the base address of xpsp2res.dll type:

dumpbin /headers %windir%\system32\xpsp2res.dll | find "image base"

Some additional process address space can be opened up by setting the /3GB boot.ini switch - this will provide an extra 1GB of address space above 0x80000000 for any process which is built with the IMAGE_FILE_LARGE_ADDRESS_AWARE in the process header (and IDS binaries are built with this).

One glimmer of light on the horizon is that when x86_64 IDS is available for for Windows 64-bit, this problem should largely be a thing of the past. Shared memory segments larger than 2GB will be available, and we can start ascending the terraces of DLL Purgatory - ok I probably took the analogy too far that time.

Before anyone asks when the x86_64 Windows port of IDS will be ready, that has not been finalized yet; all I can say is that it is in progress.[Read More]

The Q2 numbers are in and Informix Dynamic Server experienced another large increase in revenue to build on the growth of Q1. As usual the details are not released publicly but I am officially allowed to say, and I quote..

Informix Dynamic Server Role Separation can be important for data server installations where clearly defined security roles exist. This post describes how to disable Role Separation on Windows if you decide you don't need it after all, and how to partly enable it - maybe you no longer wish to maintain separate users and groups for specific DBA tasks, or maybe you don't want to add valid users to the ix_users group before they can connect to the database..

You cannot turn off role separation once you have enabled it. To remove role separation,you must uninstall the database server and reinstall it without role separation.

This is for a good reason - to disable role separation without reinstalling involves editing the registry, and if that goes wrong it is back to reinstalling. It is better to follow the policy that if a task requires direct edits to the registry it is not supported.

With suitable disclaimers in place here's how Role Separation can be disabled on Windows without reinstalling IDS. The required edits are presented in the form of a regini script. In order to use you would need to modify it to set "ol_myserver" to your INFORMIXSERVER value. If anyone knows how to specify an environment variable as a registry key in a regini script please let me know..

; Important:; Read the comments and warnings in this script - ; 1. Understand what it does before attempting to use it; 2. Backup your HKEY_LOCAL_MACHINE\Software\Informix registry key before use

; First switch off Role Separation in the DBMS key so uninstall doesn't look for it:;; Warning - If you have multiple instances installed change "Setup" and "Security" below; to the value corresponding to your instance, e.g. "Setup1", ""Security1" etc;; Warning - If your IDS version is not 10.00 edit the version in these keys:;HKEY_LOCAL_MACHINE\Software\Informix\DBMS\10.00\Setup Role Separation = REG_DWORD 0x00000000 AAO User = DELETE DBSSO User = DELETEHKEY_LOCAL_MACHINE\Software\Informix\DBMS\10.00\Security\IXAAO Group Name = REG_SZ Informix-AdminHKEY_LOCAL_MACHINE\Software\Informix\DBMS\10.00\Security\IXDBSSO Group Name = REG_SZ Informix-AdminHKEY_LOCAL_MACHINE\Software\Informix\DBMS\10.00\Security\IXUSERS Group Name = REG_SZ *

; Next set the Security groups to their default non-separated values;; Important - change "ol_myserver" to the value of your INFORMIXSERVER;; Warning - If your default IXDBSA group is not called Informix-Admin; edit the following lines:HKEY_LOCAL_MACHINE\Software\Informix\Online\ol_myserver\Security\IXAAO Group Name = REG_SZ Informix-AdminHKEY_LOCAL_MACHINE\Software\Informix\Online\ol_myserver\Security\IXDBSSO Group Name = REG_SZ Informix-AdminHKEY_LOCAL_MACHINE\Software\Informix\Online\ol_myserver\Security\IXUSERS Group Name = REG_SZ *

Once a customized version of this script is run, check the changes were made correctly, and then manually delete the ix_aao and ix_dbsso Security groups that were created at install time. When IDS restarts it should be back to its default un-role separated state.

What this registry structure implies is the possibility of having a partial role separation implementation. For example, suppose you don't want separate DBA roles, but do want to improve security by only allowing members of an ix_users group to access the data server. You could implement this by creating an operating system group for your users, and then setting the

The Informix Summer roadshow has begun and here is a repro of the event calender from IIUG. Note I added a link to pictures from the Zagreb roadshow (thanks Hrvoje). If you attend or run one of these events and have some photos to share please let me know.

Take advantage of these Informix events coming to cities near you!Informix Roadshow

The Informix Dynamic Server roadshow is traveling around the world, providing a 2-day deep-dive, hands-on opportunity to try out the latest Informix technology. Gain fresh, innovative ideas to optimize your business performance and create competitive advantage.

Does your local user group need new ideas for your meetings? IBM and the IIUG have teamed up to create Tech Day content that includes a suggested agenda, presentation materials, demos and speakers who can be available for your user group meeting. Topics include the Informix Roadmap, Continuous Availability (MACH 11), Informix Warehouse, Compression, and other topics that can be customized for your group. This can also be used as an opportunity to reach out to universities in your area by adding an Academic Initiative Information Session.

Contact Cindy Lichtenauer (cindy@iiug.org) or Pradeep Kutty (pkutty@us.ibm.com) to get a Tech Day organized for your local user group.

Business Partner Training

The Informix Bootcamps are 3-day in-depth technical workshops for IBM Business Partners that focus on IDS 11.5. Detailed presentations and hands-on labs are included where attendees will gain in-depth knowledge of exciting new features and areas including:

Continuous Availability

Informix Warehouse

Security features including label-based access control (LBAC)

OpenAdmin Tool

Administration and performance tuning

... and more!

Business Partners with applications that currently run on IDS or who are new to IDS can benefit from this workshop by learning how IDS can add value to your solutions and extend your market opportunities. Business consultants who are currently working with or planning to work with IDS for their customers are also welcome to attend.

The latest schedule of bootcamps can be found at http://www.iiug.org/url/2009_bootcamps.html.

A new sub-capacity pricing plan was announced in February that makes it more affordable to run IDS in virtualized environments.

All editions of IDS are now available and supported in a virtualized environment. Sub-capacity pricing is now available to all IDS V11.50 editions and corresponding components, helping you reduce costs and maximize your investments with server consolidation and reduced administration. IBM is responding to your tremendous interests in high availability and business continuity. With IDS V11.50, you will be able to deploy High-availability Data Replication (HDR) with no additional feature charge for the Workgroup Edition, deploying up to two secondary servers in either Idle standby or Hot active read mode. Hot active secondary servers are now eligible for sub-capacity pricing on the Processor Value Unit metric.

To cut a long story short, it means you can deploy IDS in virtualized environments without having to purchase licenses for the entire server. You can purchase Processor Value Units (PVU) up to the number of cores needed within a socket or a server. For example, if you are running IDS in a virtual machine that has a single CPU core allocated you'd purchase an appropriate IDS edition (say, Express) and 50 PVU's. If the virtual machine instance had 10 CPU cores allocated you might purchase Enterprise Edition and 500 PVU's.

Waiting at McCarran airport in a contemplative frame of mind for a flight back to Portland afforded some time to reflect on the recently completed IOD 2008 conference..

Sadly I did not get to attend as many talks or keynotes as I would have liked to this week, having had multifarious activities to work on or prepare for every day, but the main themes of the conference for me were...

Virtualization and Virtual Worlds

We announced the IDS 11.50 Developer Edition virtual appliancelast week and are seeing a lot of interest in it. Informix virtual appliances were present at various places in the conference Expo hall. I was manning the Intel booth where we were showing IDS and DB2 SLES 10 SP2 virtual appliances running under the VMWare ESxi hypervisor layer which comes optionally pre-installed on IBM Blade machines.

At the VMWare booth the first 100 people to fill in a survey were given cool looking 4GB USB drives containing the IDS and DB2 appliances. After that they were handing out DVD's.

Meanwhile at the Canonicalbooth they were demoing an Ubuntu 8.04 LTS (Hardy Heron) version of the IDS virtualappliance. Those Canonical guys are fun to work with, and Ubuntu is hugely popular because, simply put, it's easy to use and itperforms well. Stand by for more newsabout Canonical and Informix in the near future.

Among the conference technical sessions we had an intro to the virtual appliance, and ahands on lab which provided an opportunity to play around and do some programming in the Data Studio environment that comes pre-installed on the appliance. On Wednesday eveningthere was a Birds of a Feather session where some interesting feedback was provided on what people thought of the initial virtual appliance developer edition, and what they would like to see as IBM extends its virtualization strategy for Informix. One thing thatwas great to hear in this session was the favorable response from people who have tried itso far. It works, it's easy to use.

It was interesting to learn how people intend to usethe current version of the VA. Someone from a "very large retail chain" mentioned that many of their vendors supply solutions in the form of self-contained virtual appliances these days, andit will be useful to him to give the IDS appliance to any of these vendors who claim theydon't do Informix and need a starting point for Informix application development.

Another use for the virtual appliance that is gaining traction is as a core part of the Informix academicinitiative. The components required to make IDS an excellent educational platformhave fallen into place in recent times. The first was the release of the free IDS Developer Editionin IDS 11. Now everyone had access to a free IDS edition with all features enabled.Now with a virtual appliance that contains the Informix development stack, pre-configured,in one place, along with getting started tutorials, it makes for a self-contained classroom and laboratory just waiting to have a database administration and programmingcurriculum developed around it. Here's another idea - when the Ubuntu IDS virtual applianceis available, install the Edubuntu package on it - then you have a state of the art educational aid for a range of age groups that also teaches database programming.

At the bleeding edge of Informix research Lance Feagan is doing some cool things with IDS and Virtual Worlds. Lance had a couple of very interesting presentations and demo's in this area which I'd like to post more about. In the meantime take a look at this recent IBM Database Magazine article Informix Dynamic Server bridges virtual and real worlds to get the idea.

WindowsI had an IDS on Windows Deep Dive presentation scheduled for the final 11:30 to 12:30 sloton the last day and was not especially expecting anyone to show up. Lucky for me 7 determined people turned out to see 56 slides of non-stop Windows - a mixture of IDS on Windows architecture, recent Windows specific features, notes on embedding IDS on Windows plus some performance and troubleshooting tips. That reminds me, I like the new name for Windows 7, Windows 7. Inspiring.

Gambling

I have been known to fritter away as much as $10 when Ivisit Las Vegas. On Thursday evening I happened past a group of Informix slot machine junkies (Erik, Howard and Madison) and decided to throw caution to the wind and engage in a bout of reckless gambling. At $4 in it was not looking good, then I hit the jackpot and won $7. That was enough for me, I cashed out with a healthy profit. That's right, for a good time in Las Vegas don't hang out with me.

PartiesThere were some fun parties at IOD (I hope there are no photos from the Halloween party). HP and Intel showed their renewed enthusiasm for Informix by sponsoring an IIUG reception. Check out Spokey's Informix Zone blog where he has kept track of who has posted photos.

Jacques Roy somehow managed to report on every day of the conference in his: Informix and Computing blog. If you are more of the visually stimulated type,turn to roving photographer and Frenchman Jean-George Perrin who has amassed a large collection of conference images on his Facebook site.[Read More]

The IOD conference has started, with Customer Advisory Council meetings on Saturday, Business Development Day on Sunday, and a welcome reception last night with free flowing food and drink. It wouldn't be a conference without mercurial Frenchman Jean-Georges Perrin taking photos..

Don't mess with the International Informix User Group

See more of his pictures on Facebook. I'm already starting to wonder if it was wise to get a permanent Informix tattoo.. ah well, it's Vegas.[Read More]

Sitting on a flight to Las Vegas for the IOD conference affords an oportunity to tear myselfaway from the laptop and turn my attention to Carlton Doe's new book: Administering Informix Dynamic Server - Building the Foundation.

I should start by admitting a bias. I like Carlton, and from before Iknew him I've liked his writing; he writes not only with energy and passion for the subject, but with a great deal of technicalknowledge and experience. What this means is that you get all the information you would expect, but also something more than just adry technical book. So, needless to say, I was looking foward to a chance to read this book.

Is there a need for an Informix book when there is a comprehensive set of high quality Informix documentation available online?

The documentation is great but there is a huge amount of it, and zeroing in on exactly what you need can take time. Carlton addresses this question in the introduction:

In this book, I try to take the dry technical details of the documentationand put them into the context of daily life. I cover topics in what I thinkis their logical order of occurrence when working with a database environment. First, you design the environment; then you build andpopulate it. You create backups on a regular basis and monitor and tune asnecessary. There are other responsibilities and functions, but these are the most important. I use this approach to build the subjectsdiscussed in each chapter.

One thing this book does very well is provide current information. A greatdeal of new functionality has been added to IDS in the last year, andknowing how much of a lead time publishing deadlines impose I am impressed by the amount of up to date 11.50 feature descriptions thebook has. It can be stressful trying to write about a feature beforerelease while the developers change it as you watch (OpenAdmin Tool which keeps getting new features is a good example).Somehow Carlton has managed to stay current, and write with an inside knowledge ofwhich features are likely to change.

I'm glad to see a few in-jokes to keep us paying attention. In a section entitledProblem solving with Extensibility a fictitious org chart is introduced,where Mukta, Fred, Kevin and Kassa report to someone called Jerry. That soundsstrangely familiar, though I can't quite place it.

At 424 pages Administering Informix Dynamic Server is small enough tocarry around, yet comprehensive enough to serve as a single referencesource. It also manages to provide a balance between introductory material for new Informix users and advanced technical information forInformix power users. After flicking through the backup and restore chapter I find myself drawn into a few diagrams and now know enough about XBSA architecture to be dangerous.

Overall, an excellent reference that both new and experienced Informix DBA's will find useful to have around.

After a rather hectic week I am finally finding the time to write about an recent trip to a Windows Server 2008 Application Compatibility Readiness Lab in Redmond to put Informix Dynamic Server through its paces on Windows Server 2008. We did a similar exercise for Windows Vista last year (#1, #2) that proved very useful in identifying compatibility problems early on and testing performance improvements.

The LabThe readiness labs are located in the Platform Adoption Center, building 20. This year I went along with Mirav from our kernel performance team. We had the same lab as last time as far as I can tell, which by default has 4 W2K8 machines configured to order. As we wanted to run comparative performance tests in addition to compatibility tests we were fairly demanding of hardware and ended up setting a record for number of machines crammed into the lab (Win2K8, 32-bit, 64-bit, with HyperVisor & without, Win2K3, Linux, etc..) - a task our hosts cheerfully took on.

Mirav and I getting to grips with Windows Server 2008

The readiness program consists of some presentations on Windows Server 2008 features, which have some overlap with Vista features, an introduction to the WS08 Logo Program, Windows Error Reporting, and a Security Analysis. The rest of the time we are free to play around with the machines and try out new features.

Informix CompatibilityI plan two more posts this week describing running IDS on Windows Server 2008 in more detail, but here is a quick summary for the impatient:

IDS 11.50 works fine on Windows Server 2008 with approximately 30% performance improvement for network intensive operations over Windows Server 2003 on the same hardware (based on ANTS testing with >500 users).

When installing IDS 11.50.xC1 you get a pop-up error warning the IDS Message service did not start. This is logged internally as defect idsdb00160129 - contact tech support for latest status. The Message service is not required for IDS operation.

In some circumstances CSDK 3.50.xC1 installation appears to hang on Server 2008 and Vista when it reaches the "Removing backup files" phase. CSDK works fine after killing the process or rebooting (idsdb00160123).

64-bit IDS 11.50.FC1 install fails on Intel machines with PROCESSOR_IDENTIFIER=Intel64 (idsdb00160153). This problem does not occur on AMD or other Intel processors. If you have this platform contact IBM tech support for a patch.

We plan to address these incompatibilities in xC2 of the server and CSDK releases.

Logistical SupportI noticed one key difference from last year. The ice cream freezer stayed well stocked throughout the week. As always, copious quantities of snacks were provided along with meals. I can only put this down to fear of what might happen if sleep-deprived bloggers lose their sugar high. Of course being entirely focussed on the technology I didn't give any of this much thought.

A quick survey of the freezer contents

ConclusionAs for the Vista program last year, the W2K8 readiness program proved most valuable for identifying incompatibilities early and testing performance improvements. We also found we learned a lot in having the opportunity to dedicate a few days to using the OS and its new features, and also having experts on hand who could help us with solve problems such as how to do function profiling which will benefit follow-up performance analysis.

From a DBA perspective Windows Server 2008 provides a good performance improvement with sufficiently powerful hardware and has some useful file system improvements. I particularly liked the efforts around improved command line interface, and I think the concept of the Server Core has a lot of potential - more on this later.[Read More]

c:\informix> onstat -ERROR: Could not initialize the security subsystem. Please ensure that this account has the necessary privileges and ensure INFORMIXSERVER value exists in the registry and environment.

How do I fix it ?

The "security subsystem" part of this message is misleading, in that it generally doesn't relate to anything about security from a user perspective. The key part of the message is nearly always "ensure INFORMIXSERVER value exists in the registry and environment".

The way to troubleshoot this error is:

echo %INFORMIXSERVER% - does it have the correct value? if not make sure you have run the %INFORMIXDIR%\%INFORMIXSERVER%.cmd script to set your environment correctly.

Run regedit and check HKEY_LOCAL_MACHINE/SOFTWARE/Informix/Online/%INFORMIXSERVER% - does it exist? Does it have the Environment, Setup, Security subkeys? If not your IDS instance could be corrupted or installed with a different INFORMIXSERVER value to the one you were expecting. If you suspect corruption, try running the buildreg.exe utility which is part of the ntutils package downloadable from IIUG.

Friday was my last day in Tech Support and today I take up a new position in Development as IDS architect for install, common libraries and Windows. Having spent most of my career in tech support, there is plenty about the old job I'll miss. I'll subjectively state that Informix tech support is a great environment to work in. Unlike many tech support organizations Informix support engineers have equal status with their development counterparts, and have opportunities to work with source code, fix bugs and develop product features. The customer perspective that support engineers gain is valued by development and tech support sign-off is required during the review process for new features.

In the new role I am interested in any feedback and suggestions you might have regarding the IDS installation process (on any platform) - does it meet your needs? And if you use or plan to use IDS on Windows, anything you would like to see different or Windows features you would like to see better integration with? Let me know.[Read More]

A closed beta for the Windows 64-bit port of IDS is scheduled to begin around mid-May. The 64-bit port has some performance advantages, including support for shared memory greater than 4GB. Here's an example onstat -g seg from a pre-beta test instance showing >5GB shared memory. Note that SHMBASE is now placed above 2GB (0x80000000).

Ever wondered how DB2 and Informix Dynamic Server architectures compare? Which major features they have in common and what separates them? What are the major editions and platform support between the two? These questions and more are answered in a new developerWorks article by Suma C Shastry, Mohan Kumar, Prasad Srinivasachar entitled How to go hand-in-hand with DB2 and Informix.

It looks like this article has been well-researched. Particularly interesting to me are the architecture diagrams, which include Architecture overview, Process Model, Memory model, Instance Architecture, Backup mechanisms, Security architecture, administration tools.[Read More]

One of our kernel performance engineers installed this IDS 10.00.FC1 for Power 5 (ppc64) instance directly on a PS3 to see what would happen, and it works just fine.

A nice project for a rainy day might be a PS3 specific IDS port to take advantage of the Linux programming model. Too bad the PS3 only has 256Mb RAM - but for applications with small data sets and large CPU requirements...[Read More]