Deepthi Goguri

Deepthi is the Founder of dbanuggets and a SQL Database Administrator. As a Microsoft MVP, she helps SQL communities by sharing her expertise in resolving SQL Server problems globally.

• LinkedIn
• X
• GitHub

I know it’s been a while since I blogged. It’s been busy at work and I felt I had to take some time off. Firstly, I would like to apologize to the regular readers of this blog and I would like to thank some of them for remembering me and reaching out to me asking to write. Thanks for checking on how I was doing.

As a continuation of the Quick track series on beginner’s guide to Azure SQL, this post is about Auditing in Azure SQL.

As you all know how crucial it is to Audit activity on the Server for both prod and non-prod environments, turning on the auditing in Azure SQL is pretty simple and the results we see in the audit log are similar to the logs we see on-prem. The difference is where we save the audit data in Azure.

You can enable the auditing at the Server level and at the database level just like the way we can audit SQL Server on-prem. If you would like to enable audit at the Server level in Azure, it will automatically audit all the databases under that Server. If we allow the auditing at the server level (logical Server for Azure SQL Databases) and also at the database level, we might get double the amount of collected audit data as it contains the same data twice. Always chose the Storage account if you wanted to audit the data at the Server level. If you just want to collect the audit data on one or some databases only, you can disable the logical Server level audit and enable the Auditing at the database level.

If you want to specifically use a different storage method from the other audited databases or wanted to modify the default action policy groups to any specific databases, you can choose the database level auditing instead of the Server level auditing. By default, the login activity, queries, and stored procedures are audited.

The default audit policy for the Server and the Azure SQL databases includes

1. BATCH_COMPLETED_GROUP –
2. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
3. FAILED_DATABASE_AUTHENTICATION_GROUP

To enable the Auditing at the logical Server level- Go to the auditing blade on the left under the logical Server> Enable SQL Server Auditing> turn on.

Chose the audit log destination among the storage/Log analytics/Event Hub. You need to select the subscription and provide the log analytics workspace. If you already have one, select it or create one right here if you do not have one. If you need any kind of support from the Microsoft support team in the future, make sure to enable the option “Enable Auditing of Microsoft support operations” so they can have access to your logs when they work with you.

If you are choosing Server level auditing, then select the storage as destination and you will have to choose the authentication type: Managed identity (system-assigned and user-assigned managed identity is supported as per Microsoft). You can choose the retention period here as well. 0 represents unlimited retention. Changes made to the retention period will only effect the future collection of the Audit data. Any collected information while it was set to unlimited will be always retained.

To specifically enable the auditing at the database level, go to the database and select the auditing blade on the left side> Enable Azure SQL Auditing> Turn on

To view the audit logs of the database>go to the auditing blade on the left side>click auditing>click on view audit logs.

You can see the audit logs of the database. You will see the query where you can edit and filter the results.

If you would like to modify or view the audit policy through Powershell, there are some command lets you can use.  Source: Microsoft

Get-AzSqlServerAudit – Will give the Server level audit policy

Set-AzSqlServerAudit  – You can create or modify the Server audit policy

Remove-AzSqlServerAudit  – use to remove Server audit policy 

Get-AzSqlDatabaseAudit – Will give the database-level audit policy

Set-AzSqlDatabaseAudit  – You can create or modify the database audit policy

Remove-AzSqlDatabaseAudit – use to remove the database audit policy 

Here is an interesting question asked by a user on how to filter out specific login as it causes a huge amount of data. You can find the resolution here.

You can also check the audit logs from the home blade>monitor>logs. You can see the same audit information here.

For more examples on how to add or modify the auditing policy, here is the article from Microsoft you can follow.

Hope this blog post gave you an overview of auditing in Azure SQL.

Thanks for reading!

Deepthi Goguri

Deepthi is the Founder of dbanuggets and a SQL Database Administrator. As a Microsoft MVP, she helps SQL communities by sharing her expertise in resolving SQL Server problems globally.

• LinkedIn
• X
• GitHub

Azure Defender for SQL, once you enable it will alert you for any SQL injection attacks, brute force attacks or any breached identities trying to access the data of your database. It also provides the vulnerability assessments. Vulnerability assessments give you alerts about the configurations of your database. If your database configuration is not following the standards of Azure, you will receive the alerts in the vulnerability assessment report.

You can enable the Azure Defender at the subscription level or at the Server level or at the resource level as well. Under the recommendations in the security center in the Azure portal, check for the Remediate security configuration. This will show if the Azure defender is configured properly.

Azure defender for SQL consists of two features – Advanced Threat Protection and Vulnerability Assessment.

Advanced Threat Protection

Threat protection will send the alerts if there is any malicious activity going on against your database. You will receive alerts if there are any SQL injection attacks, any suspicious login attempts from new locations.

Vulnerability Assessment

Vulnerability assessment will check for the security misconfigurations based on the Azure standards. It will provide a baseline based on the standards which you can later accept and add these baselines based upon your environment. It will also provide the actionable steps to take and remediate the alert.

Azure defender supports Azure environments and also hybrid environments. For the complete list of where the Azure defender is supported, please check the Microsoft article here.

You can view all the defender collected information in the centralized view at Azure Security Center.

Vulnerability assessment requires the storage account for the PaaS service. This can be setup at the logical SQL Server level. For the IaaS service, you don’t have to specify the storage account separately.

Once you turn on the Azure Defender, we need to give sometime to collect the information and scan the system for any vulnerabilities. At the server level, Go to the Microsoft Defender for Cloud. You can also open at the resource level as well.

To view the details, click on the additional recommendations.

You can also see the recommendations from the resource level as well. Click on the Microsoft defender for cloud from the database level.

Scroll down to see the details

Let’s see the example of the first vulnerability assessment finding. The security check shows Database owners are as expected. Click on the finding to see more details.

You can either look into the details and add the recommendations to the baseline or ignore. This is completely based on your needs and your current environment.

Azure Sentinel

Azure sentinel will help in identifying the security incidents that we need to investigate. You need to create a log analytics workspace to add to Azure sentinel. Multiple data connectors can be used to connect and collect the information from these different connectors and analyze the data for the security incidents.

You can use different dashboards to view the collected data and alerts. For example, you can use the audit logs dashboard to view more details on the alerts. You can setup the alerts for the events. You can also create the playbooks to automate the response to specific alerts using the logic apps.

Learn how to setup the Azure Sentinel here.

In the next blog post, we will learn about the Auditing in Azure SQL.

References

1. Overview of Microsoft Defender for Azure SQL
2. Azure Defender for SQL

Thanks for reading!

Deepthi Goguri

Deepthi is the Founder of dbanuggets and a SQL Database Administrator. As a Microsoft MVP, she helps SQL communities by sharing her expertise in resolving SQL Server problems globally.

• LinkedIn
• X
• GitHub

Data needs to be protected no matter where it lives, On-prem or in Azure. Data can be protected by using the encryption that Azure provides. What are the types of encryption we have in Azure?

Encryption in Transit: The data needs to be protected as it moves all the way from the database to the client application and vice-versa. The encryption is always by default enforced by TLS (Transport Layer Security). Making sure your application supports the TLS version you choose on your server is important. If for example, you chose the TLS version 1.2 on your server and your client application only supports the 1.2 TLS version, your application cannot connect to the server.

As a best practice from Microsoft, client applications should have the encryption enabled on their connection string and not trust the Server certificate.

Encryption-at-rest: Data at rest on the disks- which includes the data files, log files and backup files. These are encrypted with the technology called Transparent Data Encryption.

This option is enabled by default as well. As the data is moved in and out of the drives, the data is being protected through the transparent data encryption. By default, Microsoft is going to manage the keys known as Microsoft-managed keys but also you can choose bring your own keys (BYOK). In the default option Microsoft-managed keys, the responsibility of the key generation, key rotation and the management of these keys are taken care by the Microsoft. All these keys need to be stored in the Key vault which is a Microsoft product. In the second option BYOK, the customer is responsible for the management of the keys including the key generation, key access to the users and key rotation. The third option we have is Hold your own key (HYOK) which is a IaaS offering only where the key generation including the key management and storing the keys is totally taken care by the customer.

Double layer of encryption is where you can chose the TDE along with the infrastructure encryption (volume encryption). This is an additional layer of security for your data on disk. The second layer Infrastructure encryption is optional choice.

In the case of customer managed key, the server identity should have the access to the key vault. So, when the server wants to encrypt and decrypt the data on the drive, it will use this key. The first step to create the customer managed key is to create the Key Vault if you do not already have one.

Once you created the Key vault, choose the customer managed key. To select a key, first we need to create a key that is saved in the key vault. Here I have created two keys.

Then go to the access policies on the Key vault and give the server (Securitydemo) enough permissions to access the key. That requires Wrap and unwrap permissions.

Now go to the transparent data encryption and choose the key by clicking on the Select a key and then click on change key.

Choose the key you have created and click on select

Once you select the key, click on Save on the transparent data encryption page.

Encryption-in-use: It encrypts the data during the query processing and the technology used is always encrypted. This is the client side encryption technology. Cryptographic keys that are used to encrypt and decrypt the data are never shared with anyone. The SQL client driver in between the client application and the database will serve as the mediator to encrypt and decrypt the data as the data moves in and out of the database. Initially only the equality searches were only supported but to add more functionality in supporting the range queries, pattern matching, sorting, indexing and more operations, a new technology is being introduced in SQL Server 2019 known as Secure Enclaves.

Secure Enclave is a part of memory in the SQL Server that no one can access and this part of memory is used to process the computational operations involving the sensitive encrypted column. In this part of the memory, the secure enclave will have the data which is decrypted safely and queries can be processed here.

How do we know that the Secure enclave is secure to process the sensitive data? There is a service known as Microsoft Azure attestation which will check if the secure enclave is safe and process the queries. By using the Secure Enclaves, even the windows administrators or DBA’s cannot access the sensitive data on always encrypted columns.

Dynamic Data Masking

Dynamic Data masking is a protection of the data from the application users who should not have access to specific information. This is not the encryption where the files are encrypted.

This is limiting the access to the data where the data is masked on the fly when the data is moved from the database to the application. As the data reaches to the application, only the needed information needs to be shown to the application users. You can use the custom built functions or create the custom functions to mask the data.

Ledger

Ledger is a technology which uses blockchain technology to protect the data tampering from the privileged users who have access to the sensitive data. This involves the ledger tables built in as you enable this feature on the database.

Updatable ledger tables allow to capture the data of updates and deletes. The updated rows will be stored in the history tables. The database digests using the block chain technology is saved in the trusted Azure storage where database digests are used to verify the database whether the data in it is being tampered.

Because the Ledger captures all the changes happening to the data in the database and if the tampering happens through application, these changes can be also be identified through the ledger views. This is one of the great features in Azure in protecting the data.

This blog post covered multiple data protection technologies available in Azure. In upcoming blog post, we will learn about the next security layer Threat prevention and detection.

Thanks for reading!

References:

Azure SQL Security: Data Protection

Deepthi Goguri

Deepthi is the Founder of dbanuggets and a SQL Database Administrator. As a Microsoft MVP, she helps SQL communities by sharing her expertise in resolving SQL Server problems globally.

• LinkedIn
• X
• GitHub

Authentication and Authorization are the two things that we need to get familiar with when we learn about the Access management.

Authentication is proving that we are the same user who we say we are. Below are the authentication methods available in Azure.

SQL Authentication: Azure SQL Database and for the Managed instance (MI) supports SQL authentication. This is just like any regular SQL Authentication account with username and the password. When you create the server, you will need to create the SQL authentication account which will become the server admin that will have database owner on all the databases. This account can create other SQL authentication accounts on the server as well.

Azure Active Directory: There is a centralized location for the identities and permissions to database users to be stored for the Azure active directory authentication. As the password is only stored at one location, it becomes easier for the administrators to manage the identities and for maintaining the passwords. This authentication is supported for Azure SQL database, Managed instance (MI) and Azure synapse analytics. The Active directory admin will become the server admin where to create other AD logins, only the Active directory admin can create the other AD accounts having permissions to Azure SQL database. Multi factor authentication option is available for this option. Authentication app can be used as the multi factor authentication where it provides phone call/text message/authentication app to confirm the identity. This authentication type will use the contained database users to connect to the database to access the database objects.

Active directory will support the users with the login id and password, Integrated (single sign-on) authentication used when there is a trust built between the Active directory and the Azure AD using windows authentication. Universal/interactive with multi factor authentication is where the password is generated dynamically. There is a token authentication where a token is generated from Azure AD and as we pass this token to the SQL. Source: here.

One of the important point to remember is, the Azure active directory admin has to create the Azure active directory login. SQL authentication logins cannot be created by Azure AD logins. The other type of users that the Azure AD supports are individual user accounts, group accounts, service principles.

To create the Azure AD account, you can create the account by running on virtual master database. please see sample code to create Azure AD login here.

Lets learn a bit about the differences in the logins and users in between the managed instance and Azure SQL database

Managed Instance: You can create the Azure AD server admin along with the SQL server admin. You can create the SQL logins even when you only enable Azure AD only authentication but you cant connect until you enable the SQL authentication. You can create the SQL or the Azure AD logins, Database users and database contained users. You can also create the Server trust group for the distribution transaction scenarios between the two instances.

Azure SQL Database: You can create the Azure AD Server admin along with SQL logins. Additionally you can have roles like loginmanager and dbmanager for limited server admins. You can create the users associated with the SQL logins, create contained users including Azure AD. To create these contained users, the user must be login using the Azure AD server admin.

There are two configuration options for the authentication:

Cloud only option- where you can create the AD azure accounts with out having them connected to the on-prem servers or you can federate services with single sign-in option, Azure AD storing password as a Hash and Azure SQL passthrough with single sign-on is where the password stored in on-prem.

Azure SQL Authentication supports Azure AD authentication or SQL authentication or Azure AD only authentication (disables the SQL authentication automatically).

Azure Role-Based Access control (RBAC) is what can and cannot be done as a user. We have built in roles in azure where we can assign access to the users and groups permissions all the way from the subscription to the individual resource groups and resources. These permissions can be applied to each level of the hierarchy of resources.

There are three RBAC built in roles that we need to remember:

Owner: Owner can do everything and assign other users as well.

Contributor: Can only modify but cant assign other userd

Reader: Can only read the assigned resource

Custom roles can also be created. Once these roles are created, you can assign the users to these groups.

For the SQL permissions, you can use the built in roles or the custom roles making sure you give the least privileges needed. To provide the least privileges, for the azure SQL database the AD user will be assigned to the database user which will have permissions to the database role connecting to the database. With the Azure SQL managed instance, the login at the server level will be associated and connected to the database user which is assigned to the database roles.

The other important thing to remember is that when the owner of the tables and the views objects are the same, we can only grant the user permissions to access the views instead of granting the permissions to tables for security reasons but the user can now access the tables through the views without having to have the access to the tables. This is called Ownership chaining.

Row level security: Securing the data by only providing access to the rows that the users are allowed to see. This comes under the least privilege access as well.

In order to implement the row level security, we need to create the SQL object known as security policy which is assigned to the table where it will filter the rows and provide only the information that is allowed to share.

I did not show any code examples here in this post because we already have several resources in place showing the examples of how to create the logins and users. Please check here and here

In the coming blog post, I will be writing about threat protection.

Thanks for reading!

Resources:

Azure SQL Security: Understanding Access and Authentication (Ep. 2) | Data Exposed

Deepthi Goguri

Deepthi is the Founder of dbanuggets and a SQL Database Administrator. As a Microsoft MVP, she helps SQL communities by sharing her expertise in resolving SQL Server problems globally.

• LinkedIn
• X
• GitHub

Let’s learn a bit about Security in this post.

Securing something you built is as important as creating it. Any resource that you create needs to have in depth layers of protection to save it from the bad eyes.

Let’s take the analogy of the Bank systems where you save your money and other valuables. Starting from the parking lot of the bank till the door of the bank vault where the actual money is stored, there is multi layer of security. Cameras, digital locks, padlocks and more locks to reach the money. This is in depth security system. Breaking any layer can only result in more effort to crack an another layer which is even more difficult.

Same goes with the Data. No matter you are on-prem or in the cloud, Data needs to be protected and there are no exceptions.

Securing data in Azure is an important part and there are different security layers available in Azure. Below diagram shows you the different layers of Security we have in Azure to reach the customer data.

In this post, let’s focus on the Network security.

Network Security

The below table will give an idea about different Network security options available and the differences among them.

In any specific region you have hosting the Azure SQL Databases, your client can connect to Azure SQL database using the Gateways having the public ip addresses. Gateways are the intermediate layer in between the Azure SQL database and the client. Client can connect to Azure SQL database through these gateways in two ways.

Proxy: We can understand this by simple analogy. You want to visit your friend’s apartment frequently but at the security gate, the watch man asks you several questions where you need to prove your authenticity to enter into your friend’s home every time you want to visit him. That is the proxy mode. Here, the DNS will resolve from one of the multiple gateways through port 1433. Each time the client want to connect, the gateway will act as a proxy in between to connect to the database. Proxy mode is the default mode and if you are trying to connect from outside Azure.

Redirection: You want to visit your friend’s apartment frequently and this time, you will receive additional key to the apartment at the entrance as you prove your authentication. Once you receive the key, you can directly go to the apartment with out any intermediate layer of security check at the gate. In the redirection mode, a redirect token is assigned when the client connects to the gateway and all the connections made to the database next doesn’t have to pass through the gateway. The redirect token contains the ip address of the database and the port in between the range 11000 to 11999. Redirection mode is default if you are trying to connect to the database within azure.

These gateways are known as data slices when they are logically grouped together. These data slices are used for the load balancing.

Let’s learn about each of the network security layer:

1. Allow access to Azure Services: Anything in azure can connect to your database if you enable this option. Any azure resource from any subscription and from any region. On-prem Servers cannot connect using this option. This option is a kind of the firewall rules where the start and end ip addresses were all zeros.

2. Firewall rules: These are the firewall rules that you create like any other on-prem Servers. By adding the client ip addresses here in the firewall rules, the client can connect to the Azure SQL database. On-prem servers can connect to Azure by using this option.

3. Virtual network rules: Clients from the specific virtual network subnets can connect to the Azure SQL through the private IP address.

4. Private Link: Private link is creating a private ip address from the virtual network which can connect to your database privately. Internet will be disconnected here. To create a private endpoint, we need to know what resource type you are creating the private endpoint for. It can be Azure SQL Database or the storage account, then on which virtual network and subnet we are going to create this endpoint.

Here is the youtube link you can follow to create a Private link for your database.

In the next article, we will be learning about the next security later, Access Management.

Thanks for reading!