Microsoft SQL Data Sync 2.0 using AAD authentication

Question

Microsoft SQL Data Sync 2.0 using AAD authentication

MTBmojave80 40

I would like to configure Microsoft SQL Data Sync 2.0 (latest version) to authenticate using Azure Active Directory.

I have successfully created a user and given them ownership of the SQL database, but the account still cannot connect. The Data Sync DOES connect when using the SQL Server Management or the Azure Data Studio, so this rules out any firewall issues.

The error message is:

     Connection failed = login failed for user
     Please check database credentials
     Other reasons for failure:
     Firewall settings of Local Machine block the connection to Sync Metadata Database.
     Firewall settings of Sync Metadata Database block the connection.

The user can connect to the SQL database if I provide a local account to SQL but not AAD. So, it seems they have proper rights to access the SQL database and I have checked the properties for both local SQL and AAD accounts and settings are identical.

johnwilliam67 0 Reputation points

2023-05-22T20:12:09.8833333+00:00
If you are experiencing login failures when attempting to configure Microsoft SQL Data Sync 2.0 to authenticate using Azure Active Directory (AAD), there are a few things you can check to troubleshoot the issue:

Verify Azure Active Directory configuration: Ensure your Azure Active Directory configuration is set up correctly. It includes creating the user in Azure AD and assigning the appropriate permissions to access the SQL database. Make sure the user has the necessary permissions, such as being a member of the appropriate AAD group or having specific database roles assigned.

Check firewall settings: Confirm that the firewall settings are properly configured on your local machine and the Sync Metadata Database. Check if the IP addresses or ranges used by your machine or the Sync Metadata Database are blocked by any firewall rules. Adjust the firewall settings to allow the necessary connections.

Verify connection string: Double-check the connection string used by Microsoft SQL Data Sync. Ensure that the connection string includes the correct authentication method for Azure Active Directory. It should specify the AAD user or the AAD user's token along with the appropriate properties.

Confirm access with other tools: As you mentioned, the user can connect to the SQL database using SQL Server Management or Azure Data Studio. It indicates that the user has the necessary permissions to access the SQL database when using those tools. Ensure that the connection string and authentication method used by Microsoft SQL Data Sync are consistent with the successful connections made by other tools.

By verifying these aspects, you should be able to identify any potential misconfigurations or issues causing the login failures for the user authenticating with Azure Active Directory in Microsoft SQL Data Sync.

Accepted answer

1 additional answer

Your answer

johnwilliam67 0 Reputation points

2023-05-22T20:12:09.8833333+00:00

If you are experiencing login failures when attempting to configure Microsoft SQL Data Sync 2.0 to authenticate using Azure Active Directory (AAD), there are a few things you can check to troubleshoot the issue:

Verify Azure Active Directory configuration: Ensure your Azure Active Directory configuration is set up correctly. It includes creating the user in Azure AD and assigning the appropriate permissions to access the SQL database. Make sure the user has the necessary permissions, such as being a member of the appropriate AAD group or having specific database roles assigned.

Check firewall settings: Confirm that the firewall settings are properly configured on your local machine and the Sync Metadata Database. Check if the IP addresses or ranges used by your machine or the Sync Metadata Database are blocked by any firewall rules. Adjust the firewall settings to allow the necessary connections.

Verify connection string: Double-check the connection string used by Microsoft SQL Data Sync. Ensure that the connection string includes the correct authentication method for Azure Active Directory. It should specify the AAD user or the AAD user's token along with the appropriate properties.

Confirm access with other tools: As you mentioned, the user can connect to the SQL database using SQL Server Management or Azure Data Studio. It indicates that the user has the necessary permissions to access the SQL database when using those tools. Ensure that the connection string and authentication method used by Microsoft SQL Data Sync are consistent with the successful connections made by other tools.

By verifying these aspects, you should be able to identify any potential misconfigurations or issues causing the login failures for the user authenticating with Azure Active Directory in Microsoft SQL Data Sync.

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

Azure SQL Data Sync does not support Azure Active Directory authentication. It only supports SQL authentication to connect to Azure SQL.

When you install the SQL Data Sync agent you can connect to the local SQL Server instance (on-premise) with a local Windows user or a SQL login.

To demonstrate that SQL Data Sync does not support AAD authentication, you can read here that Azure SQL Databases that were created to support only Azure ADD are not supported by Azure SQL Data Sync.

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2023-05-22T20:22:57.9933333+00:00

MTBmojave80 Thank you for reaching out. Like @Alberto Morillo pointed out with doc above for reference, when Azure AD-only authentication is enabled for SQL Database, Azure SQL Data Sync will not be supported.

see below image.

Regards,

Oury

Answer 2

MTBmojave80 40

Per Alberto Morillo, the SQL Data Sync is not a supported feature for Azure AD SQL database authentication. It is a imitated functionality only to support local SQL accounts.

Share via

Microsoft SQL Data Sync 2.0 using AAD authentication

1 additional answer

Your answer