Jim Youmans
asked on
DB2 Restore on new install not working
DB2 10.5 on Windows
So I am trying to teach myself how to install DB2 and restore a dev database. So I install DB2 10.5 on windows without issue. I then run the sample database setup and I can connect to it no problem.
When I copy over the DEV database backup and try to restore it, problem. I get this.
C:\Windows\system32>db2 restore database DEV FROM X:\dbbackups
SQL1092N The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation. User
ID: "xxx". SQLSTATE=00000
Now XXX is in the Admin group on the windows server and it is running the DB2 instance service. It should have full control of the server. I looked at the sys roles (sysmon, sysadm,sysctrl, and sysmaint group names and set them all to Administrators which is the admin group name on the windows server. I just don't get it. What am I missing?
FRUSTRATED IN STL
Jim
So I am trying to teach myself how to install DB2 and restore a dev database. So I install DB2 10.5 on windows without issue. I then run the sample database setup and I can connect to it no problem.
When I copy over the DEV database backup and try to restore it, problem. I get this.
C:\Windows\system32>db2 restore database DEV FROM X:\dbbackups
SQL1092N The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation. User
ID: "xxx". SQLSTATE=00000
Now XXX is in the Admin group on the windows server and it is running the DB2 instance service. It should have full control of the server. I looked at the sys roles (sysmon, sysadm,sysctrl, and sysmaint group names and set them all to Administrators which is the admin group name on the windows server. I just don't get it. What am I missing?
FRUSTRATED IN STL
Jim
ASKER
The database I am trying to restore does not exist on the new server. Only the SAMPLE database does.
So the account I use to run the server is AITDB2Service and when I run the above query I get...
AITDB2ADMINS
DOMAIN USERS
When I do the same for my network account which is an admin on the server I get...
BITBUCKET-SOFTWARE-USERS,C ENTRIC_LEG ALFEESDB_R WM,CENTRIC IT,CITRIX_ ACCESSMANA GEMENTCONS OLE,CONFLU ENCE-USERS ,
DEPSVCSRO,DEV_BIDATAREAD_A DMIN,DOMAI N USERS,EXTRAHOP_READONLY,IC INGANORMAL ACCESS,IS, IT,JIRA-SO FTWARE-USE RS,
LASTPASSACCESS,LINUXLOGONK CN,LINUXLO GONKEEFEDA TACENTER,O FFICE_365_ USERS,PRD_ BIDATAREAD _ADMIN,
PRD_PHONESYS_MGR,QAT_BIDAT AREAD_ADMI N,QAT_PHON ESYS_MGR,S CCM_REPORT _PURCHASIN G,SCOMSQLA DMINS,
SCSM_NOTIFY_APPLICATION DEVELOPMENT,SCSM_NOTIFY_BU SINESS INTELLIGENCE,SCSM_NOTIFY_D ATABASE SERVICES,
SCSM_SG_APPLICATION DEVELOPMENT,SCSM_SG_BUSINE SS INTELLIGENCE,SCSM_SG_DATAB ASE SERVICES,
SCSM_TIER1,SCSM_TIER2_STAN DARD,SW_DP A_ADMIN,SW _SQLDBAS,V MWARE_READ _ONLY,VPN_ ACCESS,WHS LOCATORDB_ RWM,
WSUS ADMINISTRATORS
So what should I see? For DB2 permissions?
So the account I use to run the server is AITDB2Service and when I run the above query I get...
AITDB2ADMINS
DOMAIN USERS
When I do the same for my network account which is an admin on the server I get...
BITBUCKET-SOFTWARE-USERS,C
DEPSVCSRO,DEV_BIDATAREAD_A
LASTPASSACCESS,LINUXLOGONK
PRD_PHONESYS_MGR,QAT_BIDAT
SCSM_NOTIFY_APPLICATION DEVELOPMENT,SCSM_NOTIFY_BU
SCSM_SG_APPLICATION DEVELOPMENT,SCSM_SG_BUSINE
SCSM_TIER1,SCSM_TIER2_STAN
WSUS ADMINISTRATORS
So what should I see? For DB2 permissions?
AITDB2Service seems to be in the AITDB2ADMINS group, so use it.
ASKER
I tried but it gives me the same error. How do I add a user to the AITDB2ADMINS group?
Through the Windows interface.
Right click in My Computer, then Manage, then Users.
Right click in My Computer, then Manage, then Users.
ASKER
I have obviously done something wrong. Looking at my groups in windows there is a DB2ADMNS group and a DB2USERS group. Those were created when I installed DB2. There is no AITDB2ADMINS group. Not sure where that is coming from.
I have 3 users in the DB2ADMNS group, jyoumans (me), CVADMIN (commvault), and AITDB2Service (which runs DB2 server). I have tried the restore with jyoumans and AITDB2Service, both get the same not authorized error.
I installed DB2 with the AITDB2Service user. I checked the DB2 windows service and AITDB2Service is listed as the LOG ON AS of that service.
What am I missing?
Jim
I have 3 users in the DB2ADMNS group, jyoumans (me), CVADMIN (commvault), and AITDB2Service (which runs DB2 server). I have tried the restore with jyoumans and AITDB2Service, both get the same not authorized error.
I installed DB2 with the AITDB2Service user. I checked the DB2 windows service and AITDB2Service is listed as the LOG ON AS of that service.
What am I missing?
Jim
Are you logging in with a different user to try the restore or are you passing the username/password to the RESTORE command? I've never seen this behavior and would be shocked if it happened when the username is passed on the command.
ASKER
I logged into the server using the AITDB2Service ID and then ran the restore like this.
db2 restore database DEPSVCS user AITDB2Service password "xxx' from x:\backups
And I get
SQL1092N The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation. User
ID: "AITDB2SERVICE". SQLSTATE=00000
This should not be this hard. Please note that DEPSVCS is not on the new server. Just sample.
Jim
db2 restore database DEPSVCS user AITDB2Service password "xxx' from x:\backups
And I get
SQL1092N The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation. User
ID: "AITDB2SERVICE". SQLSTATE=00000
This should not be this hard. Please note that DEPSVCS is not on the new server. Just sample.
Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So, I ended up wiping out all the sys*_group definitions and leaving them to NULL so that the server admin group would default to them. Then I stopped and restarted the db2 instance and then bounced the server. That seems to have worked. Thank you so much!!
Let's make sure that DB2 recognizes the administrative permissions of that user.
SELECT * FROM TABLE (sysproc.auth_list_groups_
And does the DEV database already exist on this server? You may need the "REPLACE EXISTING" or "REDIRECT" clause on the RESTORE statement.
Kent