Azure-docs: CREATE SERVER AUDIT on Managed Instance

[mferris2421]

The code provided with RETENTION DAYS specified throws error stating syntax is incorrect.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: a258bb07-e71a-d4e1-5f2c-bdad60a6a2e1
• Version Independent ID: 9bf5c440-3231-96be-e6ff-548e686c0203
• Content: Azure SQL Database Managed Instance Auditing
• Content Source: articles/sql-database/sql-database-managed-instance-auditing.md
• Service: sql-database
• GitHub Login: @giladm
• Microsoft Alias: giladm

[Mike-Ubezzi-MSFT]

@mferris2421 I am seeing the same exception. Just to confirm, are you running Azure SQL database Managed Instance? I am unable to complete the CREATE CREDENTIAL T-SQL because I am using Azure SQL Database and this command is not supported. I am not using Managed Instance.

But I am seeing the retention days error:

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near ', RETENTION_DAYS = 10 '.

I do not have access to Azure SQL Database Managed Instance to verify if this syntax is correct or not.

[Mike-Ubezzi-MSFT]

@giladmit Can you confirm if the SQL syntax is correct for step 6 in this tutorial: Set up auditing for your server Thank you, Mike

[mferris2421]

@Mike-Ubezzi-MSFT Correct, I am using the syntax provided in the doc, against a Managed Instance preview.

I was able to create a server audit without the retention parameter.

CREATE SERVER AUDIT [x_audit]
TO URL (PATH ='https://x.core.windows.net/x/')
GO

Now when I try to start the audit, I get an error:

ALTER SERVER AUDIT [x_audit]
WITH (STATE=ON);
GO

[giladmit]

There seem to be two different issues here:

1. In Mike's T-SQL statement, the brackets around the ", RETENTION_DAYS=10" part of the statement should be removed (in the statement definition, they denote that this is an optional parameter).
2. In Matt's T-SQL, there's an error in the container URL PATH, it's missing the "blob" part - should be 'https://x.blob.core.windows.net/x' (not 'https://x.core.windows.net/x/')

Thanks,
Gilad (MSFT)

[mferris2421]

@giladmit Sorry, that was me trying to sanitize the statement. I deleted too much. My command included the correct address.

Regarding the integer example, thank you for pointing that out. I'm not familiar with T-SQL or even SQL admittedly, and I took the brackets are part of the syntax. I was able to successfully alter my server audit without the brackets. Thanks!

I still have the same problem starting my audit.

ALTER SERVER AUDIT [X_audit]
WITH (STATE=ON);
GO

Msg 33222, Level 16, State 1, Line 1
Audit 'DOTAZMSQLT1_audit' failed to start

[giladmit]

@mferris2421, no problem, thanks for clarifying.

We're looking into it. In the meantime, can you tell me if you created new credentials for that container?

Thanks,
Gilad (MSFT)

[mferris2421]

@giladmit, Correct I was able to create new credentials to my blob storage.

CREATE CREDENTIAL [https://x.blob.core.windows.net/x]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'mykey'
GO

[mferris2421]

@giladmit

Just following up on this one. So far, we're still just piloting things and figured out our issue; but this would be good to know for the future.

[CarlRabeler]

please-close as resolved

[Mike-Ubezzi-MSFT]

@mferris2421 We will now proceed to close this thread. If there are further questions regarding this matter, please reopen it and we will gladly continue the discussion.

[saurabhsuman38]

What was the solution to the problem?

[ikflc]

Hi Mike,
Can you please let me know the solution to it? I am hitting the same issue.
I raised a MS support call but is taking long.

CREATE CREDENTIAL [https://xxx/yyy-aud]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'key without ? from the beginning of the token'
GO

CREATE SERVER AUDIT [SQLMI_SVRAUD]
TO URL ( PATH ='https://xxx/yyy-aud' , RETENTION_DAYS = 7 )
GO

alter server audit [SQLMI_SVRAUD]
with (STATE=ON)
go

Msg 33222, Level 16, State 1, Line 10
Audit 'SQLMI_SVRAUD' failed to start . For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

Thanks & regards,
Irene

[Mike-Ubezzi-MSFT]

@ikflc Can you provide me the support request ID, as I need to include this in the escalation I am going to send out. Thank you!
P.S. You can include it here or send to me via AzCommunity email.

[Mike-Ubezzi-MSFT]

Can you also include the following out from: For more information, see the SQL Server error log. You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'

[ikflc]

Hi There,

Support Id 120012223000709

I got the following by running
select * from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'

We are now looking to see if it is a networking permission issue.

Thanks & regards,
Irene

[Mike-Ubezzi-MSFT]

@ikflc You can also do the following to get more helpful information:

I believe you might get some more information by looking at the SQL Error logs rather than the xevent ring buffers. Try the following:

"Open SSMS > Connect to the SQLMI instance > expand and browse to the Management > SQL Server Logs > Current Log"
Once you have the current log open, try restarting the Audit then refresh the log, and there should be a more verbose / text-based entry in there that will likely be more helpful.

If you need help translating or understanding the error, I can assist.

[Mike-Ubezzi-MSFT]

@ikflc Wanted to follow-up and see if you were able to resolve this? Per the support request that was opened for this specific issue, the following is the issue:

It seems that access to storage account is restricted to MI VNet, which requires service endpoint. It in turn requires subnet delegation turned on on MI subnet - which is not done.

[ikflc]

Hi Mike,
I am still waiting for our cloud team to action. I think the issue can be closed.
Thanks for your follow-up.
Best regards,
Irene

From: Mike Ubezzi [e-mail]
Sent: Thursday, 30 January 2020 10:32 AM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Irene Lam [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] CREATE SERVER AUDIT on Managed Instance (#8416)

@ikflchttps://urldefense.com/v3/__https:/github.com/ikflc__;!!HKeyBm8!CQU5z2F86KKYt8Du_uOV0djSTgQvrvur2axDIl5IEajdqe_WGzjgSlFpfT3bQJw$ Wanted to follow-up and see if you were able to resolve this? Per the support request that was opened for this specific issue, the following is the issue:

It seems that access to storage account is restricted to MI VNet, which requires service endpoint. It in turn requires subnet delegation turned on on MI subnet - which is not done.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/MicrosoftDocs/azure-docs/issues/8416?email_source=notifications&email_token=AFJT7SZER22TTSR33YUHWJ3RAIGWZA5CNFSM4E7JO4K2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKJEXYI*issuecomment-580013025__;Iw!!HKeyBm8!CQU5z2F86KKYt8Du_uOV0djSTgQvrvur2axDIl5IEajdqe_WGzjgSlFpP0ElRFs$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/AFJT7SYVECEFX4P7CR43NCDRAIGWZANCNFSM4E7JO4KQ__;!!HKeyBm8!CQU5z2F86KKYt8Du_uOV0djSTgQvrvur2axDIl5IEajdqe_WGzjgSlFptebaTEI$.

---

This email, including all attachments, is confidential and for the sole use of
the intended recipient(s). If you are not the intended recipient, you are
prohibited from disclosing, copying, distributing, or in any other way using it.
If you have received this email in error, please notify me by return email,
or contact the AEMO Information and Support Hub on 1300 236 600, and then delete this email from your system.

---