Over the past month or so, I have attended a number of sessions on Azure Data Factories. Both presenters were amazing and I learned a ton. However, as I usually do, I took the information and went off on my own to play with this new tool I hear a lot of people talking about. During both sessions I was able to connect to my Azure SQL Database with SQL Server Management Studio, but not with the connection in the Azure Data Factory. While I really didn’t get much of a chance to ask the presenter about my problem, I was finally able to. He was able to give me the answer I was looking for.
Before getting to the solution, let’s go over what the problem actually was. I have an Azure SQL Database that I have connected to many times via SSMS. However, during both sessions I attempted to create a connection in ADF and the test of the connection failed every time.
So what did I check? Just to confirm, I again connected via SSMS as I had done many times in the past. Again, no issues. Now I was convinced that the issue was not the database or the username\password. The next thing I thought of was the firewall rules. I asked myself the following question: Could there be an issue with the firewall and the IP addresses? Again, all appeared to be working just fine. Then I decided to do one more check using a third technology, I connected to the database via the Azure SQL Editor, which requires a username and password to connect. Again, all appeared to be fine.
At this point, I knew more about what was not causing the problem than what was causing the problem. Finally I had a moment with one of the presenters and he was able to give me the cause and the solution. As is usually the case, it was a single setting that was causing this and a very simple fix. That setting was the an Azure SQLDB setting, specifically the “Allow Access to Azure Services” setting.
To get to the setting, navigate to the settings of the Azure SQL DB and click on Overview, the orange box below. Then you will see the “Set server Firewall” option, click that.
Once you do, you will see something like the image below. Simply move the On\Off slider to On for the “Allow access to Azure service” setting. Then click the Save button.
Once you do that, then you should be able to connect to your Azure SQL Database using the Azure Data Factory connection. This made perfect sense, SSMS is not an Azure service, while Azure Data Factory is. Also keep in mind there are many reason that could create a similar situation, this was the cause of the issue I was seeing. So make be prepared to continue to troubleshoot this issue if this does not work.
Thanks for visiting my blog!!!