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

Deployment (Part 3)

By Ashwin Venugopal -
Image
  To read part 1, please click  here To read part 2, please click  here Microsoft Monitoring Agent (MMA) MMA is used across multiple Microsoft solutions and has undergone a few name changes as its features and functionality have evolved. If it is being considered to be used, then following areas should be covered during a Microsoft Sentinel deployment project: Consider the timelines and the strategy for a migration to AMA (Azure Monitor Agent) before the MMA end-of-life.  Consider the central deployment and management methodology for MMA. Determine which endpoints are in scope for deployment as these affect the log ingestion volume significantly.  Azure Monitor Agent (AMA) Organizations with MMA as the main Microsoft Sentinel agent should start planning the migration to AMA as soon as possible to avoid a rushed migration. One of the main advantages of AMA is the ability to control the log collection policy centrally and apply different policies against different groups of computers, re
Read more

Project Resourcing (Part 2)

By Ashwin Venugopal -
Image
  To read part 1, please click  here Engineering - SIEM The SIEM engineers configures Microsoft Sentinel, including Log Analytics, Logic Apps, workbooks, and playbooks. They are also responsible for the following high-level tasks: Initial configuration of Azure tenant, including provisioning required resources, assigning access roles, and configuring workspace parameters like log retention, resource tagging, and blueprints.  Deployment and configuration of syslog/CEF log collection agents in appropriate locations to collect logs from on-premises devices. Generally, it will be joint effort of both system engineer and SIEM engineer, involving configuration and potential troubleshooting.  Working with system owners to enable log forwarding and configuring any required parsing of log data in Log Analytics.  Working with security operations to create and deploy KQL analytic rules to offer detections for SOC/Computer Security Incident Response Team (CSRIT) use. Tuning of alert rule parameter
Read more

Design Planning (Part 3)

By Ashwin Venugopal -
Image
  To read part 1, please click  here To read part 2, please click  here Complex Organizational Structures SIEM can be an expensive security control, hence, it is common multiple businesses to contribute to the overall expense. Various organizational units may require a level of access to specific dashboards or sets of data. Microsoft Sentinel offers the ability to assign table-level permissions and limit the level of access to the minimum required to perform requisite job functions.  Role-based Access Control (RBAC) Requirements Microsoft Sentinel offers an extensive list of Azure built-in roles that can be used to provide granular access according to the job requirements and permitted level of access. Some of them are various Microsoft Sentinel dedicated roles: Microsoft Sentinel Contributor- Can perform all engineering-related configuration, such as creating alert rules, configuring data connectors, and additional similar tasks.  Microsoft Sentinel Reader- Can query the log data stor
Read more