2

I'm trying to install DB2 Express-C on my PC (Windows 7 pro), but no matter how I've installed or configured it, I can't grant the ability to create databases to my windows domain account. No matter what I try, I get the errorSQL1092N my-windows-id does not have the authority to perform the requested command when I try to create a database.

My PC is centrally administered by my company, and I can't (permanently) create local acccounts on the PC. My domain account is in the local administrators group, and I've added my account to the DB2ADMNS group created during the DB2 install, but DB2 doesn't seem to be able to see that. I've spent about 12 hours going through the DB2 instructions and trying different things, but no matter what I've tried, DB2 won't allow my domain account to create databases.

I'm the only user of the PC and I'm not really concerned about security. I just need to get DB2 working in order to run some software that requires it. Is it possible to open up DB2 security and allow anyone to do anything? I tried changing the FED_NOAUTH config setting, but that didn't seem to have the desired effect.

|improve this question
  • Are you trying to create the database through the GUI (ie, Command Center) or through CLP (command line processor)? Have you also tried to launch First Steps and have it create the Sample database? – Chris Aldrich Oct 11 '12 at 12:42
  • My usual test was to launch the command line processor and issue the command "create database foobar". It would fail with the SQL1092N error. – Kenster Oct 15 '12 at 13:54
  • try attaching to the instance first, ie, db2 attach to DB2 user <name> using <password>. Make sure you use the instance owner (ie, the ID you used for administrator of DB2). Then try to create the database. U can always assign DBADM to your user id after the fact. Let me know if it worked. – Chris Aldrich Oct 15 '12 at 14:02
1

The short answer to this problem is that you need to set the DB2 registry variable DB2_GRP_LOOKUP to local.

As a local administrator on your PC (i.e. not using your domain account), start a DB2 Command Window and issue the following:

db2set DB2_GRP_LOOKUP=local
db2stop
db2start

The reason this occurs has to do with how DB2 enumerates which groups a user belongs to upon successful authentication. In a Windows Domain environment, the default group enumeration behavior is to ask the system that authenticated the user: If a user is authenticated by the local machine, DB2 asks the local machine what groups belongs to; if the user is authenticated by the domain, DB2 will ask the domain what groups the user belongs to - and it will not ask the local machine.

So, when you connect to DB2 using your domain ID, DB2 asks the domain controller what groups you belong to, so it does not see that your ID belongs to the Administrators group on your local PC.

When you set DB2_GRP_LOOKUP=local, this modifies this behavior: It tells DB2 to always enumerate groups from the local machine, regardless of whether the user was authenticated by the domain or the local machine.

After doing this, DB2 will see that your Domain ID belongs to the local Administrators group, and you'll end up with full SYSADM privileges (and the ability to create a database).

|improve this answer
  • Hmm, there must be more than that. I could succesfully install DB2 being in the local administrator group and only a regular user in the domain (both on Windows XP and Windows 7). – a_horse_with_no_name Oct 26 '12 at 9:54
  • Windows period makes DB2 much harder. We have seen some weird things with security that aren't always consistent. – Chris Aldrich Oct 26 '12 at 12:45
  • Yes, unfortunately, even though you could install the product using a Domain ID doesn't mean that the ID will have SYSADM rights by default. – Ian Bjorhovde Oct 30 '12 at 0:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.