Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

How to recover administrator access to an existing DirectAudit Installation

11 April,19 at 11:50 AM

Find out how to recover administrator access to an existing DirectAudit Installation by granting an AD User administrator privileges at the Database level

 

DirectAudit Installation Administrator

 

Working to deploy Centrify software since nearly 10 years, I have been generally occupied delivering expertise and recomendations. But sometimes, I have also saved customers from difficult situation (related or not to Centrify software as a funny fact).

 

One of those embarrassing situation is when you lose administrator access to a DirectAudit Installation.

 

First, what is the purpose of the Administrator access? And how is this setup in the first place?

 

The Administrator of a DirectAudit Installation is this account that have SysAdmin role on the SQL Server running the Management and AuditStore databases, but also have Full management privileges on the application level: e.g. creating new AuditStores, managing Audit Roles, delegating privileges on the Installation.

 

The account that create an Installation is automatically granted Administrator of this Installation. Centrify Professional Services recommend the following practice to avoid losing access to an Installation due to the Administrator account being decommissioned from Active Directory:

  • Use a Service Account to create the DirectAudit Installation, this Service Account lifespan should be attached to DirectAudit
  • Grant Administrator Privileges through an AD Group using the DirectAudit Manager Console

 

In these both recommended cases, you would not risk to lose access to your DirectAudit Installation. But well, we all know that not all deployments are following recommendations. And this person that have setup this DirectAudit Installation may have left the company a while ago, and all you are left with is this DBA Team friend that have access to the SQL Server. So, what now?

 

Recover Administrator access

 

The following SQL script can be run using SQL Management Studio with an account that have SysAdmin privileges on the SQL Server. Before to run the script, do a search and replace to add the right values for the AD User account to be granted Administrator permissions, the Database name (Management Database) and the Assembly version.

 

-- Change %DOMAIN\Username% value to the Principal Name to be granted Administrator permissions on the Management Database, e.g. AASGAARD-CLOUD\fabrice
-- Change %DatabaseName% to the name of the Management Database, e.g. AuditServer-AASGAARD
-- Change %AssemblyVersion% to the version of the Centrify.DirectAudit.GatewayDatabase.xx.xx.x.x assembly version, e.g. 19.12.0.0 for DirectAudit 2.0

USE [%DatabaseName%]
GO

-- Grant permission to user '%DOMAIN\Username%'
DECLARE @username nvarchar(max)
set @username = '%DOMAIN\Username%'

-- Create login for Windows account
IF NOT EXISTS(SELECT sys.server_principals.name from sys.server_principals WHERE sys.server_principals.name = @username)
BEGIN
    CREATE LOGIN [%DOMAIN\Username%] FROM WINDOWS
END

-- Create database user
IF NOT EXISTS(SELECT sys.database_principals.name from sys.database_principals WHERE sys.database_principals.name = @username)
AND NOT EXISTS(SELECT sys.databases.name FROM sys.databases WHERE sys.databases.name = N'%DatabaseName%' and sys.databases.owner_sid = SUSER_SID(@username))
BEGIN
    CREATE USER [%DOMAIN\Username%]
END

-- Add member to database role
IF NOT EXISTS(SELECT sys.databases.name FROM sys.databases WHERE sys.databases.name = N'%DatabaseName%' and sys.databases.owner_sid = SUSER_SID(@username))
BEGIN
    EXEC sp_addrolemember 'user', [%DOMAIN\Username%]
END
GO

-- Set user as administrator
DECLARE @clientVersion nvarchar(255)
DECLARE @trusteeSid varbinary(max)
DECLARE @count int

set @clientVersion = '%AssemblyVersion%'
set @trusteeSid = SUSER_SID('%DOMAIN\Username%')

EXECUTE ManagementDatabaseAdministratorSet
   @clientVersion,
   @trusteeSid,
   @count OUTPUT
GO

DECLARE @saUser nvarchar(255)
SELECT @saUser = [name] FROM sys.server_principals WHERE [sid]=0x01
EXECUTE('ALTER AUTHORIZATION ON DATABASE::[%DatabaseName%] TO [' + @saUser +']')
GO

Still have questions? Click here to log a technical support case, or collaborate with your peers in Centrify's Online Community.