Cannot connect to Azure SQL database, even with whitelisted IP
I am currently unable to connect to my Azure SQL database from a separate remote standalone dedicated box in a private datacenter.
I have an Azure SQL database where I manage the list of IP addresses that can connect to this database. This has worked perfectly until now. I have recently set-up a new dedicated box in a private datacenter that needs to query the Azure SQL database at regular 5 second intervals, give or take.
The problem is, this dedicated box cannot establish a connection to the Azure SQL database, despite being able to connect to other remote FTP servers, MySQL servers, etc. The Azure SQL database does have the IP address of the dedicated box on the allowed connection list. Furthermore, I temporarily opened up a massive range of allowed IP addresses (0.0.0.0 -> 255.255.255.255) on the Azure SQL database to see whether this inability to connect may have resulted from IP blocking.
Does anyone have any suggestions or thoughts on what might be causing this and how I could begin debugging the situation better?
To clarify: I can connect to the Azure SQL database from laptops with individual IP addresses in my office and elsewhere, so long as they have been added to the database whitelist; I can make outward connections to remote FTP and MySQL servers from the dedicated box; I have tried to open-up a massive range of allowed IP addresses on Azure SQL with no luck.
C:\Users\graphite.rack.ID17157>osql -S v7o06blktw.database.windows.net -U XXXXXXX@v7o06blktw -P XXXXXXX [SQL Server Native Client 11.0] Named Pipes Provider: Could not open a connection to SQL Server . [SQL Server Native Client 11.0] Login timeout expired [SQL Server Native Client 11.0] A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Windows Azure SQL Database (formerly known as SQL Azure) works exclusively and only on TCP port 1433. It only support SQL Server Authentication, TCP connection and TDS protocol as of today.
In order to successfully establish connection to SQL Azure one must fulfil the following requirements:
- Create SQL Azure server & Database
- Setup SQL Azure Server's firewall rules to accept connections from the IP address of application that will connect to that server
- Make sure the box (be it Virtual, or home, or whatever) has no blocking outbound TCP port 1433
- Explicitly force encryption in connection string
- Explicitly chose to not trust server certificate in connection string
Please note that many (if not all) ISPs (Internet Service Providers) and Hosters, as well as IT staff within companies DO block outgoing TCP Port 1433 due to the SQL Slammer worm. This outgoing port blocking appears to be one of the most faced issues of newcommers to SQL Azure.
UPDATE Nov. 2015
As of August 2015, there is preview feature that enables you to use Azure AD to authenticate to Azure SQL Database. You can read more on this new preview feature here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/
If you're using corporate network for internet access and facing this problem, a simple way around this is to use your phone's mobile hotspot for internet access instead.
Be sure to disable your ethernet connection too, which may also be connected the the internet.
Spent hours on this issue. The fix for me was finally found to be my setting on the Xfinity firewall. I had it set to high. Once I changed the setting to low security, I could connect to the Azure sql database with no issues.