microsoft / microsoft/DacFx

Audit Action Group is ignored when changing DATABASE AUDIT SPECIFICATION

Open
#707 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug sql-feature
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- SqlPackage or DacFx Version: DacFx 170.2.70
- .NET Framework (Windows-only) or .NET Core: both
- Environment (local platform and source/target platforms): windows 11 with sql 2019 developer

**Steps to Reproduce:**

1. create sql project and deploy initial database with database audit spec as follows:
-- create server_audit omitted for simplicity
CREATE DATABASE AUDIT SPECIFICATION db_audit_spec
FOR SERVER AUDIT server_audit
ADD (DATABASE_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)

2. modify database audit spec object in a project and deploy database
CREATE DATABASE AUDIT SPECIFICATION db_audit_spec
FOR SERVER AUDIT server_audit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)

db_audit_spec will not be modified. audit_action_group_name are ignored by compare logic, thus DATABASE AUDIT SPECIFICATION cannot be changed, you have to drop it in target db first, and create it again, because its only created correctly when there is no object in target db. I checked with STATE ON and OFF, because with STATE ON, object cannot be altered, it made no difference.
when check ComparisonResult it a deployment contributor the change is visible in ElementsChanged and in ElementsComposingChildrenChanged, but definition of AuditActionGroup is empty, there is no step in a deployment plan corresponding with this change also.

My expectation is the following script
- handle state of db_audit_spec if needed
- alter db_audit_spec, add or drop correct audit object groups
- handle state of db_audit_spec if needed

**Did this occur in prior versions? If not - which version(s) did it work in?**
YES
(DacFx and Visual Studio ssdt compare)

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the DATABASE AUDIT SPECIFICATION change with DacFx 170.2.70 on SQL Server 2019, comparing DATABASE_OBJECT_CHANGE_GROUP with SCHEMA_OBJECT_CHANGE_GROUP. Inspect ComparisonResult, ElementsChanged, ElementsComposingChildrenChanged, and the deployment plan. Done means audit action groups are recognized and the plan alters the specification while handling its state.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.