What 2 types of authentication does Azure SQL support?
- SQL Server
- Microsoft Entra ID (previously Azure Active Directory)
What types of identities does Microsoft Entra ID/Azure AD support?
- Cloud-only identities
- Hybrid identities that support cloud authentication with SSO, using password hash or pass-through authentication
- Hybrid identities that support federated authentication
What is the decision tree for authentication?
- Cloud-only identities
- Federated authentication
- Pass-through identification
Why would you use cloud-only identities for authentication?
- Azure AD handles sign-in completely in the cloud
- Don’t want enforced AD security policies during sign in
- Don’t have a sign-in requirement not natively supported by Azure AD
Why would you use federated authentication for authentication?
- If you want to integrate with an existing federation provider, or
- Have a sign-in requirement not natively supported by Azure AD
Why would you use pass-through authentication?
- All other cases
- Do not have a sign-in requirement not natively supported by Azure AD
- No integration with an existing federation provider, OR want to enforce user-level AD security policies during sign in
What are other authentications?
- Apps running on an Azure VM - passwordless authentication
- Apps running on a non-Azure machine that is domain-joined: use managed identities
- Apps running on a non-Azure machine that is not domain-joined: use certificate
- Admin tools on a non-Azure machine that is not domain-joined: use Azure AD integrated authentication, or Auzre AD interactive authentication with multifactor authentication.
How do you enable multi-factor authentication with Microsoft Entra ID?
- Go to the Portal, Active Directory, and Authentication methods. These include
- FIDO2 (Hardware) Security Key
- Microsoft Authenticator (App)
- Text message
- Temporary Access Pass
How do you add a new user with Azure AD authentication?
- In the Portal, go to Microsoft Entra ID.
- Go to Users > New User
- Enter name, username (similar to email addresses, either @[DomainName].onmicrosoft.com, or custom)
- Groups (optional)
- Azure AD role (Optional)
- Job Info (Optional)
- Password will be auto generated
- Click create
- Users are deleted from the same place
How should you create a second admin account?
You should create a second admin account as an Azure AD account, with the db_owner database role
How do you create a login for Azure SQL Managed Instance?
CREATE LOGIN MyLogin
WITH PASSWORD = ‘mypassword’;
CREATE USER MyLogin FOR LOGIN MyLogin
What can logins do in Azure SQL MI?
- Do SQL Agent management and jobs execution
- Database backup and restore operations
- Auditing
- Trigger login triggers
- Setup Service Brokers and DB mail
- Users cannot be created using the Azure Portal
Can you create logins from Azure AD users, groups or apps?
Yes
CREATE LOGIN loginname
[FROM EXTERNAL PROVIDER]
{WITH <option_list> [,...]}</option_list>
What are the parameters/options for creating a login from Azure AD users, groups, or apps?
- login name - an existing Azure AD UserPrincipalName of the user, DisplayName group, or app
- OptionList
- Password, cannot be used with FROM EXTERNAL PROVDER
- SID
- DEFAULT_DATABASE = database
- DEFAULT_LANGUAGE = language
What does FROM EXTERNAL PROVIDER mean as part of the CREATE LOGIN statement?
Indicates Azure AD Authentication
What is the syntax for creating users?
CREATE USER user_name
FOR | FROM LOGIN login_name
| FROM EXTERNAL PROVIDER
[WITH <limited_options_list> [,...]]</limited_options_list>
What is the limited options list when creating a user?
DEFAULT SCHEMA = schema_name
| DEFAULT_LANGUAGE = {NONE|lcid|language name|language alias}
|ALLOW_ENCRYPTED_VALUE_MODIFICATION = [ON|OFF]]
