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. 18.104.22.168 for DirectAudit 2.0
-- 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)
CREATE LOGIN [%DOMAIN\Username%] FROM WINDOWS
-- 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))
CREATE USER [%DOMAIN\Username%]
-- 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))
EXEC sp_addrolemember 'user', [%DOMAIN\Username%]
-- Set user as administrator
DECLARE @clientVersion nvarchar(255)
DECLARE @trusteeSid varbinary(max)
DECLARE @count int
set @clientVersion = '%AssemblyVersion%'
set @trusteeSid = SUSER_SID('%DOMAIN\Username%')
DECLARE @saUser nvarchar(255)
SELECT @saUser = [name] FROM sys.server_principals WHERE [sid]=0x01
EXECUTE('ALTER AUTHORIZATION ON DATABASE::[%DatabaseName%] TO [' + @saUser +']')