Home / Programming / Azure SQL Database Migration Fail: The system cannot find the file specified error

Azure SQL Database Migration Fail: The system cannot find the file specified error

As a preface, I must say that I’m quite impressed with Microsoft’s offering in the cloud space. White not quite as mature as AWS, their platform-as-a-service, integrated with visual studio, in tandem with the excellent Azure web management portal, is incredibly enticing if you’re developing on the Microsoft stack.

I lead with that preface to be clear that this isn’t a gripe… I just ran into an error that was ultimately trivial in the solution, but troublesome to debug. When you’re deploying to bare metal or even a virtual machine, you can typically just remote into the web server to view the configuration files or error logs. No so with a cloud service. It pays to read all the documentation and tutorials before hand to ensure you understand the platform.

In this case, I had a ASP.NET MVC application that had been working fine locally, and working fine on a Amazon AWS instance with both IIS and SQL Server installed. Conversion to an Azure cloud service was a snap – after installing the Azure tool kit conversion is as simple as a right-click on the project file, and choosing the convert to Azure service option.

I imported the SQL Express database to Azure using the open source migration toolkit, which was a breeze. At first when testing locally I couldn’t connect to the SQL Database in Azure, but the error message was very helpful directing me to open the firewall to my local machine. Once that was done, I could run the project locally without any problem.

However, after uploading the web project to the Azure cloud service, it could not find the database:

System.Data.ProviderIncompatibleException: An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct. ---> System.Data.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception: The system cannot find the file specified

I confirmed the SQL database firewall was configured to allow access to other services. I confirmed the connection strings were correct in the web.config file. Still no luck.

Finally, it was a *doh* moment. While the connection string was correct in the base web.config file, it was incorrect in the Web.Release.config file. In this file, it was configured to use LOCALHOST – which happened to be set up locally. Works fine on my machine… as we say.

Of course, if this little project had been deployed to a multi-machine architecture before, we would have found the error then. Initially, I wanted to blame Azure for the error, but the error would have been the same even if we had deployed to a VPC in azure with separate web servers and database instances.