Extracting a DACPAC from the master Database

Question

I'm working on bringing an existing Azure V12 database into an SSDT project so we can start using source control and CI pipelines.

The problem I'm struggling with is that there is a reference to "sys.fn_get_audit_file" in some of the code and it gives an "unresolved reference" error. I don't want any errors to be ignored while building the project so have been trying to resolve this properly. Adding a database reference to master does not resolve the issue and it seems that the master.dacpac file that comes with SSDT does not have the definition (in model.xml) of that sys function in it. I checked all versions of master.dacpac files available in the VS folders and even tried VS 2019 preview to see if a newer version of master.dacpac would come with it.

Digging around online I realised that other people had similar problems with objects missing in master.dacpac and the suggested solution was to edit model.xml, re-pack and correct the checksum. I would rather not go down that route so I tried using SqlPackage to extract a DACPAC from a master DB in Azure but the extracted model has almost no object definitions in it.

Any suggestions?

Answer 1

Someone should raise it with the team at microsoft - I know they monitor here so maybe someone can help??

In the meantime it looks like fn_get_audit_file is supported on azure, it is according to the documentation anyway.

I have created a couple of dacpac's you are welcome to use, there is the old azure version:

https://the.agilesql.club/assets/dacpacs/azure/master.dacpac

and the v12 version:

https://the.agilesql.club/assets/dacpacs/azurev12/master.dacpac

What I did was take the existing azure master dacpac's from my machine (vs 2017) and added the sys.fn_get_audit_sql element from the non-azure version.

To do this, I use 7zip to extract the model.xml and then edit the xml file and copy the model.xml file back into the dacpac.

This does result in a model with the wrong checksum so I have a small app that re-writes the origin.xml with the correct checksum, i'll put this app on github if anyone wants it but is is a version of this:

https://github.com/GoEddie/Dacpac-References/tree/master/src/GOEddie.Dacpac.References

See https://github.com/GoEddie/Dacpac-References/blob/master/src/GOEddie.Dacpac.References/ModelChecksumWriter.cs

When I had the master.dacpac's I copied them back into VS 2017:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\AzureV12 C:\Program Files (x86)\Microsoft Visual Studio\2017\Commnuity\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\AzureV12 C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\AzureV12

(depends on the edition)

This lets me use fn_get_audit_sql inside a azure / azurev12 project:

Hope it helps!

ed