Cómo usar identidades administradas con Azure SQL Database

En esta guía, aprenderá a usar identidades administradas para conectar un servicio de aplicaciones .NET a Azure SQL Database mediante identidades administradas.

Configure su base de datos de Azure

Para permitir que las identidades administradas se conecten a Azure SQL Database, debe habilitar la autenticación de Azure Active Directory (AD) y crear los usuarios administrados en la base de datos.

Para habilitar la autenticación de Azure AD para su Azure SQL Server, asegúrese de que haya un administrador de Azure AD configurado para el servidor de la base de datos.

Luego, habilite la autenticación desde su identidad administrada creando un usuario contenido . Esto difiere de las instancias locales de SQL Server que requieren un inicio de sesión del servidor y un usuario de la base de datos. Primero, asegúrese de que se haya configurado una identidad asignada por el sistema para su servicio de aplicaciones. Una vez que se haya asignado una identidad a su servicio de aplicaciones, ejecute el siguiente SQL para crear un usuario contenido con algunos roles para leer y escribir en la base de datos.

create user [my-app-service] from external provider;
alter role db_datareader add member [my-app-service];
alter role db_datawriter add member [my-app-service];

create user [my-app-service/slots/staging] from external provider;
alter role db_datareader add member [my-app-service/slots/staging];
alter role db_datawriter add member [my-app-service/slots/staging];

Nota : my-app-servicees un marcador de posición. Deberá reemplazarlo con el nombre de su propio servicio de aplicaciones. Además, para las identidades asignadas por el sistema, los diferentes espacios de implementación tienen sus propias identidades individuales.

Actualice su conexión SQL para usar identidades administradas

Ahora que ha creado los usuarios de SQL y les ha asignado roles de lectura y escritura, debe cambiar su aplicación para usar identidades administradas para autenticación y autorización.

Para comenzar a usar una identidad administrada de Azure App Service, cree un nuevo proyecto e instale algunos paquetes.

mkdir PLSQLManagedIdentity
cd PLSQLManagedIdentity
dotnet new mvc
dotnet add package Microsoft.Azure.Services.AppAuthentication
dotnet add package Microsoft.Data.SqlClient

El paquete Microsoft.Azure.Services.AppAuthenticationes un SDK de Azure que simplifica la autenticación.

Cuando ejecute el código en su máquina de desarrollo, utilizará la CLI de Azure o el inicio de sesión de Visual Studio para autenticarse. Cuando ejecuta el código en Azure App Service, utilizará la identidad asignada por el sistema.

A continuación, agregue la clase SqlAppAuthenticationProvidera su proyecto.

/// <summary>
/// An implementation of SqlAuthenticationProvider that implements Active Directory Interactive SQL authentication.
/// </summary>
public class SqlAppAuthenticationProvider : SqlAuthenticationProvider
{
    private static readonly AzureServiceTokenProvider _tokenProvider = new AzureServiceTokenProvider();

    /// <summary>
    /// Acquires an access token for SQL using AzureServiceTokenProvider with the given SQL authentication parameters.
    /// </summary>
    /// <param name="parameters">The parameters needed in order to obtain a SQL access token</param>
    /// <returns></returns>
    public override async Task<SqlAuthenticationToken> AcquireTokenAsync(SqlAuthenticationParameters parameters)
    {
        var authResult = await _tokenProvider.GetAuthenticationResultAsync("https://database.windows.net/").ConfigureAwait(false);

        return new SqlAuthenticationToken(authResult.AccessToken, authResult.ExpiresOn);
    }

    /// <summary>
    /// Implements virtual method in SqlAuthenticationProvider. Only Active Directory Interactive Authentication is supported.
    /// </summary>
    /// <param name="authenticationMethod">The SQL authentication method to check whether supported</param>
    /// <returns></returns>
    public override bool IsSupported(SqlAuthenticationMethod authenticationMethod)
    {
        return authenticationMethod == SqlAuthenticationMethod.ActiveDirectoryInteractive;
    }
}

Luego, en su Program.cs, registre el nuevo SqlAuthenticationProviderusando lo siguiente como la primera línea del método principal.

SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryInteractive, new SqlAppAuthenticationProvider());

Los dos fragmentos de código juntos buscarán cadenas de conexión SQL que contengan Authentication=Active Directory Interactive. Cuando lo encuentren, usarán AzureServiceTokenProviderpara obtener un token de acceso para autenticarse con Azure SQL Database.

Actualice su cadena de conexión

Ya ha configurado todas las piezas para autorizar su aplicación en su base de datos de Azure. Su cadena de conexión se puede actualizar a lo siguiente:

Server=xxxxxxx.database.windows.net,1433;Database=yyyyyyyyy;UID=a;Authentication=Active Directory Interactive

Solo necesita especificar el server, authenticationy el databasenombre. UIDse establece en un valor arbitrario, ya que es necesario para que la cadena de conexión pase la validación. Sin embargo, no se usa para la identidad administrada asignada por el sistema y la autenticación de la CLI de Azure.

Nota: Si usa identidades asignadas por el usuario y no usa la región global de Azure, deberá modificar la SqlAppAuthenticationProviderclase. Consulte la implementación de Microsoft de SqlAppAuthenticationProvider , que asigna más memoria pero es más flexible como referencia.

Microsoft Azure tiene una lista creciente de servicios que admiten identidades administradas para recursos de Azure. El uso de identidades administradas puede mejorar la seguridad de su aplicación, ya que no necesita administrar los secretos usted mismo. Su aplicación ahora puede conectarse a Azure SQL Database sin necesidad de un nombre de usuario o contraseña.

What is GEEK

Buddha Community

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ruthie Bugala

1620435660

How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server

In this article, you learn how to set up Azure Data Sync services. In addition, you will also learn how to create and set up a data sync group between Azure SQL database and on-premises SQL Server.

In this article, you will see:

Overview of Azure SQL Data Sync feature
Discuss key components
Comparison between Azure SQL Data sync with the other Azure Data option
Setup Azure SQL Data Sync
More…

Azure Data Sync

Azure Data Sync —a synchronization service set up on an Azure SQL Database. This service synchronizes the data across multiple SQL databases. You can set up bi-directional data synchronization where data ingest and egest process happens between the SQL databases—It can be between Azure SQL database and on-premises and/or within the cloud Azure SQL database. At this moment, the only limitation is that it will not support Azure SQL Managed Instance.

#azure #sql azure #azure sql #azure data sync #azure sql #sql server

Sekandi Hillary

1624420500

Creating and Cataloging SQL pools in Azure SQL Server

This article will walk you through creating a new SQL pool within an existing Azure SQL Server as well as catalog the same using the Azure Purview service.

Introduction

Data is generated by transactional systems and typically stored in relational data repositories. This data is generally used by live applications and for operational reporting. As this data volume grows, this data is often required by other analytical repositories and data warehouses where it can be used for referential purposes and adding more context to other data from across the organization. Transactional systems (also known as Online Transaction Processing (OLTP) systems) usually need a relational database engine, while analytical systems (also known as Online Analytical Processing (OLAP) systems) usually need analytical data processing engines. On Azure cloud, it is usually known that for OLTP requirements, SQL Server or Azure SQL Database can be employed, and for analytical data processing needs, Azure Synapse and other similar services can be employed. SQL Pools in Azure Synapse host the data on an SQL Server environment that can process the data in a massively parallel processing model, and the address of this environment is generally the name of the Azure Synapse workspace environment. At times, when one has already an Azure SQL Server in production or in use, the need is to have these SQL Pools on an existing Azure SQL Server instance, so data in these SQL pools can be processed per the requirements on an OLAP system as well as the data can be co-located with data generated by OLTP systems. This can be done by creating SQL Pools within the Azure SQL Server instance itself. In this article, we will learn to create a new SQL Pool within an existing Azure SQL Server followed by cataloging the same using the Azure Purview service.

Pre-requisite

As we intend to create a new SQL Pool in an existing Azure SQL Server instance, we need to have an instance of Azure SQL in place. Navigate to Azure Portal, search for Azure SQL and create a new instance of it. We can create an instance with the most basic configuration for demonstration purposes. Once the instance is created, we can navigate to the dashboard page of the instance and it would look as shown below.

As we are going to catalog the data in the dedicated SQL Pool hosted on Azure SQL instance, we also need to create an instance of Azure Purview. We would be using the Azure Purview studio from the dashboard of this instance, tonregister this SQL Pool as the source and catalog the instance.

#azure #sql azure #azure sql server #sql #sql #azure

Ruth Nabimanya

1621850444

List of Available Database for Current User In SQL Server

Introduction

When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current logged user in SQL Server

Get the list of database

Conclusion

#sql server #available databases for current user #check database has access #list of available database #sql #sql query #sql server database #sql tips #sql tips and tricks #tips

Ruth Nabimanya

1620633584

System Databases in SQL Server

Introduction

In SSMS, we many of may noticed System Databases under the Database Folder. But how many of us knows its purpose?. In this article lets discuss about the System Databases in SQL Server.

System Database

Fig. 1 System Databases

There are five system databases, these databases are created while installing SQL Server.

Master
Model
MSDB
Tempdb
Resource

Master

This database contains all the System level Information in SQL Server. The Information in form of Meta data.
Because of this master database, we are able to access the SQL Server (On premise SQL Server)

Model

This database is used as a template for new databases.
Whenever a new database is created, initially a copy of model database is what created as new database.

MSDB

This database is where a service called SQL Server Agent stores its data.
SQL server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts.

TempDB

The Tempdb is where SQL Server stores temporary data such as work tables, sort space, row versioning information and etc.
User can create their own version of temporary tables and those are stored in Tempdb.
But this database is destroyed and recreated every time when we restart the instance of SQL Server.

Resource

The resource database is a hidden, read only database that holds the definitions of all system objects.
When we query system object in a database, they appear to reside in the sys schema of the local database, but in actually their definitions reside in the resource db.

#sql server #master system database #model system database #msdb system database #sql server system databases #ssms #system database #system databases in sql server #tempdb system database