Hi Readers!
The below blog content is intended to help those who are involved in SQL server migration to Azure managed instance and are having SQL audit already configured in their On-premise SQL server.
As you might know, SQL server Audit events can be written to event logs and audit files for the server level (Instance level) and Database level audit specifications. I would limit the scope of this blog to migration alone and would not get much into the details of the types of auditing we can do with SQL servers using the feature.There are plenty in both Database and Server level specifications. We do not have the luxury of accessing the VM behind the SQL Managed instance in Azure and hence cant use the option to write the audit events to Event logs. We can get the audit write its events to an Azure Storage blog container and the events can also be retrieved and seen in SSMS just like how we have in On-premise.
For database audit specifications' migration, we don't need to worry much as it will be already there in the migrated database in the managed instance.We can map the audit specifications to the audit created to make it working. For the Server/Instance level, I will be using the UI for adding a few events . You can create the ones which you already have in your on-premise environments.
Lets start with the steps.
1.Creating a Blob container in azure storage account for maintaining the audit files. (Azure portal)
Choose Blobs in your Storage account Overview page, and Click on the '+Container' option on the top. Give a meaningful name in all small caps and click on to create one blob container.
2.Creating Credentials in the managed instance for authentication.
We need to create SQL credentials in the managed instance where we will be configuring the aduit to authenticate the connection between the instance and Azure storage account and let the audit write its events to the blob container.
You will need to take a note of the Shared Access Signature key from the storage account which we will need to create the credential.
Click on Shared Access signature on the left pane of your storage account and fill in the details on expiry date for the SAS key, Allowed permissions, protocols like below. You could choose to keep a longer expiry date for the SAS key, so that the credentials would be valid for longer. Click on the button below for creating the SAS key.
Below the button, you should have the SAS token listed now. Click on the copy button the right to copy the SAS token text. When you are pasting it to a notepad/ssms window, make sure to remove the '?' sign in the beginning as it is not needed for credential creation. With the ? symbol, credential wouldn't work.
Go back to your SSMS and open a new query window against your managed instance and past the below query for credential creation.
The below blog content is intended to help those who are involved in SQL server migration to Azure managed instance and are having SQL audit already configured in their On-premise SQL server.
As you might know, SQL server Audit events can be written to event logs and audit files for the server level (Instance level) and Database level audit specifications. I would limit the scope of this blog to migration alone and would not get much into the details of the types of auditing we can do with SQL servers using the feature.There are plenty in both Database and Server level specifications. We do not have the luxury of accessing the VM behind the SQL Managed instance in Azure and hence cant use the option to write the audit events to Event logs. We can get the audit write its events to an Azure Storage blog container and the events can also be retrieved and seen in SSMS just like how we have in On-premise.
For database audit specifications' migration, we don't need to worry much as it will be already there in the migrated database in the managed instance.We can map the audit specifications to the audit created to make it working. For the Server/Instance level, I will be using the UI for adding a few events . You can create the ones which you already have in your on-premise environments.
Lets start with the steps.
1.Creating a Blob container in azure storage account for maintaining the audit files. (Azure portal)
Choose Blobs in your Storage account Overview page, and Click on the '+Container' option on the top. Give a meaningful name in all small caps and click on to create one blob container.
2.Creating Credentials in the managed instance for authentication.
We need to create SQL credentials in the managed instance where we will be configuring the aduit to authenticate the connection between the instance and Azure storage account and let the audit write its events to the blob container.
You will need to take a note of the Shared Access Signature key from the storage account which we will need to create the credential.
Click on Shared Access signature on the left pane of your storage account and fill in the details on expiry date for the SAS key, Allowed permissions, protocols like below. You could choose to keep a longer expiry date for the SAS key, so that the credentials would be valid for longer. Click on the button below for creating the SAS key.
Below the button, you should have the SAS token listed now. Click on the copy button the right to copy the SAS token text. When you are pasting it to a notepad/ssms window, make sure to remove the '?' sign in the beginning as it is not needed for credential creation. With the ? symbol, credential wouldn't work.
Go back to your SSMS and open a new query window against your managed instance and past the below query for credential creation.
Create CREDENTIAL [https://xxxxxxstorageac.blob.core.windows.net/dbinstanceaudit]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=sdsfsfsfp&se=2019-10-03T22:37:04Z&st=2019-09-06T14:37:04Z&spr=https,http&sig=fdfddgdgdgdgdgdgd2IJZOZro7MNVyVLlzI%3D';
dbinstanceaudit - blob container we have created in the storage account.
Value for 'Secret' - SAS key that we have copied from above.
xxxxxxstorageac is my storage account name with prefix changed for security reasons. You should replace is with your own storage account.
3. Creating SQL audit
Navigate to Security (Instance level) and Right click on the "Audits" and select "New Audit". You should be able to see the below window.
Clicking on browse will open a window to sign in to your azure subscription and select the storage account and the blob container.
I would leave the name of the audit and queue delay and retention period for you to decide or set as per the existing configuration in the on-premise. Queue delay is the parameter that decides how much the audit can wait (In milliseconds) before writing the event to the audit file.
4.Mapping Server and Database audit specifications to the Audit
Once we have the Audit created, please move on to providing the server audit specifications.
Right click on the Server Audit specifications under Security and add the specifications like how you have in your On-premise.Also select the audit which we have created in step3.
If your database that is migrated from the on-premise has database level audit specifications already configured, you should get to see them under database specifications like below.
Click on that and map the 'Audit' we have created.
Now the last step would be enabling the audit, server audit specifications and database audit spefications by right clicking on them and selecting 'Enable'.
You may verify the audit logs that are being captured by right clicking on the audit and choosing 'View audit logs'.
That brings us to the end of this blog. I will blog on another topic soon. Please keep visiting.
Please let me know if you want me to write on a topic.
