Private preview of Data Virtualization in Azure SQL Database

by Contributed | Sep 15, 2023 | Technology

This article is contributed. See the original author and article here.

We are very happy to announce the private preview of Data Virtualization in Azure SQL Database. Data Virtualization in Azure SQL Database enables working with CSV, Parquet, and Delta files stored on Azure Storage Account v2 (Azure Blob Storage) and Azure Data Lake Storage Gen2. Azure SQL Database will now support: CREATE EXTERNAL TABLE (CET), CREATE EXTERNAL TABLE AS SELECT (CETAS) as well as enhanced OPENROWSET capabilities to work with the new file formats.

The list of capabilities available in private preview are:

• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL) – SQL Server | Microsoft Learn with Shared Access Signature (SAS Token) support;


• CREATE EXTERNAL DATA SOURCE (Transact-SQL) – SQL Server | Microsoft Learn with support for Azure Storage Account v2 (ABS), and Azure Data Lake Gen2 (ADLS);


• CREATE EXTERNAL FILE FORMAT (Transact-SQL) – SQL Server | Microsoft Learn with support for CSV, and Parquet;


• CREATE EXTERNAL TABLE (Transact-SQL) – SQL Server | Microsoft Learn with support for CSV, Parquet, and Delta;


• CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL) – SQL Server | Microsoft Learn with support for CSV and Parquet;


• OPENROWSET (Transact-SQL) – SQL Server | Microsoft Learn with support for CSV, Parquet, and Delta.

Metadata functions:

Just like in SQL Server 2022 (Data Virtualization with PolyBase for SQL Server 2022 – Microsoft SQL Server Blog) and Azure SQL Managed Instance (Data virtualization now generally available in Azure SQL Managed Instance – Microsoft Community Hub), Data Virtualization in Azure SQL Database also supports updated metadata functions, wildcard search mechanism, and procedures that enables the users to query across different folders and leverage partition pruning, commands like:

• Filename();


• Filepath();


• sp_describe_first_result_set.

Benefits:

Major benefits of Data Virtualization in Azure SQL Database are:

• No data movement: Access real-time data where it is.


• T-SQL language: Ability to leverage all the benefits of the T-SQL language, its commands, enhancements, and familiarity.


• One source for all your data: Users and applications can use Azure SQL Database as a data hub, accessing all the required data in a single environment.


• Security: Leverage SQL security capabilities to simplify permissions, credential management, and control


• Export: Easily export data as CSV or Parquet to any Azure Storage location, either to empower other applications or reduce cost.

Getting started:

For simplicity, we are going to use publicly available NYC Taxi dataset (NYC Taxi and Limousine yellow dataset – Azure Open Datasets | Microsoft Learn) that allows anonymous access. 

-- Create data source for NYC public dataset:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

-- Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 1000 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

-- Schema discovery:
EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

-- Query top 100 files and project file path and file name information for each row:
SELECT TOP 100 filerows.filepath(1) as [Year_Folder], 
filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], 
filerows.filepath() as [Full_Path]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

-- Create external file format for Parquet:
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH ( FORMAT_TYPE=PARQUET );

-- Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);

-- Query the external table:
SELECT TOP 1000 * FROM tbl_TaxiRides;

Private Preview Sign-up form:

Data Virtualization in Azure SQL Database is in active development, Private Preview users will help shape the future of the feature, with regular interactions with Data Virtualization product team. If you want to be part of the private preview a sign-up form is required and can be found here.

How Tenant Restrictions v2 Can be Used to Prevent Data Exfiltration

by Contributed | Sep 13, 2023 | Technology

This article is contributed. See the original author and article here.

In a previous blog, we introduced Continuous Access Evaluation (CAE) – a product that brings Zero Trust principles to session management. Today we would like to discuss securing cross-tenant access with a focus on preventing data exfiltration. 

It’s impossible to imagine a successful modern organization that doesn’t collaborate with partners across organizational boundaries. While cross-company collaboration empowers employees and enables partnerships, it also lowers barriers for both accidental and malicious data exfiltration. Microsoft Cross-Tenant Access Settings is designed to address security of cross-company exchange.  

Outbound and Inbound Cross-Tenant Access Settings offer fine grain security controls for cross-company collaboration using user’s home identity, while Tenant Restriction v2 (TRv2) can be used to prevent data exfiltration using foreign identity. 

Some of the hardest-to-prevent data leaks happen when users inside your organization use foreign identities to connect to external tenants. Let’s consider one such attack. A malicious insider creates a Microsoft Entra tenant. Then they authenticate to their malicious tenant from your organization’s device. Now the attacker can leak your files via email using the Exchange Online account of the malicious tenant. These types of attacks can be described as creating a “USB dongle in a cloud.” Regular security methods do not work against such attacks. Your tenant’s policies do not apply to external identities that attackers use. Blocking Microsoft Entra ID or Exchange Online URIs in the firewall would block your legitimate users along with the attacker. These types of attack need special defenses that TRv2 provides.  

TRv2 works by sending special signals to Entra ID, Microsoft Account and other Microsoft resources. These signals point to Cross-Tenant Access Settings’ TRv2 policy that you created. Microsoft resources evaluate the policy and block unsanctioned access.  We have two major flavors of TRv2.   

Auth Plane TRv2 can block logins with external identities based on policy. To configure it you need to deploy a network proxy in your organization and configure that proxy to set TRv2 signals on all traffic to Entra ID and Microsoft Account. In the above example of a malicious insider leaking data over external email, the attacker will not be able to login to their malicious tenant and therefore will not be able to send email. Auth Plane TRv2 is now generally available.  

Universal TRv2 as part of Microsoft Entra Global Secure Access goes one step further to protect against more sophisticated attacks where an attacker bypasses authentication by allowing anonymous access to the malicious tenant’s apps, such as anonymous meeting join in Teams. Or the attacker can import to your organizational device an access token lifted from a device in the malicious tenant. All these attack vectors bypass login to Entra ID. Since Universal TRv2 sends TRv2 signals on authentication plane (Entra ID and Microsoft Account) and data plane (Microsoft cloud applications), these attacks will be prevented. Universal TRv2 is currently in public preview.  

We have another flavor of TRv2 in public preview – TRv2 on Windows. It’s a partial solution that protects the authentication and data planes but only for some scenarios. It only works on managed Windows devices and does not protect .NET stack, Chrome, or Firefox. We have heard from customers that it is difficult to deploy and does not provide adequate security. The Windows solution was meant to provide temporary protection until Universal TRv2 is released and we’re planning to retire it after Universal TRv2 is generally available.   

Anna Barhudarian 

Principal Product Manager, Identity Division  

Learn more about Microsoft Entra: 

• Related Articles: Apply Zero Trust Principles to Authentication Session Management with Continuous Access Evaluation – Microsoft Community Hub  


• See recent Microsoft Entra blogs 


• Dive into Microsoft Entra technical documentation 


• Join the conversation on the Microsoft Entra discussion space and Twitter 


• Learn more about Microsoft Security