I am not able to compile the database

Question

I am not able to compile the database in One Identity Maneger 8.1.4 installation, I am using SQL server 2017 as database. I received the following error message: "Processing step 'QBM ContentWaitForProcessing (wait for processing initial content)' failed. 1 - Wait command failed. The operation has timed out." 
 The operation hangs in the "Wait for DBqueue processor" process. 
 If you've been through this or know how to solve it, I'd be very grateful for the help.

Markus Weiss-Ehlers · Answer

In order to create the missing roles and logins, please use the attached SQL script. The information in the script(s) provided is known to work successfully; however, they have not been officially tested by our Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by our Support and Development. Support and Development recommend always making a backup of the current database prior to execution of any script(s) that may modify it. GrantPermissionsOnRestoredDatabase.sql -- -- ONE IDENTITY LLC. PROPRIETARY INFORMATION -- -- This software is confidential. One Identity, LLC. or one of its affiliates or -- subsidiaries, has supplied this software to you under terms of a -- license agreement, nondisclosure agreement or both. -- -- You may not copy, disclose, or use this software except in accordance with -- those terms. -- -- -- Copyright 2019 One Identity LLC. -- ALL RIGHTS RESERVED. -- -- ONE IDENTITY LLC. MAKES NO REPRESENTATIONS OR -- WARRANTIES ABOUT THE SUITABILITY OF THE SOFTWARE, -- EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED -- TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, -- FITNESS FOR A PARTICULAR PURPOSE, OR -- NON-INFRINGEMENT. ONE IDENTITY LLC. SHALL NOT BE -- LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE -- AS A RESULT OF USING, MODIFYING OR DISTRIBUTING -- THIS SOFTWARE OR ITS DERIVATIVES. -- --------------------------------------------------------------------------------------------------------------------------------- -- Script to restore the database permissions, users and roles after restoring a One Identity Manager database from a backup. -- -- Please execute as sa (or similar)! -- Parameters to be set: @DatabaseName, @AdminLoginPwd, @ConfigLoginPwd, @UserLoginPwd -- -- /!\ Afterwards the DBCompiler (with _Admin login) needs to be started to setup the DBQueueProcessor within this context -- -- FYI: When this script creates users or roles, DBQueue tasks QBM-K-DBPrincipalCreate will be put into DBQueue which - as soon as the -- DBQueue Processor SQL Agent is created and started - will create server roles OneIMConfigRole_ (OneIMAdmuinRole_ -- will be created by this script) --------------------------------------------------------------------------------------------------------------------------------- declare @DatabaseName nvarchar(100) , @AdminLoginName nvarchar(100) , @AdminLoginPwd nvarchar(100) , @ConfigLoginName nvarchar(100) , @ConfigLoginPwd nvarchar(100) , @UserLoginName nvarchar(100) , @UserLoginPwd nvarchar(100) , @Cmd nvarchar(max) --------------------------------------------------------------------------------------------------------------------------------- -- set parameters - please fill in --------------------------------------------------------------------------------------------------------------------------------- select @DatabaseName = '' --DB_Name() select @AdminLoginName = @DatabaseName + '_Admin' select @AdminLoginPwd = '' --new password for the new user to create select @ConfigLoginName = @DatabaseName + '_Config' select @ConfigLoginPwd = '' --new password for the new user to create select @UserLoginName = @DatabaseName + '_User' select @UserLoginPwd = '' --new password for the new user to create begin try --------------------------------------------------------------------------------------------------------------------------------- -- checkup --------------------------------------------------------------------------------------------------------------------------------- if (@DatabaseName = '' or @AdminLoginPwd = '' or @ConfigLoginPwd = '' or @UserLoginPwd = '') begin raiserror ('Parameter values missing. Please provide a value for each parameter!', 18, 1) with nowait end select @Cmd = 'use ' + @DatabaseName + ' if not exists (select 1 from DialogTable where Tablename = ''QBMDBPrincipal'') begin raiserror(''50000 2 This database ' + @DatabaseName + ' has a wrong version! '', 18, 1) with nowait end ' exec sp_executeSQL @Cmd select @Cmd = 'use ' + @DatabaseName + ' if not exists (select 1 from QBMDBPrincipal where UserName = ''Config'' and LoginName > '' '') begin raiserror(''50000 2 This database ' + @DatabaseName + ' is not yet run with specific database users and permissions. In order to bring your OneIM database into this state you need to perform an update migration. Please refer to OneIM installation guide (chapter "Users with granular permission for the One Identity Manager database on an SQL Server"! '', 18, 1) with nowait end ' exec sp_executeSQL @Cmd --------------------------------------------------------------------------------------------------------------------------------- -- create Admin Login --------------------------------------------------------------------------------------------------------------------------------- select @Cmd = 'use ' + @DatabaseName + ' if exists (select top 1 1 from sys.server_principals where name = ''' + @AdminLoginName + ''' and type not in (''S'')) begin raiserror(''50000 2 Login ' + @AdminLoginName + ' already exists, but has the wrong type (<> SQL_LOGIN)'', 18, 1) with nowait end if not exists (select top 1 1 from sys.server_principals where name = ''' + @AdminLoginName + ''' and type = ''S'') begin CREATE LOGIN ' + @AdminLoginName + ' WITH PASSWORD = ''' + @AdminLoginPwd + ''' end -- create db user with fix name: Admin if exists (select top 1 1 from sys.database_principals where name = ''Admin'') begin ALTER USER [Admin] WITH LOGIN = ' + @AdminLoginName + ' end else begin CREATE USER [Admin] FOR LOGIN ' + @AdminLoginName + ' WITH DEFAULT_SCHEMA=[dbo] end -- grant required permission role ALTER ROLE [db_owner] ADD MEMBER Admin ' select @Cmd exec sp_executeSQL @Cmd --------------------------------------------------------------------------------------------------------------------------------- -- msdb: agents permissions (we need a user and a role on msdb for that) --------------------------------------------------------------------------------------------------------------------------------- select @Cmd = 'use [msdb] if exists (select top 1 1 from sys.database_principals where name = ''OneIM_' + @DatabaseName + ''' and type in (''S'', ''U'', ''G'')) begin DROP USER [OneIM_' + @DatabaseName + '] end CREATE USER [OneIM_' + @DatabaseName + '] FOR LOGIN ' + @AdminLoginName + ' WITH DEFAULT_SCHEMA=[dbo] if not exists (select top 1 1 from sys.database_principals where name = ''OneIMRole_' + @DatabaseName + ''' and type = ''R'') begin CREATE ROLE [OneIMRole_' + @DatabaseName + '] end GRANT SELECT ON OBJECT::sysjobs TO [OneIMRole_' + @DatabaseName + '] GRANT SELECT ON OBJECT::sysjobactivity TO [OneIMRole_' + @DatabaseName + '] GRANT SELECT ON OBJECT::sysjobschedules TO [OneIMRole_' + @DatabaseName + '] GRANT SELECT ON OBJECT::sysschedules TO [OneIMRole_' + @DatabaseName + '] -- managed instance needs some more grant if SERVERPROPERTY(''EngineEdition'') = 8 begin GRANT SELECT ON OBJECT::sysjobsteps TO [OneIMRole_' + @DatabaseName + '] end -- assign this role to the SQLAgentUserRole ALTER ROLE [SQLAgentUserRole] ADD MEMBER [OneIMRole_' + @DatabaseName + '] -- put our user in the role ALTER ROLE [OneIMRole_' + @DatabaseName + '] ADD MEMBER [OneIM_' + @DatabaseName + '] ' select @Cmd exec sp_executeSQL @Cmd --------------------------------------------------------------------------------------------------------------------------------- -- master: permissions for executing sys.xp_readerrorlog (we need a user and a role on master for that) --------------------------------------------------------------------------------------------------------------------------------- select @Cmd = 'use [master] if exists (select top 1 1 from sys.database_principals where name = ''OneIM_' + @DatabaseName + ''' and type in (''S'', ''U'', ''G'')) begin DROP USER [OneIM_' + @DatabaseName + '] end CREATE USER [OneIM_' + @DatabaseName + '] FOR LOGIN ' + @AdminLoginName + ' WITH DEFAULT_SCHEMA=[dbo] if not exists (select top 1 1 from sys.database_principals where name = ''OneIMRole_' + @DatabaseName + ''' and type = ''R'') begin CREATE ROLE [OneIMRole_' + @DatabaseName + '] end GRANT EXECUTE ON OBJECT::xp_readerrorlog TO [OneIMRole_' + @DatabaseName + '] -- managed instance needs 3 more grants if SERVERPROPERTY(''EngineEdition'') = 8 begin GRANT EXECUTE ON OBJECT::xp_sqlagent_is_starting TO [OneIMRole_' + @DatabaseName + '] GRANT EXECUTE ON OBJECT::xp_sqlagent_notify TO [OneIMRole_' + @DatabaseName + '] GRANT EXECUTE ON OBJECT::xp_sqlagent_enum_jobs TO [OneIMRole_' + @DatabaseName + '] end -- put our user in the role ALTER ROLE [OneIMRole_' + @DatabaseName + '] ADD MEMBER [OneIM_' + @DatabaseName + '] ' select @Cmd exec sp_executeSQL @Cmd --------------------------------------------------------------------------------------------------------------------------------- -- create server role, fix name: 'OneIMAdminRole_$DatabaseName$' --------------------------------------------------------------------------------------------------------------------------------- select @Cmd = 'USE [master] if not exists (select top 1 1 from sys.server_principals where name = ''OneIMAdminRole_' + @DatabaseName + ''' and type = ''R'') begin CREATE SERVER ROLE [OneIMAdminRole_' + @DatabaseName + '] end -- add login to server role ALTER SERVER ROLE [OneIMAdminRole_' + @DatabaseName + '] ADD MEMBER ' + @AdminLoginName + ' -- to be able to create new server roles GRANT ALTER ANY SERVER ROLE TO [OneIMAdminRole_' + @DatabaseName + '] -- to be able to see logins, which should be assigned to users GRANT VIEW ANY DEFINITION TO [OneIMAdminRole_' + @DatabaseName + '] -- following grants directly to the login (not to the server role), because otherwise it won''t work (despite of "with grant option") -- permission to kill sessions GRANT ALTER ANY CONNECTION TO ' + @AdminLoginName + ' WITH GRANT OPTION -- to check the availability of the server agent, select on sys.dm_server_services GRANT VIEW SERVER STATE TO ' + @AdminLoginName + ' WITH GRANT OPTION ' select @Cmd exec sp_executeSQL @Cmd --------------------------------------------------------------------------------------------------------------------------------- -- create logins for "Config" and "User" on the server --------------------------------------------------------------------------------------------------------------------------------- select @Cmd = 'use [master] if exists (select top 1 1 from sys.server_principals where name = ''' + @ConfigLoginName + ''' and type not in (''S'')) begin raiserror(''50000 2 Login ' + @ConfigLoginName + ' already exists, but has the wrong type (<> SQL_LOGIN)'', 18, 1) with nowait end if not exists (select top 1 1 from sys.server_principals where name = ''' + @ConfigLoginName + ''' and type = ''S'') begin CREATE LOGIN ' + @ConfigLoginName + ' WITH PASSWORD = ''' + @ConfigLoginPwd + ''' end if exists (select top 1 1 from sys.server_principals where name = ''' + @UserLoginName + ''' and type not in (''S'')) begin raiserror(''50000 2 Login ' + @UserLoginName + ' already exists, but has the wrong type (<> SQL_LOGIN)'', 18, 1) with nowait end if not exists (select top 1 1 from sys.server_principals where name = ''' + @UserLoginName + ''' and type = ''S'') begin CREATE LOGIN ' + @UserLoginName + ' WITH PASSWORD = ''' + @UserLoginPwd + ''' end ' select @Cmd exec sp_executeSQL @Cmd --------------------------------------------------------------------------------------------------------------------------------- -- make logins for "Config" and "User" known in OneIM database --------------------------------------------------------------------------------------------------------------------------------- select @Cmd = 'use ' + @DatabaseName + ' update QBMDBPrincipal set LoginName = ''' + @ConfigLoginName + ''' where UserName = ''Config'' update QBMDBPrincipal set LoginName = ''' + @UserLoginName + ''' where UserName = ''User'' ' select @Cmd exec sp_executeSQL @Cmd --------------------------------------------------------------------------------------------------------------------------------- -- post hint --------------------------------------------------------------------------------------------------------------------------------- select 'Please use DBCompiler with a database user '+@AdminLoginName+' and follow the wizard to complete the restore!' as "DBCompiler Start Hint" end TRY begin CATCH declare @ErrorMessage NVARCHAR(4000); declare @ErrorSeverity int; declare @ErrorState int; select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState ); end CATCH

Endpoint Privilege Management

I am not able to compile the database

Top Replies