Login Problems with my Azure Active Directory Account to Azure SQL Data Warehouse

  • Article

From time to time, we on the Azure SQL Data Warehouse (ADW) support team get cases where a customer is having login problems related to their Azure Active Directory (AAD) account. Some of the problems could be from a bad password, a paused instance at the time of logon, etc., . . . and those are not what I really want to talk about it this article. AAD is a federated login which can be much more complicated depending on the configuration in play.

Most of the people I talk to don't care about AAD because they just want access to their data; so for those people, I will get to the answer as quickly as possible.

To The Point . . .

First, make sure your client firewall is allowing your client(s) to connect to all of the IP ranges in the Azure datacenters associated with your instance.

- All Azure datacenter IP ranges are updated WEEKLY and published on this link - https://www.microsoft.com/en-us/download/details.aspx?id=4165

- To determine what datacenter ranges you need to add, see this link - /en-us/azure/best-practices-availability-paired-regions

Let's say your ADW instance is in West US. This means that you need to add West US and East US datacenter IP ranges to your firewall allow list. Shared services like AAD (Azure Active Directory) will sometimes send authentication request to the paired data center.

If you want to know more about Azure Active Directory look here.

How it works . . .

Now to answer the , "Why, when logging on to ADW, would I need to connect to an entirely different region to complete the process?" question that I know you were asking yourself . . . Take a look at this diagram; it explains most of it which I will detail below. Assume you are using something like SSMS to login to ADW and you just clicked Connect:

  1. The first thing the client will do is connect directly to the ADW. Since the connection does not have an authorization token, it is redirected to AAD
  2. Once the user is authenticated to AAD (SSMS is handling this process), a token is returned to the client
  3. Now that the client has a valid token they (SSMS) will return to the ADW login and provide the token
  4. Access is granted

The problem comes in when the client tries to get the token (Step 2) but is unable to connect to AAD. The error message from an application like SSMS may be that the connection was made but then disconnected. This is because a channel with ADW is in place when the token process is started, but the connection times out having never getting received a token so the login attempt fails.

Thanks to Scott for assisting me with this post.

Allan