When trying to connect to the Azure DW or Azure DB you can experience the following failure, Cannot connect to <servername>.database.windows.net. One or more errors occurred (mscorlib)
SQL Server Management studio allows you to review the detailed exception information and in doing so one is able to obtain the Message, Error Code , Help Link and additional data which could help in troubleshooting the problem.
Select the icon in the SSMS Connection error window
Which will provide the information as example below.
For the exception in our case I was able to obtain the following from the Program Location information where the exception occurred : ADALNativeWrapper.ADALGetAccessToken
It indicated that the login process failed while obtaining an Access Token from the Domain Controller and was not able to complete the Login Process with the Data Warehouse.
The Domain Controller and Authentication process is highly dependent on your AD implementation in this particular scenario a Hybrid Deployment is in place which required the token to be provided by a federated service.
The easiest method to identify where the communication failure is happening is to perform a Netsh trace, especially if you are not familiar with the network and domain topology.
From Admin CMD Prompt run the following which will capture and generate a report and not exceed 1GB in size to a path of your choice.
C:\ Netsh trace start capture=yes report=Yes maxsize=1000 tracefile=c:\<path>\<filename>.etl
Reviewing the output in Message Analyzer and grouping by destination address I could easily determine to which destination my request was failing.
As per the events , sso.<domain>.com which is the Single Sign on Service endpoint was not reachable and therefore prevented the token from being generated. Local firewall rules prevented access to the URL from internal network and once resolve Authentication completed successfully.
When troubleshooting authentication failures ensure that you have an understanding of the Domain Topology and where authentication is occurring which will allow you to confirm that all services are operational and responding as expected.
To obtain a better understanding of AD and Different connection Typologies review the following article.
https://docs.microsoft.com/en-us/azure/active-directory/hybrid/plan-connect-topologies
An alternate solution would be to use Fiddler and perform a fiddler trace which will share the IP details where the connection is failing. In most scenarios we are failing to communicate with the Federated Domain services which are responsible for performing the domain authentication.
