Azure Active Directory only authentication for Azure SQL
We’re announcing a new feature called Azure Active Directory only authentication for Azure SQL (hereafter referred to as “Azure AD-only auth”). This feature is in public preview and is supported for Azure SQL Database (SQL DB) and Azure SQL Managed Instance (MI). Following the SQL Server on-premises feature that allows the disabling of SQL authentication and enabling only Windows authentication, Azure SQL now allows only Azure AD authentication, and disables SQL authentication in the Azure SQL environment. Feature details When “Azure AD-only auth” is active (enabled), SQL authentication is disabled, including for SQL server admin, as well as SQL logins and users. The feature allows only Azure AD authentication for the Azure SQL server and MI. SQL authentication is disabled at the server level (including all databases) and prevents any authentication (connection to the Azure SQL server and MI) based on any SQL credentials. Although SQL authentication is disabled, the creation of new SQL logins and users are not blocked. However, pre-existing and newly created SQL accounts will not be allowed to connect to the server. In addition, enabling the Azure AD-only auth feature does not remove existing SQL login and user accounts, but it denies these accounts from connecting to Azure SQL server and any database created for this server. Tooling-support We support PowerShell, CLI commands, Rest APIs, ARM templates, as well as the Azure portal for SQL Database to enable or disable the Azure AD-only auth feature. The Azure portal for MI is currently not supported. For more on details on this feature and available interfaces, see AAD-only-authentication. Permissions required to enable/disable Azure AD-only auth To enable or disable the Azure AD-only auth feature special permissions are required available to the high privileged built-in roles such as subscription owner, contributor, or co-administrator. The required permissions can also be customized by creating custom roles. For more information on Azure built-in roles, see https://docs.microsoft.com/en-us/azure/role-based-access-control/built-in-roles. To allow Azure AD users with lower privileges to enable/disable the Azure AD-only auth feature, the existing built-in role SQL Security Manager was modified to allow these operations for SQL DB and MI. The two Azure SQL built-in roles, SQL Server Contributor (used for SQL DB) and SQL Managed Instance Contributor (used for MI), do not have the permission to enable or disable Azure AD-only auth. This role-separation helps in implementing separation of duties, where users who can create an Azure SQL server or create an Azure AD admin, such as SQL Server contributor or SQL Managed Instance Contributor, cannot enable nor disable security features such as Azure AD-only auth. Enabling/disabling the Azure AD-only auth feature using the Azure portal After assigning an Azure AD user a role discussed above, such as SQL Security Manager, the Azure AD-only auth feature can be enabled using the Azure portal by checking the feature box and saving its action (see below). The Azure AD-only auth feature using the Azure portal is currently supported only for SQL DB, and not for MI. Note that the Azure AD admin must be set for this server to check the feature box. Once the feature is enabled, any attempt to login to this server using SQL authentication fails with an error message indicating the cause of the failure (see below). Similarly, the feature box can be unchecked allowing both Azure AD and SQL authentication. In this case, repeating the SQL login using the SSMS example above will succeed. Limitations Azure AD-only auth is supported at the Azure SQL server level This means that when this mode is enabled, all databases that belong to this server can only be accessed using Azure AD authentication Enabling Azure AD-only auth does not remove existing SQL logins or SQL users based on these logins. They continue being stored in SQL metadata, but cannot be used for SQL authentication Even though the Azure AD-only auth is enabled, with proper SQL permissions for Azure AD users, SQL logins and SQL users can be created. However, the authentication process to connect to Azure SQL using SQL logins/users will fail Azure AD users with proper permissions can impersonate existing SQL users Impersonation continues working between SQL authentication users even though the Azure AD-only auth feature is enabled. This is consistent to the way impersonation works today, where even disabled users can be impersonated. New update As an extension to this feature we have also released a new functionality that is now part the public preview for the Aure AD-only auth allowing to provision an Azure SQL server with Azure AD-only enabled during a server creation. In addition a server admin and a server password can be set by the system (set password to random) during a server provisioning. For more information see Create server with Azure Active Directory only authentication enabled in Azure SQL - Azure SQL Database | Microsoft Docs.Announcing Azure SQL Database ledger
Bringing tamper-evidence capabilities to your database, the ledger feature for Azure SQL Database provides cryptographic that your data has not been maliciously altered. This article explains Azure SQL Database ledger and how it can help digitize trusting your data.What’s new in Azure SQL Managed Instance at Ignite 2020
Since its inception, Azure SQL Managed Instance (SQL MI) has been continuously improved based on customer feedback, aiming to meet critical requirements of organizations that migrate and modernize their applications in Azure. In that spirit we are excited to announce several important capabilities coming to preview or general availability during Ignite 2020, which will bring significant improvements across multiple product aspects: performance, security and compliance, management experience, programmability surface area and application compatibility.How to migrate data from regular tables to ledger tables
This article will guide you through the steps to convert your existing data into tamper proof data by using the ledger feature. To enable ledger for a specific table, you can migrate the data from an existing regular table to a ledger table, and then replace the original table with the ledger table. We have built the tools to help you do that. To convert a regular table into a ledger table, Microsoft recommends using the sys.sp_copy_data_in_batches stored procedure. It copies data from the source table to the target ledger table (updatable or append-only) after verifying that their schema is identical in terms of number of columns, column names and their data types. Indexes between the tables can be different but the target table can only be a heap or have a clustered index. To create a ledger table with an identical schema as the source table, you can script the existing table and add the ledger syntax to create an updatable ledger table or an append-only ledger table. The stored procedure will split the copy operation into batches of 10-100K rows per transaction. Now, why are these batches important? When you perform a database ledger verification, the process needs to order all operations within each transaction. By using smaller batches, the database ledger verification has smaller transactions that can be sorted in parallel. This helps improve the time of the database ledger verification tremendously. If you use a SELECT INTO or BULK INSERT statement to copy a few billion rows from a regular table to a ledger table, it will all be done in one single transaction. This means lots of data must be fully sorted, which is performed in a single thread. The sorting operation takes a long time to complete and has impact on the verification time. Secondly, the copy process of the stored procedure is also more efficient than SELECT INTO or BULK INSERT. The operation uses parallelism and the logging optimizations, used traditionally by index build operations. I did a simple test to demonstrate the benefits of the stored procedure (and the smaller batches). In the first test I will compare the performance of the data migration. The second test is the verification based on the 2 different migration methods. Setup and scenario I restored the Microsoft sample database WideWorldImportersDW on a local SQL Server 2022 instance and generated extra data for performance testing. The source table [Fact].[Sale] contained approximately 24 million records. Secondly, I created 2 append-only ledger heap tables with the exact schema as the source table: [Fact].[Sale_Ledger] will be used to load the data with the sys.sp_copy_data_in_batches stored procedure. [Fact].[Sale_Ledger_2] will be used to load the data in 1 transaction by using INSERT SELECT. Once the data is loaded, I ran the database verification on both tables separately to show the difference in verification time. Migrate the data First, I’m loading the data with the sp_copy_data_in_batches stored procedure. This took 3 min 42 sec to complete. EXECUTE sys.sp_copy_data_in_batches @source_table_name = N'[Fact].[Sale]' , _table_name = N'[Fact].[Sale_Ledger]' Secondly, I’m loading the data with a INSERT INTO... SELECT statement. This took 7 min 12 sec to complete. INSERT INTO [Fact].[Sale_Ledger_2]([City Key], [Customer Key], [Bill To Customer Key], [Stock Item Key], [Invoice Date Key], [Delivery Date Key], [Salesperson Key], [WWI Invoice ID], [Description], [Package], [Quantity], [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Profit], [Total Including Tax], [Total Dry Items], [Total Chiller Items], [Lineage Key]) SELECT [City Key], [Customer Key], [Bill To Customer Key], [Stock Item Key], [Invoice Date Key], [Delivery Date Key], [Salesperson Key], [WWI Invoice ID], [Description], [Package], [Quantity], [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Profit], [Total Including Tax], [Total Dry Items], [Total Chiller Items], [Lineage Key] FROM [Fact].[Sale] Ledger verification First verification of the [Fact].[Sale_Ledger] ledger table which was loaded in batches had a total execution time of 1 min 31 sec. DECLARE @digest_locations NVARCHAR(MAX) = (SELECT * FROM sys.database_ledger_digest_locations FOR JSON AUTO, INCLUDE_NULL_VALUES);SELECT @digest_locations as digest_locations; BEGIN TRY EXEC sys.sp_verify_database_ledger_from_digest_storage @digest_locations, @table_name = N'[Fact].[Sale_Ledger]'; SELECT 'Ledger verification succeeded.' AS Result; END TRY BEGIN CATCH THROW; END CATCH The second verification of the [Fact].[Sale_Ledger_2] ledger table, which was loaded in 1 transaction, had a total execution time of 2 min 51 sec. DECLARE @digest_locations NVARCHAR(MAX) = (SELECT * FROM sys.database_ledger_digest_locations FOR JSON AUTO, INCLUDE_NULL_VALUES);SELECT @digest_locations as digest_locations; BEGIN TRY EXEC sys.sp_verify_database_ledger_from_digest_storage @digest_locations, @table_name = N'[Fact].[Sale_Ledger_2]'; SELECT 'Ledger verification succeeded.' AS Result; END TRY BEGIN CATCH THROW; END CATCH Conclusion As you can see from the test results, the data migration and the verification on the table where I used the sp_copy_data_in_batches stored procedure is almost twice as fast as verifying a table that was loaded with a single transaction. You can still use other commands, services, or tools to copy the data from the source table to the target table, but the bottom line is: make sure you avoid large transactions because this will have a performance impact on the database ledger verification and the data migration.
