连接到 Azure 上的 SQL Server 虚拟机Connect to a SQL Server Virtual Machine on Azure

概述Overview

本主题介绍如何连接到运行于 Azure 虚拟机的 SQL Server 实例。This topic describes how to connect to your SQL Server instance running on an Azure virtual machine. 涵盖一些常规连接方案,并在门户中提供更改连接设置的相关步骤It covers some general connectivity scenarios and then provides steps in the portal for changing connectivity settings. 如果需要在门户外排除连接故障或配置连接,请参阅主题末尾部分的手动配置If you need to troubleshoot or configure connectivity outside of the portal, see the manual configuration at the end of this topic.

如需观看预配和连接的完整演练,请参阅在 Azure 上预配 SQL Server 虚拟机If you would rather have a full walk-through of both provisioning and connectivity, see Provisioning a SQL Server Virtual Machine on Azure.

连接方案Connection scenarios

客户端连接虚拟机上运行的 SQL Server 的方式取决于客户端的位置与网络配置。The way a client connects to SQL Server running on a Virtual Machine differs depending on the location of the client and the networking configuration.

如果在 Azure 门户中预配 SQL Server VM,则可以选择指定“SQL 连接”的类型。If you provision a SQL Server VM in the Azure portal, you have the option of specifying the type of SQL connectivity.

预配期间的公共 SQL 连接选项

用于连接的选项包括:Your options for connectivity include:

选项Option 说明Description
公共Public 通过 Internet 连接到 SQL ServerConnect to SQL Server over the internet
专用Private 连接到同一虚拟网络中的 SQL ServerConnect to SQL Server in the same virtual network
本地Local 在同一虚拟机上本地连接到 SQL ServerConnect to SQL Server locally on the same virtual machine

以下各节详细说明了“公共”和“专用”选项。The following sections explain the Public and Private options in more detail.

通过 Internet 连接到 SQL ServerConnect to SQL Server over the Internet

如果想要通过 Internet 连接到 SQL Server 数据库引擎,则请在预配过程中通过门户选择“公共”作为“SQL 连接”类型。If you want to connect to your SQL Server database engine from the Internet, select Public for the SQL connectivity type in the portal during provisioning. 门户自动执行以下步骤:The portal automatically does the following steps:

  • 为 SQL Server 启用 TCP/IP 协议。Enables the TCP/IP protocol for SQL Server.
  • 配置防火墙规则以打开 SQL Server TCP 端口(默认值为 1433)。Configures a firewall rule to open the SQL Server TCP port (default 1433).
  • 启用公共访问所需的 SQL Server 身份验证。Enables SQL Server Authentication, required for public access.
  • 在 VM 上将网络安全组配置为 SQL Server 端口上的所有 TCP 流量。Configures the network security group on the VM to all TCP traffic on the SQL Server port.

Important

SQL Server Developer Edition 和 Express Edition 的虚拟机映像不会自动启用 TCP/IP 协议。The virtual machine images for the SQL Server Developer and Express editions do not automatically enable the TCP/IP protocol. 对于 Developer Edition 和 Express Edition,在创建 VM 后,必须使用 SQL Server 配置管理器手动启用 TCP/IP 协议For Developer and Express editions, you must use SQL Server Configuration Manager to manually enable the TCP/IP protocol after creating the VM.

任何可以访问 Internet 的客户端都可以连接到 SQL Server 实例,只需指定虚拟机的公共 IP 地址或分配到该 IP 地址的任何 DNS 标签即可。Any client with internet access can connect to the SQL Server instance by specifying either the public IP address of the virtual machine or any DNS label assigned to that IP address. 如果 SQL Server 端口为 1433,则不需在连接字符串中进行指定。If the SQL Server port is 1433, you do not need to specify it in the connection string. 以下连接字符串使用 SQL 身份验证(还可以使用公共 IP 地址)连接到具有 DNS 标签 sqlvmlabel.chinaeast.cloudapp.chinacloudapi.cn 的 SQL VM。The following connection string connects to a SQL VM with a DNS label of sqlvmlabel.chinaeast.cloudapp.chinacloudapi.cn using SQL Authentication (you could also use the public IP address).

Server=sqlvmlabel.chinaeast.cloudapp.chinacloudapi.cn;Integrated Security=false;User ID=<login_name>;Password=<your_password>

尽管客户端可通过 Internet 进行连接,但这并不意味着任何人都可以连接到 SQL Server。Although this enables connectivity for clients over the internet, this does not imply that anyone can connect to your SQL Server. 外部客户端必须有正确的用户名和密码。Outside clients have to the correct username and password. 但是,为了提高安全性,可以不使用 1433 这个众所周知的端口。However, for additional security, you can avoid the well-known port 1433. 例如,如果将 SQL Server 配置为在端口 1500 上进行侦听并制定了适当的防火墙和网络安全组规则,则可将端口号附加到服务器名称上进行连接。For example, if you configured SQL Server to listen on port 1500 and established proper firewall and network security group rules, you could connect by appending the port number to the Server name. 下面的示例通过将自定义端口号 1500 添加到服务器名称,对前一个端口号进行了更改:The following example alters the previous one by adding a custom port number, 1500, to the server name:

Server=sqlvmlabel.chinaeast.cloudapp.chinacloudapi.cn,1500;Integrated Security=false;User ID=<login_name>;Password=<your_password>"

Note

在 VM 中通过 Internet 查询 SQL Server 时,Azure 数据中心的所有传出数据都将服从常规出站数据传输的定价When you query SQL Server in a VM over the internet, all outgoing data from the Azure datacenter is subject to normal pricing on outbound data transfers.

在虚拟网络中连接到 SQL ServerConnect to SQL Server within a virtual network

在门户中为“SQL 连接”类型选择“专用”时,Azure 会将大多数设置配置为相同的“公共”。When you choose Private for the SQL connectivity type in the portal, Azure configures most of the settings identical to Public. 其中一个区别在于,不存在允许 SQL Server 端口(默认值为 1433)上的外部流量的网络安全组规则。The one difference is that there is no network security group rule to allow outside traffic on the SQL Server port (default 1433).

Important

SQL Server Developer Edition 和 Express Edition 的虚拟机映像不会自动启用 TCP/IP 协议。The virtual machine images for the SQL Server Developer and Express editions do not automatically enable the TCP/IP protocol. 对于 Developer Edition 和 Express Edition,在创建 VM 后,必须使用 SQL Server 配置管理器手动启用 TCP/IP 协议For Developer and Express editions, you must use SQL Server Configuration Manager to manually enable the TCP/IP protocol after creating the VM.

专用连接通常与虚拟网络结合使用,从而启用多个方案。Private connectivity is often used in conjunction with Virtual Network, which enables several scenarios. 可以连接同一虚拟网络中的 VM,即使这些 VM 位于不同的资源组中。You can connect VMs in the same virtual network, even if those VMs exist in different resource groups. 使用站点到站点 VPN,可以创建连接 VM 与本地网络和计算机的混合体系结构。And with a site-to-site VPN, you can create a hybrid architecture that connects VMs with on-premises networks and machines.

虚拟网络还允许将 Azure VM 加入域。Virtual networks also enable you to join your Azure VMs to a domain. 这是对 SQL Server 使用 Windows 身份验证的唯一方式。This is the only way to use Windows Authentication to SQL Server. 其他连接方案需要使用用户名和密码进行 SQL 身份验证。The other connection scenarios require SQL Authentication with user names and passwords.

假设已在虚拟网络中配置 DNS,则可在连接字符串中指定 SQL Server VM 计算机名来连接 SQL Server 实例。Assuming that you have configured DNS in your virtual network, you can connect to your SQL Server instance by specifying the SQL Server VM computer name in the connection string. 以下示例还假设同时已配置 Windows 身份验证,并且用户已获得访问 SQL Server 实例的权限。The following example also assumes that Windows Authentication has also been configured and that the user has been granted access to the SQL Server instance.

Server=mysqlvm;Integrated Security=true

更改 SQL 连接设置Change SQL connectivity settings

可以更改 Azure 门户中的 SQL Server 虚拟机的连接设置。You can change the connectivity settings for your SQL Server virtual machine in the Azure portal.

  1. 在 Azure 门户中,选择“虚拟机”。In the Azure portal, select Virtual Machines.

  2. 选择 SQL Server VM。Select your SQL Server VM.

  3. 在“设置”下,单击“SQL Server 配置”。Under Settings, click SQL Server configuration.

  4. 将“SQL 连接级别”更改为所需设置。Change the SQL connectivity level to your required setting. 可以选择性地使用此区域来更改 SQL Server 端口或 SQL 身份验证设置。You can optionally use this area to change the SQL Server port or the SQL Authentication settings.

    更改 SQL 连接性

  5. 请等待几分钟时间以完成更新。Wait several minutes for the update to complete.

    SQL VM 更新通知

为 Developer Edition 和 Express Edition 启用 TCP/IPEnable TCP/IP for Developer and Express editions

更改 SQL Server 连接性设置时,Azure 不会为 SQL Server Developer Edition 和 Express Edition 自动启用 TCP/IP 协议。When changing SQL Server connectivity settings, Azure does not automatically enable the TCP/IP protocol for SQL Server Developer and Express editions. 以下步骤说明了如何手动启用 TCP/IP,以便通过 IP 地址进行远程连接。The steps below explain how to manually enable TCP/IP so that you can connect remotely by IP address.

首先,通过远程桌面连接到 SQL Server 计算机。First, connect to the SQL Server machine with remote desktop.

  1. 创建并运行 Azure 虚拟机以后,单击 Azure 门户中的“虚拟机”图标即可查看 VM。After the Azure virtual machine is created and running, click the Virtual Machines icon in the Azure portal to view your VMs.

  2. 单击对应于新建 VM 的省略号 ...。Click the ellipsis, ..., for your new VM.

  3. 单击“连接”。Click Connect.

    在门户中连接到 VM

  4. 打开浏览器为 VM 下载的 RDP 文件。Open the RDP file that your browser downloads for the VM.

  5. “远程桌面连接”会通知你,无法识别此远程连接的发布者。The Remote Desktop Connection notifies you that the publisher of this remote connection cannot be identified. 单击“ 连接 ”以继续。Click Connect to continue.

  6. 在“Windows 安全性”对话框中,单击“使用其他帐户”。In the Windows Security dialog, click Use a different account. 可能需要单击“更多选项”才能看到此项。You might have to click More choices to see this. 指定在创建 VM 时配置的用户名和密码。Specify the user name and password that you configured when you created the VM. 必须在用户名之前添加反斜杠。You must add a backslash before the user name.

    远程桌面身份验证

  7. 单击“确定”进行连接。Click OK to connect.

接下来,通过“SQL Server 配置管理器”启用 TCP/IP 协议。Next, enable the TCP/IP protocol with SQL Server Configuration Manager.

  1. 使用远程桌面连接到虚拟机以后,搜索“配置管理器”:While connected to the virtual machine with remote desktop, search for Configuration Manager:

    打开 SSCM

  2. 在“SQL Server 配置管理器”的控制台窗格中,展开“SQL Server 网络配置”。In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.

  3. 在控制台窗格中,单击“MSSQLSERVER 的协议”(默认实例名称)。在详细信息窗格中,右键单击“TCP”,然后单击“启用”(如果尚未启用)。In the console pane, click Protocols for MSSQLSERVER (the default instance name.) In the details pane, right-click TCP and click Enable if it is not already enabled.

    启用 TCP

  4. 在控制台窗格中,单击“SQL Server 服务”。In the console pane, click SQL Server Services. 在详细信息窗格中,右键单击“SQL Server (实例名)”(默认实例为 SQL Server (MSSQLSERVER)),然后单击“重启”以停止并重启该 SQL Server 实例。In the details pane, right-click SQL Server (instance name) (the default instance is SQL Server (MSSQLSERVER)), and then click Restart, to stop and restart the instance of SQL Server.

    重新启动数据库引擎

  5. 关闭 SQL Server 配置管理器。Close SQL Server Configuration Manager.

有关启用 SQL Server 数据库引擎的协议的详细信息,请参阅启用或禁用服务器网络协议For more information about enabling protocols for the SQL Server Database Engine, see Enable or Disable a Server Network Protocol.

使用 SSMS 进行连接Connect with SSMS

以下步骤演示如何为 Azure VM 创建可选 DNS 标签,然后与 SQL Server Management Studio (SSMS) 进行连接。The following steps show how to create an optional DNS Label for your Azure VM and then connect with SQL Server Management Studio (SSMS).

配置用于公共 IP 地址的 DNS 标签Configure a DNS Label for the public IP address

若要从 Internet 连接到 SQL Server 数据库引擎,请考虑创建用于公共 IP 地址的 DNS 标签。To connect to the SQL Server Database Engine from the Internet, consider creating a DNS Label for your public IP address. 可以通过 IP 地址进行连接,但 DNS 标签可以创建更容易标识的 A 记录,并可抽象基础性公共 IP 地址。You can connect by IP address, but the DNS Label creates an A Record that is easier to identify and abstracts the underlying public IP address.

Note

如果打算只连接到同一虚拟网络中的 SQL Server 实例,或者只进行本地连接,则 DNS 标签不是必需的。DNS Labels are not required if you plan to only connect to the SQL Server instance within the same Virtual Network or only locally.

若要创建 DNS 标签,请首先在门户中选择“虚拟机” 。To create a DNS Label, first select Virtual machines in the portal. 选择要显示其属性的 SQL Server VM。Select your SQL Server VM to bring up its properties.

  1. 在虚拟机概览中,选择“公共 IP 地址”。In the virtual machine overview, select your Public IP address.

    公共 ip 地址

  2. 在公共 IP 地址的属性中,展开“配置” 。In the properties for your Public IP address, expand Configuration.

  3. 输入 DNS 标签名称。Enter a DNS Label name. 此名称是一种可通过名称而非 IP 地址直接连接到 SQL Server VM 的 A 记录。This name is an A Record that can be used to connect to your SQL Server VM by name instead of by IP Address directly.

  4. 单击“保存”按钮 。Click the Save button.

    dns 标签

从其他计算机连接到数据库引擎Connect to the Database Engine from another computer

  1. 在连接到 Internet 的计算机上,打开 SQL Server Management Studio (SSMS)。On a computer connected to the internet, open SQL Server Management Studio (SSMS). 如果没有 SQL Server Management Studio,可以从此处下载。If you do not have SQL Server Management Studio, you can download it here.

  2. 在“连接到服务器”或“连接到数据库引擎”对话框中,编辑“服务器名称”值。In the Connect to Server or Connect to Database Engine dialog box, edit the Server name value. 输入虚拟机的 IP 地址或完整 DNS 名称(已在上一个任务中确定)。Enter the IP address or full DNS name of the virtual machine (determined in the previous task). 也可添加逗号并提供 SQL Server 的 TCP 端口。You can also add a comma and provide SQL Server's TCP port. 例如,mysqlvmlabel.chinaeast.cloudapp.chinacloudapi.cn,1433For example, mysqlvmlabel.chinaeast.cloudapp.chinacloudapi.cn,1433.

  3. 在“身份验证”框中,选择“SQL Server 身份验证”。In the Authentication box, select SQL Server Authentication.

  4. 在“登录” 框中,键入有效 SQL 登录的名称。In the Login box, type the name of a valid SQL login.

  5. 在“密码” 框中,键入登录的密码。In the Password box, type the password of the login.

  6. 单击“连接” 。Click Connect.

    ssms 连接

手动配置和故障排除Manual configuration and troubleshooting

尽管门户提供自动配置连接的选项,但了解如何手动配置连接也非常重要。Although the portal provides options to automatically configure connectivity, it is useful to know how to manually configure connectivity. 了解相关要求也有助于进行故障排除。Understanding the requirements can also aid troubleshooting.

下表列出了连接到在 Azure VM 中运行的 SQL Server 的要求。The following table lists the requirements to connect to SQL Server running in an Azure VM.

要求Requirement 说明Description
启用 SQL Server 身份验证模式Enable SQL Server Authentication mode 除非已在虚拟网络上配置 Active Directory,否则需要进行 SQL Server 身份验证才能连接到远程 VM。SQL Server Authentication is needed to connect to the VM remotely unless you have configured Active Directory on a Virtual Network.
创建 SQL 登录名Create a SQL login 如果使用的是 SQL 身份验证,则需要提供 SQL 登录名,并且用户名和密码还有权访问目标数据库。If you are using SQL Authentication, you need a SQL Login with a user name and password that also has permissions to your target database.
启用 TCP/IP 协议Enable TCP/IP protocol SQL Server 必须允许通过 TCP 连接。SQL Server must allow connections over TCP.
启用 SQL Server 端口的防火墙规则Enable firewall rule for the SQL Server port VM 上的防火墙必须允许 SQL Server 端口(默认为 1433)上的入站流量。The firewall on the VM must allow inbound traffic on the SQL Server port (default 1433).
创建 TCP 1433 的网络安全组规则Create a network security group rule for TCP 1433 如果希望通过 Internet 连接,必须允许 VM 接收 SQL Server 端口(默认为 1433)上的流量。You must allow the VM to receive traffic on the SQL Server port (default 1433) if you want to connect over the internet. 本地和仅虚拟网路连接对此无要求。Local and virtual-network-only connections do not require this. 这是在 Azure 门户中所要求的唯一步骤。This is the only step required in the Azure portal.

Tip

在门户中配置连接时,已为你完成上表中的步骤。The steps in the table above are done for you when you configure connectivity in the portal. 只需使用这些步骤来确认配置或手动为 SQL Server 设置连接。Only use these steps to confirm your configuration or to setup connectivity manually for SQL Server.

后续步骤Next Steps

若要查看预配说明以及这些连接步骤,请参阅在 Azure 上预配 SQL Server 虚拟机To see provisioning instructions along with these connectivity steps, see Provisioning a SQL Server Virtual Machine on Azure.

有关其他与在 Azure VM 中运行 SQL Server 相关的主题,请参阅 SQL Server on Azure Virtual Machines(Azure 虚拟机上的 SQL Server)。For other topics related to running SQL Server in Azure VMs, see SQL Server on Azure Virtual Machines.