This article describes,
Connect azure sql database using azure active directory user or cannot connect to XXXXXX.database.windows.net error 18456
SQL Server Authentication Method :
There are 2 types of authentication mode in SQL server Virtual Machines.
Windows Authentication
In Windows Authentication mode, when you’re using SQL Server from the same computer as where it’s installed, SQL Server doesn’t ask for username and password as shown below.
SQL Server Authentication
When you use SQL Server Authentication, you must require a username and password. In the business world, most applications use this authentication to provide their client or vendor access to their databases.
Azure SQL Database and Azure SQL Managed Instance support Azure AD authentication methods.
Azure Active Directory (Azure AD) authentication is a mechanism for connecting to Azure SQL Database, Azure SQL Managed Instance, by using identities in Azure AD.
Azure Active Directory authentication supports the following methods of connecting to a database using Azure AD identities:
- Azure Active Directory Password
- Azure Active Directory Integrated
- Azure Active Directory Universal with Multi-Factor Authentication
- Using Application token authentication
The following authentication methods are supported for Azure AD server principals (logins):
- Azure Active Directory Password
- Azure Active Directory Integrated
- Azure Active Directory Universal with Multi-Factor Authentication
How to resolved: Error 18456 sql server or Connect azure sql database using azure active directory user or cannot connect to XXXXXX.database.windows.net error while connect to Azure SQL Database
Step 1 : Please verify if user exists on Azure Active Directory. If not exists User need to be created first before granting access on Azure SQL Database.
Step 2 : Connect Azure SQL Database with AD admin user in SSMS. AD Admin is subscription admin user who has administrator access on all resources.
Step 3 : Create AD user on Azure SQL Database using below Query. To create AD user Azure SQL database should be connected using AZURE AD user who has Administrator access. Run below command to create AD SQL user
Query :
CREATE USER [test@omkara11589gmail.onmicrosoft.com] FROM EXTERNAL PROVIDER;
Step 4 : Connect Azure SQL Database using Azure Active Directory user.
Enter password of Azure AD user.
Update Azure AD user password if asked.
Select database name on connection property before connecting in SSMS.
Check out:
Create SQL Server Jobs using PowerShell | Automate SQL Server jobs Creation Using PowerShell