Lesson Learned #163: Failed to save Auditing settings for server: xxx .Principal xxx does not exist

Published 02-18-2021 02:25 PM 980 Views
Microsoft

I have been working on a support ticket were customer was getting the following error trying to save Auditing settings.

 

Palomag_MSFT_0-1613684965611.png

 

“Failed to save Auditing settings for server: xxx .Principal xxx does not exist in the directory xxx”. 

 

During auditing configuration , customer had selected, an storage account that was being used by other Azure SQL server to save their auditing logs.

 

We confirmed that storage account was configured with firewall enabled

 

Palomag_MSFT_2-1613685632822.png

 

The problem came because,  identity assignment  was not getting saved correctly.

 

Executing the following query we could see that identity was not getting saved


select * from sys.database_scoped_credentials

 

The solution was reassign the Identity and resave auditing executing the following PowerShell command

 

Set-AzSqlServer -ResourceGroupName <NameofTheRG> -ServerName  <NameOFTheServer> -AssignIdentity

If server also has TDE with AKV you will need to run :

$server = Get-AzSqlServer -ResourceGroupName rgname -ServerName server
$objectid = $server.Identity.PrincipalId
Set-AzKeyVaultAccessPolicy -VaultName vault -ObjectId $objectid -PermissionsToKeys get, wrapKey, unwrapKey
 

 

After apply mitigation, "select * from sys.database_scoped_credentials"  output was the following, and auditing configuration could be saved correctly 
 

Palomag_MSFT_1-1613685530926.png

 

Enjoy!

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2147670%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23163%3A%20Failed%26nbsp%3Bto%26nbsp%3Bsave%26nbsp%3BAuditing%26nbsp%3Bsettings%26nbsp%3Bfor%26nbsp%3Bserver%3A%26nbsp%3Bxxx%26nbsp%3B.Principal%26nbsp%3Bxxx%26nbsp%3Bdoes%26nbsp%3Bnot%26nbsp%3Bexist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2147670%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20working%20on%20a%20support%20ticket%20were%20customer%20was%20getting%20the%20following%20error%20trying%20to%20save%20Auditing%20settings.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Palomag_MSFT_0-1613684965611.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255909iF56EA5E24FD7EDDB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Palomag_MSFT_0-1613684965611.png%22%20alt%3D%22Palomag_MSFT_0-1613684965611.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%E2%80%9CFailed%26nbsp%3Bto%26nbsp%3Bsave%26nbsp%3BAuditing%26nbsp%3Bsettings%26nbsp%3Bfor%26nbsp%3Bserver%3A%26nbsp%3Bxxx%26nbsp%3B.Principal%26nbsp%3Bxxx%26nbsp%3Bdoes%26nbsp%3Bnot%26nbsp%3Bexist%26nbsp%3Bin%26nbsp%3Bthe%26nbsp%3Bdirectory%26nbsp%3Bxxx%E2%80%9D.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDuring%20auditing%20configuration%20%2C%20customer%20had%20selected%2C%20an%20storage%20account%20that%20was%20being%20used%20by%20other%20Azure%20SQL%20server%20to%20save%20their%20auditing%20logs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20confirmed%20that%20storage%20account%20was%20configured%20with%20firewall%20enabled%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Palomag_MSFT_2-1613685632822.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255912i8303852752F2D56D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Palomag_MSFT_2-1613685632822.png%22%20alt%3D%22Palomag_MSFT_2-1613685632822.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20problem%20came%20because%2C%26nbsp%3B%26nbsp%3Bidentity%20assignment%26nbsp%3B%20was%26nbsp%3Bnot%20getting%20saved%20correctly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExecuting%20the%20following%20query%20we%20could%20see%20that%20identity%20was%20not%20getting%20saved%3C%2FP%3E%0A%3CDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eselect%26nbsp%3B*%26nbsp%3Bfrom%26nbsp%3Bsys.database_scoped_credentials%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20solution%20was%26nbsp%3B%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3Ereassign%26nbsp%3Bthe%26nbsp%3BIdentity%26nbsp%3Band%26nbsp%3Bresave%26nbsp%3Bauditing%20executing%20the%20following%20PowerShell%20command%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESet-AzSqlServer%26nbsp%3B-ResourceGroupName%26nbsp%3B%3CNAMEOFTHERG%3E%26nbsp%3B-ServerName%26nbsp%3B%26nbsp%3B%3CNAMEOFTHESERVER%3E%26nbsp%3B-AssignIdentity%3C%2FNAMEOFTHESERVER%3E%3C%2FNAMEOFTHERG%3E%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3EIf%26nbsp%3Bserver%26nbsp%3Balso%26nbsp%3Bhas%26nbsp%3BTDE%26nbsp%3Bwith%26nbsp%3BAKV%26nbsp%3Byou%20will%20need%26nbsp%3Bto%20run%26nbsp%3B%3A%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%24server%26nbsp%3B%3D%26nbsp%3BGet-AzSqlServer%26nbsp%3B-ResourceGroupName%26nbsp%3Brgname%26nbsp%3B-ServerName%26nbsp%3Bserver%0A%24objectid%26nbsp%3B%3D%26nbsp%3B%24server.Identity.PrincipalId%0ASet-AzKeyVaultAccessPolicy%26nbsp%3B-VaultName%26nbsp%3Bvault%26nbsp%3B-ObjectId%26nbsp%3B%24objectid%26nbsp%3B-PermissionsToKeys%26nbsp%3Bget%2C%26nbsp%3BwrapKey%2C%26nbsp%3BunwrapKey%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EAfter%26nbsp%3Bapply%26nbsp%3Bmitigation%2C%20%22select%26nbsp%3B*%26nbsp%3Bfrom%26nbsp%3Bsys.database_scoped_credentials%22%26nbsp%3B%20output%20was%20the%26nbsp%3Bfollowing%2C%26nbsp%3Band%26nbsp%3Bauditing%26nbsp%3Bconfiguration%26nbsp%3Bcould%20be%26nbsp%3Bsaved%26nbsp%3Bcorrectly%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Palomag_MSFT_1-1613685530926.png%22%20style%3D%22width%3A%20923px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255911i69B27E017BA482B0%2Fimage-dimensions%2F923x37%3Fv%3D1.0%22%20width%3D%22923%22%20height%3D%2237%22%20role%3D%22button%22%20title%3D%22Palomag_MSFT_1-1613685530926.png%22%20alt%3D%22Palomag_MSFT_1-1613685530926.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2147670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3ELesson%20Learned%20%23163%3A%20Failed%26nbsp%3Bto%26nbsp%3Bsave%26nbsp%3BAuditing%26nbsp%3Bsettings%26nbsp%3Bfor%26nbsp%3Bserver%3A%26nbsp%3Bxxx%26nbsp%3B.Principal%26nbsp%3Bxxx%26nbsp%3Bdoes%26nbsp%3Bnot%26nbsp%3Bexist%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-TEASER%3E
Co-Authors
Version history
Last update:
‎Feb 18 2021 02:25 PM
Updated by: