Database Security (part 1 of 4)

By Ashwin Venugopal -

 



To read part 2 please click here
To read part 3 please click here
To read part 4 please click here


SQL Database Authentication

Authentication and authorization

Authentication is the process of proving the user is who they claim to be and when a user attempts to connect to a database, they provide a user account and authentication information. There are two authentication methods:

  • SQL authentication- With this authentication method, the user submits the user account name and associated password to establish a connection which is stored in the master database in the user accounts linked to a login or stored in the database containing the user accounts not linked to a login.
  • Azure AD authentication- With this authentication method, the user submits a user account name and requests that the service use the credential information stored in Azure AD. 

You can create user accounts in the master database, and grant permissions in all databases on the server, or you can create them in the database itself called contained database users, through which you can obtain enhance portability and scalability.

Login and users: In Azure SQL, a user account in a database can be associated with a login that is stored in the master database or can be a user name that is stored in that is stored in an individual database.

  • A login is an individual account in the master database, to which the user account in one or more databases can be linked. With a login, the credential information for the user account is stored with the login.
  • A user account is an individual account in any database that may be but does not have to linked to a login. With the user account that is not linked to a login, the credential information is stored with the user account.

Authorization to access data and perform various actions are managed using database roles and explicit permissions while it is controlled by your user account's database role memberships and object-level permissions. As the best practice, you should grant users the least privileges necessary and the recommended approach is to create a contained database user, which allows your app to authenticate directly to the database.

Use Azure AD authentication to centrally manage identities of the database users and as an alternative to the SQL Server authentication.

SQL Database Firewalls

Configure an SQL database firewall

Azure SQL Database and Azure Synapse Analytics, previously SQL Data Warehouse offers a relational database service for Azure and other internet-based applications. Firewalls prevents all access to your database server until you specify which computers have permission and grant access to the database based on the originating IP address of each request, to help protect your data.

In addition to IP rules, the firewall also manages virtual network rules which are based on virtual network service endpoints and might be preferable to IP rules in some cases.

Connecting from the internet

When a computer attempts to connect to your database server from the internet, the firewall first checks the originating IP address of the request against the database-level IP firewall rules for the database that the connection is granted to the SQL database that the connection is requesting:

  • If the IP address of the request is within one of the ranges specified in the database-level IP firewall rules, the connection is granted to the SQL Database containing the rule.

  • If the IP address of the request is not within one of the ranges specified in the database-level IP firewall rules, the firewall checks the server-level IP firewall rules and if the request is within one of the ranges specified in the server-level IP firewall rules, the connection is granted.

  • If the IP address of the request is not within the ranges specified in any of the database-level or server-level IP firewall rules, the connection request fails.

Connecting from Azure

When an application from Azure attempts to connect to your database server, the firewall verifies that Azure connections are allowed, but, if the connection attempt is not allowed, the request does not reach the Azure SQL Database Server. 

When selecting this option, make sure your sign-in and user permissions limit access to authorized users only. 

Server-level IP firewall rules

Server-level IP firewall rules enables the clients to access your entire Azure SQL Database, i.e, all the databases within the same SQL Database server. These rules are stored in master database.

You can easily configure server-level IP firewall rules using the Azure Portal, PowerShell, or by using Transact-SQL statements. To create a server-level IP firewall rules by using Transact-SQL, you must connect to the SQL Database instance as the server-level principle login or the Azure AD administrator.

Database-level IP firewall rules

Database-level IP firewall rules enable the clients to access certain secure databases within the same SQL Database Server. You can only create and manage Database-level IP firewall rules for the master databases and user databases by using Transact-SQL statements, and only after you have configured the first server-level firewall. You can have a maximum of 128 database-level IP firewall rules for a database.

Whenever possible, as the best practice, use database-level IP firewall rules to enhance security and to make your database more portable.   



To read part 2 please click here
To read part 3 please click here
To read part 4 please click here





Comments

Post a Comment

Popular posts from this blog

Query, Visualize, & Monitor Data in Azure Sentinel

By Ashwin Venugopal -
Image
  Azure Sentinel Workbooks Several ready for use templates are provided by the Azure Sentinel that can be used to create your own workbook and then modify them as needed for Contoso. Most of the data connectors it uses to ingest data come with their own workbooks, but better insight can be obtained by simply looking into the data that is being ingested by using tables and visualizations, including bar and pie charts. You can also make your own workbook easily by using this data from the beginning instead of using the predefined templates. Workbook Page You can easily access the workbook page from the Azure Sentinel from the navigation pane which consists of the: Workbook header- You can add a new workbook and review the saved workbooks as well as templates that are available on the workbook page. Templates section- You can access existing workbook templates on the Templates tab. You can save some of the workbooks for quick access and they will appear on the My Workbooks tab.  From the
Read more

Planning for Implementing SAP Solutions on Azure (Part 2 of 5)

By Ashwin Venugopal -
Image
  To read part 1 please click  here To read part 3 please click  here To read part 4 please click  here To read part 5 please click  here Load Balancing The load balancer uses probe ports to determine as well as route the traffic to an active DBMS node, but of there's any failover then the most common SAP application architecture can automatically reconnect against the private virtual IP address without the need for the SAP application to reconfigure it, while the load balancer redirect the traffic against the private virtual IP address without any need for the SAP application to reconfigure. The loaf balancer also offers an option of DirectServerReturn which if configured, can help in directing the traffic from the DBMS VM to the SAP application layer directly to the application layer without routing through the load balancer. But, if it isn't configured, then the return traffic to the SAP application layer is routed through the load balancer. You can also use Azure Load Balan
Read more

Work with String Data Using KQL Statements

By Ashwin Venugopal -
Image
  Extract Data from Unstructured String Fields Unstructured string fields often contains the Security log data and requires parsing to extract data. There are multiple ways of pulling information from string fields in KQL and the two primary operators used are extract and parse.  Extract It gets a match for a regular expression from a text string. You can convert the extracted substring to the indicated type.  Arguments regex- A regular expression. captureGroup- A positive int constant indicating the capture group to extract. 0 stands for the entire match, 1 for the value matched by the first '('parenthesis')' in the regular expression, 2 or more for subsequent parenthesis.  text- A string to search. typeLiteral- An optional type literal. If provided, the extracted substring is converted to this type.  Returns If regex finds a match in text- the substring matched against the indicated capture group captureGroup, optionally converted to typeLiteral. If there's no mat
Read more