Hello @greg schlitt
Let’s troubleshoot this step by step.
- Authentication Configuration:
- Ensure that your app is registered in Azure Active Directory (AAD) and has the necessary permissions to access the Azure SQL Database. You’ve already successfully signed in external users using the
SignInButton_Click
method, which means your authentication setup is working. - Make sure you’ve registered your app with AAD and granted it the appropriate permissions. Specifically, you need to add the “Azure SQL Database” permission with “User impersonation” delegated permission.
- Verify that the user you’re trying to authenticate has been added to the Azure SQL Database. You mentioned creating the user via
CREATE USER [joe@xyz.com] FROM EXTERNAL PROVIDER
, but ensure that the user is correctly mapped to the database and has the necessary roles (e.g.,db_datareader
,db_datawriter
).
- Your connection string in the
ContactDB_Click
method should specify the correct authentication method. Since you’re using AAD authentication, set it to"Authentication=Active Directory Default;"
.- Double-check the connection string for any typos or missing parameters. It should include the server name, database name, and other relevant settings.
- Ensure that your app is registered in Azure Active Directory (AAD) and has the necessary permissions to access the Azure SQL Database. You’ve already successfully signed in external users using the
Token-Identified Principal Error:
- The error message “Login failed for token-identified principal” indicates that the user’s token is not being properly recognized during authentication.
- This issue might be related to the SID (Security Identifier) mismatch between the AAD user and the SQL Server user. Ensure that the SID of the AAD user matches the SID of the corresponding SQL user.
- To verify if this is the issue, run the following query in the database you’re trying to connect to:
Compare the retrieved SID with the AAD user’s SID.SELECT name, sid FROM sys.database_principals WHERE name = 'joe@xyz.com';
SSMS Connection:
- You mentioned that connecting via SSMS works after specifying the database name. This is because Azure SQL Databases are self-contained, and users don’t have permission to connect to the master database by default.
- Make sure your app specifies the correct database name in the connection string. If it doesn’t, SSMS won’t connect to the desired database.
Troubleshooting:
- If you encounter further issues, consider enabling diagnostic logging for Azure SQL Database. This can provide additional details about authentication failures.
- Review the Azure AD authentication configuration and ensure that the user’s UPN (user principal name) is used for authentication.
Remember to handle exceptions gracefully in your code to provide better error messages and troubleshoot effectively.
I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.