无法从Visual Studio（或实际任何地方）连接到AWS EC2 [英] Cannot connect to AWS EC2 from visual studio (or anywhere actually)

问题描述

我有一个AWS服务器（带有IIS的MS SQL SERVER EXPRESS 2008 R2），其弹性IP为54.214.8.111，使用HTTP，MS SQL和FTP配置了安全组，并且可以使用以下命令连接到远程虚拟机：

I have an AWS server (MS SQL SERVER EXPRESS 2008 R2 with IIS), with an elastic IP of 54.214.8.111, security groups configured with HTTP, MS SQL and FTP, and I can connect to the remote virtual machine using the rdp file that amazon gives you in the console.

但是，当我尝试通过Visual Studio连接以测试数据库是否正常工作时，它给了我一个登录失败的提示。用户'dbuser'错误。我在SQL Server（当然是服务器端）上有一个名为dbuser的用户，具有完全访问权限，等等。尽管有所有指南和帮助文档，但我也无法连接到服务器上的ftp。我只是在多台计算机上尝试过相同的操作。

However when I try to connect via visual studio to test if the database is working, it gives me a "Login failed for user 'dbuser' error. I have a user called dbuser on my SQL Server (on the server side of course) with full access permissions etc. I also cannot connect to an ftp on the server, despite all guides and help doc. Something seems to be simply blocking my connection. I have tried the same thing on multiple computers.

除了我怀疑服务器实例之外，所有配置似乎都正确配置了。

Everything seems to be configured correctly except I suspect the server instance.

任何人都可以帮忙吗？PS我对Web开发来说还很陌生，这是我的第一台AWS EC2服务器。

Can anyone help? PS I'm fairly new to web development, this is my first AWS EC2 server.

[更新]我只是尝试在其他地方创建一个新实例位置，同样的错误，也许我不知道如何在EC2上配置SQL Server？

[UPDATE] I just tried to create a new instance in a different location, same error, maybe I don't know how to configure the SQL Server on the EC2?

推荐答案

我最近有

在AWS上远程连接到SQL Server涉及3个主要因素：
AWS-> Windows安全性-> SQL Server设置/安全性。

Connecting to SQL Server remotely on AWS involves 3 main factors: AWS --> Windows Security --> SQL Server Settings/Security.

由于每个区域都有特定的要求，因此连接很容易失败。我将检查清单：

Connections can easily fail because each area has specific requirements. I'll go through the check-list:

AWS：

1. 在AWS管理控制台中，转到安全组，单击适用于您的Windows服务器的组，确保将MS SQL TCP端口1433打开为0.0.0.0或您的特定客户端IP。如果没有，则需要添加它。

1. In AWS management console, go to Security Groups, click on the group that applies to your windows server, make sure MS SQL TCP port 1433 is open to 0.0.0.0 or your specific client IP. If not, you'll need to add it.

请注意服务器的公共IP

Note the Public IP of your server

WINDOWS：

1. RDP到Amazon Windows服务器，开始>管理工具>本地安全策略


2. 单击具有高级安全性的Windows防火墙， Windows防火墙属性，单击公共配置文件选项卡，将防火墙状态设置为开，将入站设置为阻止，将出站设置为允许（或阻止）取决于您的应用程序）。确定。


3. 展开Windows防火墙（在左侧窗格中），右键单击入站规则，单击新建规则。


4. 单击端口选项， Next>，对于TCP，输入1433 Next>，允许连接，Next>，Next>，为其命名（SQL-PORT-ACCESS）

1. RDP to the Amazon Windows server, Start > Administrative Tools > Local Security Policy
2. Click Windows Firewall with Advanced Security, Windows Firewall Properties, Click the "Public Profile" tab, set Firewall State to "ON", Inbound to Block, Outbound to Allow (or block depending on your application). OK.
3. Expand the Windows Firewall (on the left Pane), R-Click the Inbound Rule, Click New Rule.
4. Click Port option, Next > , for TCP, enter 1433 Next >, Allow the connection, Next >, Next > , give it a name (SQL-PORT-ACCESS)

SQL服务器：

1. 使用默认的Windows身份验证通过SSMS登录SQL Server（SQL Server Management Studio）。


2. 在左侧窗格中，右键单击顶部的服务器列表（带有数据库图标的第一个列表），然后选择属性


3. 属性窗口，单击左窗格上的安全性，选择 SQL Server和Windows身份验证


4. 单击连接，选中允许远程连接选项...单击确定。 / li>
   
5. 打开SQL配置管理器，依次开始>程序> Microsoft SQL Server>配置工具> SQL Server配置管理器


6. SQL Server网络配置（扩展），选择 MSSQL协议，R单击 TCP，选择属性（应启用TCP）


7. 单击 IP地址选项卡，检查是否已启用IP1，动态端口为0，TCP端口为1433


8. 一直向下滚动到IPAll部分，在TCP动态端口中输入0，在TCP端口中输入1433。 OK ...


9. 返回左窗格，单击 SQL Server服务，R单击 SQL Server选项，然后选择重新启动。
   （注意：SQL Browser服务器不会影响连接，浏览器服务仅列出可用的服务器，并带有您的特定连接参数，无需启动或担心浏览器）

1. Login to SQL Server with SSMS (SQL Server Management Studio) using the default windows authentication.
2. On the left-pane, R-click the top server listing (with the database icon, the very first listing), and select "Properties"
3. Properties window, click Security on the left pane, choose the "SQL Server and Windows Auth"
4. Click Connections, check the "Allow Remote Connections" option ... Click OK.
5. Open the SQL Configuration Manager, Start > Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager
6. SQL Server Network Configuration (Expand), select Protocols for MSSQL, R-Click TCP , select Properties (TCP should be enabled)
7. Click IP Addresses tab, check that IP1 is enabled, Dynamic Ports is 0, TCP port is 1433
8. Scroll all the way down to IPAll section, Enter 0 in TCP Dynamic Ports, and 1433 in TCP Port. OK...
9. Back on the left pane, click, SQL Server Services, R-Click the SQL Server option, and select "Restart". (NOTE: SQL Browser server does not impact connectivity, browser service only lists available servers, with your specific connection parms, no need to start or worry about the browser)

测试：
您无需前往远程客户端进行测试，首先尝试从服务器上的同一SSMS窗口进行连接。这样可以减少起初可能出现的所有其他问题，如果您可以在此处连接，则可以确认它是可行的。如果无法在您自己的服务器上运行，则问题与Windows安全性，SQL安全性和设置有关。

TESTING: You don't have go to your remote client to test, start by trying to connect from the same SSMS window on the server. This reduces all the other things that can go wrong at first, if you can connect here, you have some confirmation that it works. If it doesn't work from your own server, the issues are related to Windows security and SQL security and setup.

1. 在对象浏览器（SQL Server Management Studio）下，单击连接>数据库引擎 ...


2. 在服务器名称：中，输入您的PUBLIC IP（逗号），然后输入 1433 。例如，如果您的公共IP为 54.4.4.4 ，请输入 54.4.4.4,1433 ，


3. 选择身份验证为 SQL Server，输入登录用户和密码。
   
   

1. Under the Object Explorer (SQL server Management Studio), click "Connect" > Database Engine...
2. In the Server name:, enter your PUBLIC IP, a comma, then 1433. Example, if your public IP is 54.4.4.4 , enter 54.4.4.4,1433,
3. Select the authentication as "SQL Server", enter the login user and password.

1. 如果您使用的是 sa，请记住更改密码。


2. 如果您的连接在本地可用，则可以尝试建立远程客户端连接。

至此，您知道您的SQL Server和用户设置正确。

接下来，尝试在另一台计算机上使用SSMS。如果失败，则可能是防火墙需要进行第二次查看...

Next, try using SSMS on another computer. If that fails, probably the firewall needs a 2nd look...

了解问题的好地方，是否可以从SSMS轻松访问SQL日志，在左窗格中，展开管理，然后是SQL Server日志，当前日志将列出所有问题。

Good place to understand issues, is the SQL logs easily accessible from SSMS, on the left pane, expand Management, then SQL Server Logs, current log will list any problems.

因此，所有这些都涉及到-错过其中一个，您会很沮丧，但是从头开始通过减少测试时的碎片。

So, those are all the parts involved -- miss one and you'll be frustrated, but start by reducing the pieces when testing.

我能够从Windows设备连接到AWS Windows Server / SQL Express上的数据库...

I was able to connect to my database on AWS Windows Server/SQL Express from a Windows device...

祝你好运！

这篇关于无法从Visual Studio（或实际任何地方）连接到AWS EC2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！