无法连接到Azure SQL数据库，即使具有白名单IP [英] Cannot connect to Azure SQL database, even with whitelisted IP

问题描述

我目前无法从私人资料中心的独立远端独立专用方块连接到我的Azure SQL资料库。我有一个Azure SQL资料库可连接到此数据库的IP地址列表。这已经工作完美，直到现在。我最近在私人数据中心设置了一个新的专用箱，需要每隔5秒钟查询Azure SQL数据库。

问题是，这个专用的盒子无法建立到Azure SQL数据库的连接，尽管能够连接到其他远程FTP服务器，MySQL服务器等。Azure SQL数据库确实具有允许的连接列表上的专用框的IP地址。此外，我暂时在Azure SQL数据库上打开了一系列允许的IP地址（0.0.0.0 - > 255.255.255.255），以查看这个无法连接是否由于IP阻止。

有没有人对可能导致这种情况的任何建议或想法，以及如何更好地开始调试情况？

澄清：我可以从笔记本电脑连接到Azure SQL数据库，在我的办公室和其他地方使用单独的IP地址，只要它们已添加到数据库白名单中;我可以从专用盒向外连接到远程FTP和MySQL服务器;我试图在Azure SQL上打开大量允许的IP地址，没有运气。

编辑

  C：\Users\graphite.rack.ID17157> osql -s v7o06blktw.database.windows.net -U XXXXXXX @ v7o06blktw -P XXXXXXX 
 [SQL Server Native Client 11.0]命名管道提供程序：无法打开到SQL Server的
连接[53]。 
 [SQL Server Native Client 11.0]登录超时
 [SQL Server Native Client 11.0]与SQL Server建立连接时发生网络相关或实例特定错误
。服务器不是
找到或无法访问。检查实例名称是否正确，并且是否将SQL Server 
配置为允许远程连接。有关详细信息，请参阅SQL Server 
联机丛书。 
  

解决方案

总结。

Windows Azure SQL数据库（以前称为SQL Azure）仅在TCP端口1433上工作。它仅支持今天的SQL Server身份验证，TCP连接和TDS协议。

为了成功建立与SQL Azure的连接，必须满足以下要求：

• 创建SQL Azure服务器&数据库


• 设置SQL Azure服务器的防火墙规则以从将连接到该服务器的应用程序的IP地址接受连接


• it virtual，or home，or whatever）没有阻止出站TCP端口1433


• 在连接字符串中显式强制加密


• 明确选择不信任服务器连接字符串中的证书

请注意，许多（如果不是全部）ISP（互联网服务提供商）在公司内由于 SQL Slammer蠕虫而阻止传出TCP端口1433。

2015年11月UPDATE

截至2015年8月，有一个预览功能，您可以使用Azure AD对Azure SQL数据库进行身份验证。您可以在这里阅读有关此新预览功能的详情： https ：//azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/

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.

Edit

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 [53].  
[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.

解决方案

To summarize.

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/

这篇关于无法连接到Azure SQL数据库，即使具有白名单IP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！