1

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?

Serital
  • 343
  • 3
  • 13
  • See if this workaround works for you: 1) remove master reference (if needed), 2) change target platform to SQL Server vNext, 3) add system database master reference 4) change target platform back to Azure SQL Databse V12. – Dan Guzman Mar 04 '19 at 12:30
  • That actually almost solved the problem as the vNext version does seem to have the new objects in its model.xml but caused every reference to "sys" in the project to give an error, even though the solution compiled regardless of the new errors. There is a warning sign on the master reference and the errors related to it are ignored during a local build. But the build still failed when done in Azure DevOps (VSTS). I'm not 100% this is related but it fails with a "failed to load master.dacpac" error even though the file is there. – Serital Mar 05 '19 at 15:40
  • Even if Azure DevOps worked though, the list of (ignored) errors would be a challenge for developers as they would all need to know to ignore the master related ones and figure out which errors actually matter when they're doing work. – Serital Mar 05 '19 at 15:41

1 Answers1

0

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:

enter image description here

Hope it helps!

ed

Ed Elliott
  • 6,666
  • 17
  • 32
  • Thanks Ed. That's the solution I was trying to avoid as I might have to modify that master.dacpac every time a new sys object is referenced but I guess there isn't an alternative for now. – Serital Mar 07 '19 at 10:02