Skip to content

Commit dba7c8b

Browse files
JamesJBarnettJamesJBarnett
authored
Merge pull request #27744 from nofield/nofield/update-aad-auth-methods
Update AAD auth methods
2 parents e55b679 + 4c58f67 commit dba7c8b

8 files changed

Lines changed: 122 additions & 69 deletions

‎azure-sql/database/authentication-aad-service-principal.md‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@ Azure Active Directory (Azure AD) supports user creation in Azure SQL Database (
1919

2020
## Service principal (Azure AD applications) support
2121

22-
This article applies to applications that are integrated with Azure AD, and are part of Azure AD registration. These applications often need authentication and authorization access to Azure SQL to perform various tasks. This feature allows service principals to create Azure AD users in SQL Database. There was a limitation preventing Azure AD object creation on behalf of Azure AD applications that was removed.
22+
This article applies to applications that are integrated with Azure AD, and are part of Azure AD registration. These applications often need authentication and authorization access to Azure SQL to perform various tasks. This feature allows service principals to create Azure AD users in SQL Database.
2323

2424
When an Azure AD application is registered using the Azure portal or a PowerShell command, two objects are created in the Azure AD tenant:
2525

@@ -34,7 +34,7 @@ SQL Database and SQL Managed Instance support the following Azure AD objects:
3434
- Azure AD groups (managed and federated)
3535
- Azure AD applications
3636

37-
The T-SQL command `CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDER` on behalf of an Azure AD application is now supported for SQL Database.
37+
Azure AD users, groups, and applications can all be created on a database using the T-SQL command `CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDER`.
3838

3939
## Functionality of Azure AD user creation using service principals
4040

@@ -44,7 +44,7 @@ Supporting this functionality is useful in Azure AD application automation proce
4444

4545
To enable an Azure AD object creation in SQL Database on behalf of an Azure AD application, the following settings are required:
4646

47-
1. Assign the server identity. The assigned server identity represents the Managed Service Identity (MSI). The server identity can be system-assigned or user-assigned managed identity. For more information, see [User-assigned managed identity in Azure AD for Azure SQL](authentication-azure-ad-user-assigned-managed-identity.md).
47+
1. Assign the server identity. The assigned server identity represents the Managed Service Identity (MSI). The server identity can be a system-assigned or user-assigned managed identity. For more information, see [User-assigned managed identity in Azure AD for Azure SQL](authentication-azure-ad-user-assigned-managed-identity.md).
4848
- For a new Azure SQL logical server, execute the following PowerShell command:
4949

5050
```powershell
@@ -88,7 +88,7 @@ To enable an Azure AD object creation in SQL Database on behalf of an Azure AD a
8888
Directory Reader permission to your identity" (https://aka.ms/sqlaadsetup)'"`
8989
- For the above error, follow the steps to [Assign an identity to the Azure SQL logical server](authentication-aad-service-principal-tutorial.md#assign-an-identity-to-the-azure-sql-logical-server) and [Assign Directory Readers permission to the SQL logical server identity](authentication-aad-service-principal-tutorial.md#assign-directory-readers-permission-to-the-sql-logical-server-identity).
9090
- Setting the service principal (Azure AD application) as an Azure AD admin for SQL Database is supported using the Azure portal, [PowerShell](authentication-aad-configure.md?tabs=azure-powershell#powershell-for-sql-database-and-azure-synapse), [REST API](/rest/api/sql/2020-08-01-preview/servers), and [CLI](authentication-aad-configure.md?tabs=azure-cli#powershell-for-sql-database-and-azure-synapse) commands.
91-
- Using an Azure AD application with service principal from another Azure AD tenant will fail when accessing SQL Database or SQL Managed Instance created in a different tenant. A service principal assigned to this application must be from the same tenant as the SQL logical server or Managed Instance.
91+
- Using an Azure AD application with service principal from another Azure AD tenant will fail when accessing SQL Database or SQL Managed Instance created in a different tenant. A service principal assigned to this application must be from the same tenant as the SQL logical server or SQL Managed Instance.
9292
- [Az.Sql 2.9.0](https://www.powershellgallery.com/packages/Az.Sql/2.9.0) module or higher is needed when using PowerShell to set up an individual Azure AD application as Azure AD admin for Azure SQL. Ensure you are upgraded to the latest module.
9393
9494
## Next steps

‎docs/relational-databases/security/authentication-access/azure-ad-authentication-sql-server-overview.md‎

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,9 @@ You can now connect to SQL Server using the following authentication methods usi
2424
- Azure Active Directory Password
2525
- Azure Active Directory Integrated
2626
- Azure Active Directory Universal with Multi-Factor Authentication
27+
- Azure Active Directory Service Principal
28+
- Azure Active Directory Managed Identity
29+
- Azure Active Directory Default
2730
- Azure Active Directory access token
2831

2932
The current authentication modes, such as [SQL authentication and Windows authentication](../choose-an-authentication-mode.md) remain unchanged.
@@ -48,12 +51,27 @@ Allows specifying the username and password to the client and driver, but this i
4851

4952
### Azure Active Directory Integrated
5053

51-
When the Windows domain is synchronized with Azure AD, and a user is logged into the Windows domain, the user's Windows credentials are used for Azure AD authentication.
54+
When the Windows domain is synchronized with Azure AD and a user is logged into the Windows domain, the user's Windows credentials are used for Azure AD authentication.
5255

5356
### Azure Active Directory Universal with Multi-Factor Authentication
5457

5558
This is the standard interactive method with multi-factor authentication option for Azure AD accounts. This will work in most scenarios.
5659

60+
### Azure Active Directory Service Principal
61+
62+
A service principal is an identity that can be created for use with automated tools, jobs and applications. With the Service Principal authentication method, you can connect to your SQL instance using the client ID and secret of a service principal identity.
63+
64+
### Azure Active Directory Managed Identity
65+
66+
Managed identities are special forms of service principals. There are two types of managed identities: system-assigned and user-assigned. System-assigned managed identities are enabled directly on an Azure resource, whereas user-assigned managed identities are a standalone resource that can be assigned to one or more Azure resources.
67+
68+
> [!Note]
69+
> In order to use a managed identity to connect to a SQL resource through GUI clients such as SSMS and ADS, the machine running the client application must have an Azure AD client running with the identity's certificate stored in it. This is most commonly achieved through an Azure VM, as the identity can be easily assigned to the machine through the VM's portal blade.
70+
71+
### Azure Active Directory Default
72+
73+
The Default authentication option with Azure Active Directory enables authentication that's performed through password-less and non-interactive mechanisms including Managed Identities, Visual Studio, Visual Studio Code, Azure CLI, and more.
74+
5775
### Azure Active Directory access token
5876

5977
Some non-GUI clients such as [Invoke-sqlcmd](/powershell/module/sqlserver/invoke-sqlcmd) allow providing an access token. The scope or audience of the access token must be `https://database.windows.net/`.

‎docs/relational-databases/security/authentication-access/azure-ad-authentication-sql-server-setup-tutorial.md‎

Lines changed: 25 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -40,12 +40,12 @@ In this tutorial, you learn how to:
4040
> [!NOTE]
4141
> Extended functionality has been implemented in Azure to allow the automatic creation of the Azure Key Vault certificate and Azure AD application during setting up an Azure AD admin for the SQL Server. For more information, see [Tutorial: Using automation to set up the Azure Active Directory admin for SQL Server](azure-ad-authentication-sql-server-automation-setup-tutorial.md).
4242
43-
- To perform Azure AD authentication, SQL Server needs to be able to query Azure AD and requires an Azure AD app registration, which it can authenticate as. The app registration also needs a handful of permissions for the queries SQL Server will perform.
43+
- An Azure AD app registration for SQL Server. Registering a SQL Server instance as an Azure AD app allows the instance to query Azure AD, and allows the Azure AD app to authenticate on behalf of the SQL Server instance. The app registration also requires a few permissions, which are used by SQL Server for certain queries.
4444

45-
- SQL Server uses a certificate for this authentication, and it is stored in Azure Key Vault (AKV). The Azure Arc agent downloads the certificate to the SQL Server instance host.
45+
- SQL Server uses a certificate for this authentication, which is stored in Azure Key Vault (AKV). The Azure Arc agent downloads the certificate to the SQL Server instance host.
4646

4747
> [!WARNING]
48-
> Connections authenticated by Azure AD are always encrypted. If SQL Server is using a self-signed certificate, you must add `trust server cert = true` in the connection string. SQL Server and Windows authenticated connections don't require encryption, but it is recommended.
48+
> Connections authenticated by Azure AD are always encrypted. If SQL Server is using a self-signed certificate, you must add `trust server cert = true` in the connection string. SQL Server and Windows authenticated connections don't require encryption, but it is strongly recommended.
4949
5050
## Create and register an Azure AD application
5151

@@ -86,6 +86,7 @@ Select the newly created application, and on the left side menu, select **API Pe
8686
1. Go to the [Azure portal](https://portal.azure.com), select **Key vaults**, and select the key vault you wish to use or create a new one. Select **Certificates** > **Generate/Import**
8787

8888
1. For the **Method of certificate creation**, use **Generate**.
89+
8990
1. Add a certificate name and subject.
9091

9192
1. The recommended validity period is at most 12 months. The rest of the values can be left as default.
@@ -136,7 +137,7 @@ Select the newly created application, and on the left side menu, select **API Pe
136137

137138
1. Select **Azure Active Directory** on the left-hand column.
138139

139-
1. Select **Set Admin**, and choose an account that will be added as an admin login to SQL Server.
140+
1. Select **Set Admin**, and choose an account to set as an admin login to SQL Server.
140141

141142
1. Select **Customer-managed cert** and **Select a certificate**.
142143

@@ -146,7 +147,7 @@ Select the newly created application, and on the left side menu, select **API Pe
146147

147148
1. Select **Change app registration**, and select the app registration you created earlier.
148149

149-
1. Select **Save**. This will send a request to the Arc server agent, which will configure Azure AD authentication for that SQL Server instance.
150+
1. Select **Save**. This sends a request to the Arc server agent, which configures Azure AD authentication for that SQL Server instance.
150151

151152
:::image type="content" source="media/configure-azure-ad-for-sql-server-instance.png" alt-text="Screenshot of setting Azure Active Directory authentication in the Azure portal.":::
152153

@@ -175,18 +176,18 @@ After the Azure Arc agent on the SQL Server host has completed its operation, th
175176
176177
### Create login syntax
177178

178-
The same syntax that is used for creating Azure AD logins and users on Azure SQL Database and Azure SQL Managed Instance can now be used on SQL Server.
179+
The same syntax for creating Azure AD logins and users on Azure SQL Database and Azure SQL Managed Instance can now be used on SQL Server.
180+
179181
> [!NOTE]
180-
>
181-
On SQL Server, any account that has the `ALTER ANY LOGIN` or `ALTER ANY USER` permission can create Azure AD logins respectively users. The account doesn't need to be an Azure AD login.
182+
> On SQL Server, any account that has the `ALTER ANY LOGIN` or `ALTER ANY USER` permission can create Azure AD logins or users, respectively. The account doesn't need to be an Azure AD login.
182183
183-
To create a login for an Azure AD account, execute the T-SQL command below in the `master` database:
184+
To create a login for an Azure AD account, execute the following T-SQL command in the `master` database:
184185

185186
```sql
186-
CREATE LOGIN [principal name] FROM EXTERNAL PROVIDER;
187+
CREATE LOGIN [principal_name] FROM EXTERNAL PROVIDER;
187188
```
188189

189-
For users, the principal name should be in the format `user@contoso.com`. For all other account types, the tenant name isn't necessary and either the Azure AD group name or application name must be used.
190+
For users, the principal name must be in the format `user@tenant.com`. In Azure AD, this is the user principal name. For all other account types, like Azure AD groups or applications, the principal name is the name of the Azure AD object.
190191

191192
Here's some examples:
192193

@@ -202,20 +203,19 @@ CREATE LOGIN [my_app_name] FROM EXTERNAL PROVIDER;
202203
GO
203204
```
204205

205-
To list the Azure AD logins in `master` database, execute the T-SQL command:
206+
To list the Azure AD logins in the `master` database, execute the T-SQL command:
206207

207208
```sql
208209
SELECT * FROM sys.server_principals
209210
WHERE type IN ('E', 'X');
210211
```
211212

212-
To grant an Azure AD user membership to the `sysadmin` role (for example `admin@contoso.com`), execute the following commands in `master` database:
213+
To grant an Azure AD user membership to the `sysadmin` role (for example `admin@contoso.com`), execute the following commands in `master`:
213214

214215
```sql
215216
CREATE LOGIN [admin@contoso.com] FROM EXTERNAL PROVIDER;
216217
GO
217-
ALTER ROLE sysadmin
218-
ADD MEMBER [admin@contoso.com];
218+
ALTER SERVER ROLE sysadmin ADD MEMBER [admin@contoso.com];
219219
GO
220220
```
221221

@@ -225,7 +225,7 @@ The `sp_addsrvrolemember` stored procedure must be executed as a member of the S
225225

226226
You can create an Azure AD user either as a user with an Azure AD login, or as an Azure AD contained user.
227227

228-
To create an Azure AD user from an Azure AD login in a SQL Server database where the user should reside in, use the following syntax:
228+
To create an Azure AD user from an Azure AD login in a SQL Server database, use the following syntax:
229229

230230
```sql
231231
CREATE USER [principal_name] FROM LOGIN [principal_name];
@@ -275,15 +275,13 @@ To list the users created in the database, execute the following T-SQL command:
275275
SELECT * FROM sys.database_principals;
276276
```
277277

278-
The newly created user in a database has only the **Connect** permission, by default. All other SQL Server permissions for this user must be explicitly granted by the grantors.
278+
A new database user is given the **Connect** permission by default. All other SQL Server permissions must be explicitly granted by authorized grantors.
279279

280280
### Azure AD guest accounts
281281

282-
The `CREATE LOGIN` and `CREATE USER` syntax also supports guest users. For example, if `testuser@outlook.com` was invited to the `contoso.com` tenant, it could be added as a login to SQL Server with the syntax below. In the example, `outlook.com` is provided even though SQL Server will use the account registered in the `contoso.com` tenant.
283-
284-
The following section has examples of creating guest users.
282+
The `CREATE LOGIN` and `CREATE USER` syntax also supports guest users. For example, if `testuser@outlook.com` is invited to the `contoso.com` tenant, it can be added as a login to SQL Server with the following syntax. In the examples, `outlook.com` is provided even though SQL Server uses the account registered in the `contoso.com` tenant.
285283

286-
#### Create a guest user with login that exists
284+
#### Create a guest user from an existing login
287285

288286
```sql
289287
CREATE USER [testuser@outlook.com] FROM LOGIN [testuser@outlook.com];
@@ -297,24 +295,27 @@ CREATE USER [testuser@outlook.com] FROM EXTERNAL PROVIDER;
297295

298296
## Connect with a supported authentication method
299297

300-
SQL Server supports four authentication methods for Azure AD authentication:
298+
SQL Server supports several methods of Azure AD authentication:
301299

302300
- Azure Active Directory Password
303301
- Azure Active Directory Integrated
304302
- Azure Active Directory Universal with Multi-Factor Authentication
303+
- Azure Active Directory Service Principal
304+
- Azure Active Directory Managed Identity
305+
- Azure Active Directory Default
305306
- Azure Active Directory access token
306307

307308
Use one of these methods to connect to the SQL Server instance. For more information, see [Azure Active Directory authentication for SQL Server](azure-ad-authentication-sql-server-overview.md).
308309

309310
## Authentication example using SSMS
310311

311-
Below is the snapshot of the SQL Server Management Studio (SSMS) connection page using the authentication method, **Azure Active Directory - Universal with MFA**.
312+
Below is the snapshot of the SQL Server Management Studio (SSMS) connection page using the authentication method **Azure Active Directory - Universal with MFA**.
312313

313314
:::image type="content" source="media/sql-server-management-studio-connection.png" alt-text="Screenshot SSMS showing the Connect to Server window.":::
314315

315316
During the authentication process, a database where the user was created must be explicitly indicated in SSMS. Expand **Options > Connection Properties > Connect to database: `database_name`**.
316317

317-
For more information on **Azure Active Directory - Universal with MFA** authentication, see [Using Azure Active Directory Multi-Factor Authentication](/azure/azure-sql/database/authentication-mfa-ssms-overview).
318+
For more information, see [Using Azure Active Directory Multi-Factor Authentication](/azure/azure-sql/database/authentication-mfa-ssms-overview).
318319

319320
SQL Server tools that support Azure AD authentication for Azure SQL are also supported for [!INCLUDE [sssql22-md](../../../includes/sssql22-md.md)].
320321

‎docs/ssms/f1-help/connect-to-server-connection-properties-page-database-engine.md‎

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -31,10 +31,10 @@ Select a protocol from the list. The available client protocols are configured u
3131
**Network packet size**
3232
Enter the size of the network packets to be sent. The default is 4096 bytes.
3333

34-
**Connection timeout**
34+
**Connection time-out**
3535
Enter the number of seconds to wait for a connection to be established before timing out. The default value is 15 seconds.
3636

37-
**Execution timeout**
37+
**Execution time-out**
3838
Enter the amount of time in seconds to wait before execution of a task is completed on the server. The default value is zero seconds, which indicates there is no time-out.
3939

4040
**Encrypt connection**
@@ -48,9 +48,6 @@ Select to specify the background color for the status bar in a [!INCLUDE[ssDE](.
4848
- When you specify a color for a server entry in the **Registered Servers** pane, that color is used when you open a Query Editor window. To open a Query Editor window, either right-click the server entry and select **New Query**; or, when the **Registered Server** pane is active and focused on this server, click **New Query** on the toolbar.
4949

5050
- On the **File** menu, when you click **New** and then **Database Engine Query**, the color you that you specify in the **Connect to Server** dialog box applies to that Query Editor window.
51-
52-
**AD domain name or tenant ID**
53-
When connecting with **Active Directory - Universal with MFA** authentication, specify the authenticating domain. This option is only available when using SSMS version 17.2 or later.
5451

5552
**Reset All**
5653
Replace all manually entered connection property values with their defaults.

0 commit comments

Comments
 (0)