Azure data sync

Category: sql server setup


msloy on Tue, 10 Apr 2018 23:43:38

I tried to explore  Azure Data Sync 2.0 to sync a database from premise to Azure.

I installed the data sync agent on my on premise SQL server, I know it is not a good practice, but just for testing and exploring purpose.

I open the agent and in the first screen, I can only see submit agent key configuration is enabled, all others is grayed out.

Is that supposed so?

Then when I open submit agent key configuration box, I input my network user login which has permissions on our Azure databases and the key. I used my login to create the Azure sync group etc.

But I got an error message when click test connection, what could be the problem? Thanks



Martin.Cairney on Wed, 11 Apr 2018 00:04:22

That looks like you don't have the Azure SQL DB firewall configured to allow connections from your On-Premises SQL Server.

The easiest way to check this is to remote desktop to the SQL Server On-Premises and open SSMS. Then try to make a connection to the Azure SQL DB with the login and password that you have. It should prompt you to login to Azure and then add your On-Premises SQL Server's IP Address to the firewall rules.

msloy on Wed, 11 Apr 2018 17:17:59

Thank you.

But I checked the IP address in the range that can access all the databases on our Azure SQL databases.

I also tried on this machine using SSMS to connect to the site. and I am able to see the databases from the box.

Any other suggestions?

Thanks much

msloy on Wed, 11 Apr 2018 23:24:47

Also in the data syn agent screen, when I put user name I use my windows network login.

I can use the same login from SSMS to the Azure server and databases.

But I am using active directory integrated authentication.

But on the data sync agent screen, can I use windows login mydomain/myusername.

Or we have to use SQL server login?


Martin.Cairney on Wed, 11 Apr 2018 23:48:42

There are 2 connection parts with the Agent.

Part 1 - when you add the key and register the connection. This connects to your Sync Metadata Databasde database in Azure and has to use SQL Authentication.  The login should have been defined at the server level in Azure.

Part 2 - when you connect to your on-premises SQL Server. This can be either windows or SQL authentication.