Azure Data Migration Error 22300 when attempting to connect to Azure Sql Db

Question

Trying to use Azure Sql Migration to migrate a localdb to an Azure Sql Db. Credentials for both are solid. The user logged into the localdb ("sync_migration") has both db_owner database ownership and has been granted Alter permissions. The Integration Runtime has been configured correctly as you can see from the smaller window I placed in the screenshot. Finally, I have created an inbound rule allowing data to pass from port 1433 (off-screen), so neither the linked service configuration (Self-hosted Integration Runtime) nor local firewall rules should be the problem despite what Error 22300 says.

I referred to the documentation: https://learn.microsoft.com/en-us/azure/dms/known-issues-troubleshooting-dms-source-connectivity as well as the "Known issues" github, and neither address this.

Answer 1

After trying all 5 or so of the typical reasons for failure (they're all over the internet if you do enough searching), I've found that the culprit is the server itself. I was using localdb when I should have been using anything else. Apparently, localdb is not designed for migrating data to non-prem dbs, but what is frustrating is nothing about this was found online or in speaking with MS representatives. Further, in SSMS, the option to turn remote connections on and off is available for localdb even when it actually doesn't work. This needs to be made more clear to folks who aren't senior DBAs.

Long story short, I got the data migration working once I moved my data to a localhost server using Sql Server 2022 and making the correct configurations that you can find how to do online.

Answer 2

Hello,

Make sure that the SQL Server instance hosting the local database is configured to allow remote connections. You can check this in SQL Server Management Studio (SSMS) by right-clicking on the SQL Server instance, selecting "Properties," then going to the "Connections" section and ensuring "Allow remote connections to this server" is checked.

Also plaese confirm that the SQL Server instance is listening on port 1433 or another specified port. You can find this information in the SQL Server Configuration Manager under "SQL Server Network Configuration" > "Protocols for [instance name]" > "TCP/IP" > "IP Addresses" tab. Make sure the "TCP Dynamic Ports" field is empty and the "TCP Port" field is set to 1433 or another specified port.

If this answer helped please mark it as accepted to help others find this topic.

Regards