AzureHow to Secure Azure SQL Database April 17, 20169098 views0Share By IG ShareSecurity is always a concern for Customers & Organisations when they thing about moving to the cloud. Azure provides new security paradigms and robust security at physical, logical and data access layers for both Application and Databases. If you want to know how to secure Azure SQL Database look no further. Azure SQL Database is a cloud based “Database as a Service” offering from Microsoft. With the latest V12 Databases, you now have the option to protect your Databases by implementing multiple layers of security.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.Here are the options to secure your Azure SQL Database:Built-in Security on AzureFirewall and ACLS (Access Control Lists)Authentication with SQL or Azure AD (AAD)Manage Permissions using Database roles and Server rolesDatabase Auditing for Logging and monitoringData protection options using the following:Transparent Data EncryptionAlways EncryptedEncrypting Transit DataRow Level Security (RLS)Dynamic Data Masking (DDM)Read my earlier posts on Azure SQL Database Performance & Azure SQL on Virtual Machines.Azure databases already meets most regulatory and security requirements like HIPAA, ISO 27001/27002, and PCI DSS Level 1, among others. Visit the Microsoft Azure Trust Center site to see the complete list.Firewall per Azure SQL DatabaseAzure provides you firewall per database where you can explicitly specify who has access to the database. You have granular access over IP addresses and using the Azure Management Portal you can specify which IP addresses can connect to your database.Port 1433 has to be opened since Azure SQL Database does not work on any other port.You can manage the Firewall rules using the following options:Azure Management PortalT-SQLPowershellRest APIAuthentication:Azure SQL supports two types of AuthenticationAzure Active Directory (AAD)SQL AuthenticationAzure SQL Database extends existing authentication mechanism by allowing Azure AD users to login to the database using either the Principle Username & Password, Integrated Windows Authentication or Token based authentication.For more information see Connecting to SQL Database By Using Azure Active Directory AuthenticationBest Practices:Use separate login users and applications for each database to avoid malicious attacks or a SQL Injection attack.For V12 databases, use contained Database users, which directly authenticate against the Database instead of going to the master database, thereby increasing performance.User Server-Level roles for managing server level access and security and Database roles for managing Database level access.Use Database auditing which is available on all service tiers to tack logins and transaction management.Transparent Data Encryption(TDE)TDE is meant to encrypt data at rest and can be implemented by a push of a button. Just like SQL on premises, backups of TDE enabled databases are also encrypted.Always EncryptedIf you are looking to go beyond encrypting data at rest and encrypt data on disk as well as on memory, then Always Encrypted is the way to go. For .NET 4.6 and above, you can use the client libraries to encrypt the data on the client and not on the database. This way, even the server administrator or anyone with direct database access will not be able to decrypt the data. The encryption key remains in the application in a trusted location, such as “Azure Storage Vault”Encrypting Data in TransitYou can encrypt your SQL Database connections using TLS/SSL for the Tabular Data Stream (TDS) transfer of data. The new V12 now supports Transport Layer Security (TLS) 1.2 when connecting with the latest versions of theADO.Net (4.6)JDBC (4.2)ODBCSupport for ODBC on Linux, PHP, and node.js is coming soon. For Azure SQL Database Microsoft provides a valid certificate for the TLS connection to eliminate any possibilities of “man-in-the-middle” attacks.Row Level Security (RLS)Using Row-Level Security (RLS) you can restricts access to rows, using a security predicate that is defined as an inline table-valued function (TVF). You create a security policy to enforce this function.Code Sample:---------------------------------------------------------------- -- PART 1: What RLS looks like ---------------------------------------------------------------- -- RLS is already enabled in the sample database to limit access to sensitive data about -- AdventureWorks customers, contained in the Sales.CustomerPII table. The access logic is: -- - Sales Persons should only be able to view customers who are in their assigned territory. -- - Managers and VPs in the Sales org should be able to see all customers. -- EXAMPLE 1: -- The user 'michael9' is a member of the SalesPersons role, so he can only access customers -- who are in his assigned territory (Territory 2): EXECUTE AS USER = 'michael9' go -- Only customers for Territory 2 are visible (other territories are filtered) SELECT * FROM Sales.CustomerPII go -- Cannot update or delete customers who are not in Territory 2 (other territories are filtered) DELETE FROM Sales.CustomerPII WHERE TerritoryID = 10 -- 0 rows affected UPDATE Sales.CustomerPII SET FirstName = 'Changed' WHERE TerritoryID = 9 -- 0 rows affected go -- Blocked from inserting a new customer in a territory not assigned to him... INSERT INTO Sales.CustomerPII (CustomerID, FirstName, LastName, TerritoryID) VALUES (0, 'Bad', 'Customer', 10) -- operation failed, block predicate conflicts -- ...but can insert a new customer in a territory assigned to him INSERT INTO Sales.CustomerPII (CustomerID, FirstName, LastName, TerritoryID) VALUES (0, 'Good', 'Customer', 2) -- 1 row affected -- Blocked from updating the territory of an accessible customer to be in an unassigned territory UPDATE Sales.CustomerPII SET TerritoryID = 7 WHERE CustomerID = 0 -- operation failed, block predicate conflicts -- Reset the changes DELETE FROM Sales.CustomerPII WHERE CustomerID = 0 go REVERT go -- EXAMPLE 2: -- On the other hand, the user 'amy0' is a member of the SalesManagers role, so she can access -- all customers in all territories: EXECUTE AS USER = 'amy0' -- Manager go SELECT * FROM Sales.CustomerPII -- all customers are visible go REVERT go ---------------------------------------------------------------- -- PART 2: How to configure RLS ---------------------------------------------------------------- -- Let's change the security policy to support a common scenario for web applications. In this scenario, -- Sales Persons connect to the database through a middle-tier application using a shared SQL login. To -- identify the current application user in the database, the application will store the current user name -- in the SESSION_CONTEXT immediately after opening a connection. This way, the RLS policy can filter rows -- based on the user name stored in SESSION_CONTEXT. -- First, create a shared SQL login for the application's connection string CREATE LOGIN ApplicationServiceAccount WITH PASSWORD = '{SomeStrongPassword}' CREATE USER ApplicationServiceAccount FOR LOGIN ApplicationServiceAccount GRANT SELECT, INSERT, UPDATE, DELETE ON Sales.CustomerPII TO ApplicationServiceAccount go -- To set the SESSION_CONTEXT, the application will execute the following each time a connection is opened: EXEC sp_set_session_context @key=N'user_name', @value=N'michael9' -- for example, the Sales Person from above go -- Now, this user name is stored in the SESSION_CONTEXT for the rest of the session (it will be reset when the -- connection is closed and returned to the connection pool). SELECT SESSION_CONTEXT(N'user_name') go -- Reset for now EXEC sp_set_session_context @key=N'user_name', @value=NULL go -- We need to change our security policy to filter based on the user_name stored in SESSION_CONTEXT. To do this, -- create a new predicate function that adds the new access logic. As a best practice, we'll put the function in a -- separate 'Security' schema that we've already created. CREATE FUNCTION Security.customerAccessPredicate_v2(@TerritoryID int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS accessResult FROM HumanResources.Employee e INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = e.BusinessEntityID WHERE -- SalesPersons can only access customers in assigned territory ( IS_MEMBER('SalesPersons') = 1 AND RIGHT(e.LoginID, LEN(e.LoginID) - LEN('adventure-works')) = USER_NAME() AND sp.TerritoryID = @TerritoryID ) -- SalesManagers and database administrators can access all customers OR IS_MEMBER('SalesManagers') = 1 OR IS_MEMBER('db_owner') = 1 -- NEW: Use the user_name stored in SESSION_CONTEXT if ApplicationServiceAccount is connected OR ( USER_NAME() = 'ApplicationServiceAccount' AND RIGHT(e.LoginID, LEN(e.LoginID) - LEN('adventure-works')) = CAST(SESSION_CONTEXT(N'user_name') AS sysname) AND sp.TerritoryID = @TerritoryID ) go -- Swap this new function into the existing security policy. The FILTER predicate filters which rows -- are accessible via SELECT, UPDATE, and DELETE. The BLOCK predicate will prevent users from INSERT-ing or -- UPDATE-ing rows such that they violate the predicate. ALTER SECURITY POLICY Security.customerPolicy ALTER FILTER PREDICATE Security.customerAccessPredicate_v2(TerritoryID) ON Sales.CustomerPII, ALTER BLOCK PREDICATE Security.customerAccessPredicate_v2(TerritoryID) ON Sales.CustomerPII go -- To simulate the application, impersonate ApplicationServiceAccount EXECUTE AS USER = 'ApplicationServiceAccount' go -- If the application has not set the user_name key in SESSION_CONTEXT (i.e. it's NULL), then all rows are filtered: SELECT * FROM Sales.CustomerPII -- 0 rows go -- So the application should set the current user_name in SESSION_CONTEXT immediately after opening a connection: EXEC sp_set_session_context @key=N'user_name', @value=N'michael9' -- assume 'michael9' is logged in to the application go -- Only customers for Territory 2 are visible SELECT * FROM Sales.CustomerPII go -- Application is blocked from inserting a new customer in a territory not assigned to the current user... INSERT INTO Sales.CustomerPII (CustomerID, FirstName, LastName, TerritoryID) VALUES (0, 'Bad', 'Customer', 10) -- operation failed, block predicate conflicts go REVERT go -- Reset the changes EXEC sp_set_session_context @key=N'user_name', @value=NULL go ALTER SECURITY POLICY Security.customerPolicy ALTER FILTER PREDICATE Security.customerAccessPredicate(TerritoryID) ON Sales.CustomerPII, ALTER BLOCK PREDICATE Security.customerAccessPredicate(TerritoryID) ON Sales.CustomerPII go DROP FUNCTION Security.customerAccessPredicate_v2 DROP USER ApplicationServiceAccount DROP LOGIN ApplicationServiceAccount go -- Final note: Use these system views to monitor and manage security policies and predicates SELECT * FROM sys.security_policies SELECT * FROM sys.security_predicates goDynamic Data MaskingIf you do not want to go into the complications of encrypting the entire database, or use cases like credit cards or social security where you want to see a part of your data, you can go for “Dynamic Data Masking”.In order to implement DDM, you would require the following components:Privileged SQL users: These SQL users always have access to unmasked data.Masking function: This set of methods controls access to data for different scenarios.Masking rules: This set of rules defines the fields to mask and the masking function.Code Sample:-- This demo uses the Sales.CustomerPII table in the AdventureWorks2016CTP3 sample database -- to demonstrate how Dynamic Data Masking (DDM) can be used to mask (fully or partially) data -- in sensitive columns. -- USE AdventureWorks2016CTP3 go -- DDM is already enabled in the sample database to mask the EmailAddress and PhoneNumber columns -- in the Sales.CustomerPII table. -- If you are connected as 'dbo', you will always see unmasked data: SELECT * FROM Sales.CustomerPII go -- Unprivileged users see masked data by default. For example, this SalesPerson 'michael9' will -- see masked data: EXECUTE AS USER = 'michael9' SELECT * FROM Sales.CustomerPII -- EmailAddress and PhoneNumber are masked REVERT go -- Granting users or roles the UNMASK permission will enable them to see unmasked data: GRANT UNMASK TO SalesPersons -- role go EXECUTE AS USER = 'michael9' SELECT * FROM Sales.CustomerPII -- EmailAddress and PhoneNumber are no longer masked REVERT go -- Reset the changes REVOKE UNMASK TO SalesPersons go -- DDM is configured in the table schema. For example, if you have the ALTER ANY MASK permission, -- you can remove a mask on a column like this: ALTER TABLE Sales.CustomerPII ALTER COLUMN EmailAddress DROP MASKED go -- And you can add a mask like this: ALTER TABLE Sales.CustomerPII ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()') go -- You can also edit a mask with a different masking function. This shows how to define a custom -- mask, where you specify how many characters to reveal (prefix and suffix) and your own padding -- string in the middle: ALTER TABLE Sales.CustomerPII ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'partial(2, "zzz@abab", 4)') -- New mask for email go ALTER TABLE Sales.CustomerPII ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'partial(0, "111-111-11", 2)') -- New mask for phone go -- See how that masks now: EXECUTE AS USER = 'michael9' SELECT * FROM Sales.CustomerPII -- New custom masks for EmailAddress and Phone REVERT go -- Reset the changes ALTER TABLE Sales.CustomerPII ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()') go ALTER TABLE Sales.CustomerPII ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'default()') go -- Try doing a SELECT INTO from the table with a mask into a temp table (with a user that doesn't -- have the UNMASK permission), and you'll find that the temp table contains masked data: EXECUTE AS USER = 'michael9' SELECT CustomerId, EmailAddress, PhoneNumber INTO #temp_table FROM Sales.CustomerPII -- Masked Email and Phone SELECT * FROM #temp_table -- temp table has masked data DROP TABLE #temp_table REVERT goReferences:Securing your SQL Databasehttps://azure.microsoft.com/en-in/documentation/articles/sql-database-security-guidelines/Azure SQL Database security guidelines and limitationshttps://azure.microsoft.com/en-in/documentation/articles/sql-database-security/SQL Azure: Cloud Database Securityhttps://technet.microsoft.com/en-us/hh352139.aspx Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
AzureMove & Load Data to Azure SQL DataWarehouse using Data Platform StudioThere are multiple ways to populate data into Azure SQL DataWarehouse PAAS (Platform as a ...
SQLHow to Upgrade to SQL Server 2016 from Earlier and Older VersionsHow to Upgrade to SQL Server 2016 from Earlier and Older Versions SQL Server 2016 ...
Enterprise TechAuditing Features in SQL Server 2016 and Azure SQL DatabaseAuditing Features in SQL Server 2016 and Azure SQL Database SQL Server auditing features allow ...
Enterprise TechHow to Replicate your Data to an Azure VM with SQL StandardHow to Replicate your Data to an Azure VM with SQL Standard SQL Server Standard ...