您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

连接到 Azure 上的 SQL Server 虚拟机(经典部署)Connect to a SQL Server Virtual Machine on Azure (Classic Deployment)

概述Overview

本主题介绍如何连接到 Azure 中虚拟机上运行的 SQL Server 实例。This topic describes how to connect to your SQL Server instance running on an Azure virtual machine. 介绍一些常规连接方案,并提供在 Azure VM 中配置 SQL Server 连接的详细步骤It covers some general connectivity scenarios and then provides detailed steps for configuring SQL Server connectivity in an Azure VM.

重要

Azure 提供两个不同的部署模型用于创建和处理资源:Resource Manager 和经典模型Azure has two different deployment models for creating and working with resources: Resource Manager and Classic. 本文介绍如何使用经典部署模型。This article covers using the Classic deployment model. Microsoft 建议大多数新部署使用资源管理器模型。Microsoft recommends that most new deployments use the Resource Manager model. 如果使用的是 Resource Manager VM,请参阅使用 Resource Manager 连接到 Azure 上的 SQL Server 虚拟机If you are using Resource Manager VMs, see Connect to a SQL Server Virtual Machine on Azure using Resource Manager.

连接方案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 machine/networking configuration. 这些方案包括:These scenarios include:

备注

使用下列任一方法进行连接之前,必须遵循本文中的步骤配置连接Before you connect with any of these methods, you must follow the steps in this article to configure connectivity.

连接到同一云服务中的 SQL ServerConnect to SQL Server in the same cloud service

可以在同一云服务中创建多个虚拟机。Multiple virtual machines can be created in the same cloud service. 要了解此虚拟机方案,请参阅如何将虚拟机连接到虚拟网络或云服务To understand this virtual machines scenario, see How to connect virtual machines with a virtual network or cloud service. 本方案介绍一台虚拟机上的客户端尝试连接到运行于同一云服务中另一虚拟机的 SQL Server 时的情况。This scenario is when a client on one virtual machine attempts to connect to SQL Server running on another virtual machine in the same cloud service.

在此方案中,可使用 VM 名称(在门户中也显示为计算机名主机名)连接。In this scenario, you can connect using the VM Name (also shown as Computer Name or hostname in the portal). 这是你在创建 VM 时为其提供的名称。This is the name you provided for the VM during creation. 例如,如果将 SQL VM 命名为 mysqlvm,则同一云服务中的客户端 VM 可以使用以下连接字符串连接:For example, if you named your SQL VM mysqlvm, a client VM in the same cloud service could use the following connection string to connect:

"Server=mysqlvm;Integrated Security=false;User ID=<login_name>;Password=<your_password>"

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

如果想要通过 Internet 连接到 SQL Server 数据库引擎,则必须创建虚拟机终结点以进行传入 TCP 通信。If you want to connect to your SQL Server database engine from the Internet, you must create a virtual machine endpoint for incoming TCP communication. 此 Azure 配置步骤将传入 TCP 端口通信定向到虚拟机可以访问的 TCP 端口。This Azure configuration step, directs incoming TCP port traffic to a TCP port that is accessible to the virtual machine.

若要通过 Internet 进行连接,必须使用 VM 的 DNS 名称和(本文中稍后配置的)VM 终结点端口号。To connect over the internet, you must use the VM's DNS name and the VM endpoint port number (configured later in this article). 要查找 DNS 名称,请导航到 Azure 门户,并选择“虚拟机(经典)”。 To find the DNS Name, navigate to the Azure portal, and select Virtual machines (classic). 然后选择虚拟机。Then select your virtual machine. “DNS 名称” 显示在“概述” 部分。The DNS name is shown in the Overview section.

例如,假设一个名为 mysqlvm 的经典虚拟机,其 DNS 名称为 mysqlvm7777.cloudapp.net,VM 终结点为 57500For example, consider a classic virtual machine named mysqlvm with a DNS Name of mysqlvm7777.cloudapp.net and a VM endpoint of 57500. 假设正确配置了连接性,则可从 Internet 上的任意位置使用以下连接字符串访问该虚拟机:Assuming properly configured connectivity, the following connection string could be used to access the virtual machine from anywhere on the internet:

"Server=mycloudservice.cloudapp.net,57500;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 端口。For additional security, don't use the well-known port 1433 for the public virtual machine endpoint. 如果可能,请考虑在终结点上添加 ACL 以将流量限制为允许的客户端。And if possible, consider adding an ACL on your endpoint to restrict traffic only to the clients you permit. 有关在终结点上使用 ACL 的说明,请参阅管理终结点上的 ACLFor instructions on using ACLs with endpoints, see Manage the ACL on an endpoint.

备注

务必注意,使用此方法与 SQL Server 通信时,Azure 数据中心的所有传出数据都将服从出站数据传输的定价It is important to note that when you use this technique to communicate with SQL Server, all outgoing data from the Azure datacenter is subject to normal pricing on outbound data transfers.

连接到同一虚拟网络中的 SQL ServerConnect to SQL Server in the same virtual network

虚拟网络支持其他方案。Virtual Network enables additional scenarios. 可以连接同一虚拟网络中的 VM,即使这些 VM 位于不同的云服务中。You can connect VMs in the same virtual network, even if those VMs exist in different cloud services. 使用站点到站点 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 enables 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.

如果要配置域环境和 Windows 身份验证,则不需要使用本文中的步骤来配置公共终结点或 SQL 身份验证和登录。If you are going to configure a domain environment and Windows Authentication, you do not need to use the steps in this article to configure the public endpoint or the SQL Authentication and logins. 在此方案中,可以在连接字符串中指定 SQL Server VM 名称以连接 SQL Server 实例。In this scenario, you can connect to your SQL Server instance by specifying the SQL Server VM name in the connection string. 以下示例假设同时已配置 Windows 身份验证,并且用户已获得访问 SQL Server 实例的权限。The following example 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"

在 Azure VM 中配置 SQL Server 连接的步骤Steps for configuring SQL Server connectivity in an Azure VM

以下步骤演示如何使用 SQL Server Management Studio (SSMS) 通过 Internet 连接到 SQL Server 实例。The following steps demonstrate how to connect to the SQL Server instance over the internet using SQL Server Management Studio (SSMS). 但是,这些步骤同样适用于使 SQL Server 虚拟机可以通过本地和 Azure 中运行的应用程序访问。However, the same steps apply to making your SQL Server virtual machine accessible for your applications, running both on-premises and in Azure.

必须先完成下列各部分中描述的下列任务,然后才能从其他 VM 或 Internet 连接到 SQL Server 的实例:Before you can connect to the instance of SQL Server from another VM or the internet, you must complete the following tasks as described in the sections that follow:

下图概述了连接路径:The connection path is summarized by the following diagram:

连接到 SQL Server 虚拟机

为虚拟机创建 TCP 终结点Create a TCP endpoint for the virtual machine

要从 Internet 访问 SQL Server,虚拟机必须具有终结点以侦听传入的 TCP 通信。In order to access SQL Server from the internet, the virtual machine must have an endpoint to listen for incoming TCP communication. 此 Azure 配置步骤将传入 TCP 端口通信定向到虚拟机可以访问的 TCP 端口。This Azure configuration step, directs incoming TCP port traffic to a TCP port that is accessible to the virtual machine.

备注

如果在同一云服务或虚拟网络中连接,则不需要创建一个公开访问的终结点。If you are connecting within the same cloud service or virtual network, you do not have to create a publicly accessible endpoint. 在这种情况下,可以继续执行下一步。In that case, you could continue to the next step. 有关详细信息,请参阅连接方案For more information, see Connection Scenarios.

  1. 在 Azure 门户中,选择“虚拟机(经典)”。 On the Azure Portal, select Virtual machines (classic).

  2. 然后选择 SQL Server 虚拟机。Then select you SQL Server virtual machine.

  3. 选择“终结点”,并单击终结点边栏选项卡顶部的“添加”按钮。 Select Endpoints, and then click the Add button at the top of the Endpoints blade.

    用于创建终结点的门户步骤

  4. 在“添加终结点”边栏选项卡上,提供“名称”,例如 SQLEndpoint。 On the Add Endpoint blade, provide a Name such as SQLEndpoint.

  5. 选择“TCP”作为“协议” 。Select TCP for the Protocol.

  6. 为“公用端口”指定端口号,如“57500” 。For Public port, specify a port number such as 57500.

  7. 为“专用端口”,指定 SQL Server 侦听端口,默认为“1433” 。For Private port, specify SQL Server's listening port, which defaults to 1433.

  8. 单击“确定” 创建终结点。Click Ok to create the endpoint.

在 Windows 防火墙中为数据库引擎的默认实例打开 TCP 端口Open TCP ports in the Windows firewall for the default instance of the Database Engine

  1. 通过远程桌面连接到虚拟机。Connect to the virtual machine with Remote Desktop. 有关连接到 VM 的详细说明,请参阅使用远程桌面打开 SQL VMFor detailed instructions on connecting to the VM, see Open a SQL VM with Remote Desktop.

  2. 登录后,在开始屏幕中,键入“WF.msc” ,并按 ENTER。Once signed in, at the Start screen, type WF.msc, and then hit ENTER.

    启动防火墙程序

  3. 在“高级安全 Windows 防火墙”的左窗格中,右键单击“入站规则”,并在操作窗格中单击“新建规则”。 In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.

    新建规则

  4. 在“新建入站规则向导”对话框中,在“规则类型”下,选择“端口”,并单击“下一步”。 In the New Inbound Rule Wizard dialog box, under Rule Type, select Port, and then click Next.

  5. 在“协议和端口”对话框中,使用默认“TCP”。 In the Protocol and Ports dialog, use the default TCP. 然后,在“特定本地端口”框中,键入数据库引擎实例的端口号(即默认实例对应的端口号“1433”,或在终结点步骤中为专用端口选择的端口号)。 In the Specific local ports box, then type the port number of the instance of the Database Engine (1433 for the default instance or your choice for the private port in the endpoint step).

    TCP 端口 1433

  6. 单击“下一步”。 Click Next.

  7. “操作” 对话框中,选择 “允许连接” ,然后单击 “下一步”In the Action dialog box, select Allow the connection, and then click Next.

    安全说明: 选择“只允许安全连接”可增加安全性。 Security Note: Selecting Allow the connection if it is secure can provide additional security. 如果想在环境中配置其他安全性选项,请选择此选项。Select this option if you want to configure additional security options in your environment.

    允许连接

  8. 在“配置文件”对话框中,选择“公用”、“专用”和“域”。 In the Profile dialog box, select Public, Private, and Domain. 然后单击“下一步” 。Then click Next.

    安全说明: 选择“公用”允许通过 Internet 进行访问。 Security Note: Selecting Public allows access over the internet. 只要有可能,就请选择更具限制性的配置文件。Whenever possible, select a more restrictive profile.

    公用配置文件

  9. “名称” 对话框中,输入此规则的名称和说明,再单击 “完成”In the Name dialog box, type a name and description for this rule, and then click Finish.

    规则名称

根据需要为其他组件打开附加端口。Open additional ports for other components as needed. 有关详细信息,请参阅配置 Windows 防火墙以允许 SQL Server 访问For more information, see Configuring the Windows Firewall to Allow SQL Server Access.

将 SQL Server 配置为侦听 TCP 协议Configure SQL Server to listen on the TCP protocol

  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.

配置混合模式的 SQL Server 身份验证Configure SQL Server for mixed mode authentication

在没有域环境的情况下,SQL Server 数据库引擎无法使用 Windows 身份验证。The SQL Server Database Engine cannot use Windows Authentication without domain environment. 要从其他计算机连接到数据库引擎,请将 SQL Server 的身份验证模式配置为混合。To connect to the Database Engine from another computer, configure SQL Server for mixed mode authentication. 混合模式身份验证同时允许 SQL Server 身份验证和 Windows 身份验证。Mixed mode authentication allows both SQL Server Authentication and Windows Authentication.

备注

如果已使用配置的域环境配置了 Azure 虚拟网络,可能没有必要配置混合模式身份验证。Configuring mixed mode authentication might not be necessary if you have configured an Azure Virtual Network with a configured domain environment.

  1. 在连接到虚拟机时,在“开始”页面中,键入“SQL Server Management Studio”,并单击勾选图标。 While connected to the virtual machine, on the Start page, type SQL Server Management Studio and click the selected icon.

    Management Studio 在首次打开时,一定会创建用户 Management Studio 环境。The first time you open Management Studio it must create the users Management Studio environment. 这可能需要一小段时间。This may take a few moments.

  2. Management Studio 会显示“连接到服务器”对话框。 Management Studio presents the Connect to Server dialog box. 在“服务器名称”框中键入要使用对象资源管理器连接到数据库引擎的虚拟机的名称(除了虚拟机名称,还可以使用“(local)”或一个句点作为“服务器名称”)。 In the Server name box, type the name of the virtual machine to connect to the Database Engine with the Object Explorer (Instead of the virtual machine name you can also use (local) or a single period as the Server name). 选择“Windows 身份验证”,在“用户名”框中保留“your_VM_name\your_local_administrator”。 Select Windows Authentication, and leave your_VM_name\your_local_administrator in the User name box. 单击“连接” 。Click Connect.

    连接到服务器

  3. 在 SQL Server Management Studio 的“对象资源管理器”中,右键单击 SQL Server 实例的名称(虚拟机名称),并单击“属性”。 In SQL Server Management Studio Object Explorer, right-click the name of the instance of SQL Server (the virtual machine name), and then click Properties.

    服务器属性

  4. 在“安全性”页上的“服务器身份验证”下,选择“SQL Server 和 Windows 身份验证模式”,并单击“确定”。 On the Security page, under Server authentication, select SQL Server and Windows Authentication mode, and then click OK.

    选择身份验证模式

  5. 在 SQL Server Management Studio 对话框中,单击“确定”接受重新启动 SQL Server 的要求。 In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

  6. 在“对象资源管理器”中,右键单击服务器,并单击“重新启动”。 In Object Explorer, right-click your server, and then click Restart. (如果 SQL Server 代理正在运行,它也必须重新启动。)(If SQL Server Agent is running, it must also be restarted.)

    重新启动

  7. 在 SQL Server Management Studio 对话框中,单击“是”同意重新启动 SQL Server。 In the SQL Server Management Studio dialog box, click Yes to agree that you want to restart SQL Server.

创建 SQL Server 身份验证登录名Create SQL Server authentication logins

要从其他计算机连接到数据库引擎,必须创建至少一个 SQL Server 身份验证登录名。To connect to the Database Engine from another computer, you must create at least one SQL Server authentication login.

  1. 在 SQL Server Management Studio 对象资源管理器中,展开要在其中创建新登录名的服务器实例所在的文件夹。In SQL Server Management Studio Object Explorer, expand the folder of the server instance in which you want to create the new login.

  2. 右键单击“安全性”文件夹,指向“新建”,然后选择“登录名…” 。Right-click the Security folder, point to New, and select Login....

    新建登录名

  3. 在“登录名 - 新建”对话框中的“常规”页上,在“登录名”框中输入新用户的名称。 In the Login - New dialog box, on the General page, enter the name of the new user in the Login name box.

  4. 选择“SQL Server 身份验证” 。Select SQL Server authentication.

  5. 在“密码”框中,输入新用户的密码。 In the Password box, enter a password for the new user. 在“确认密码”框中再次输入该密码。 Enter that password again into the Confirm Password box.

  6. 选择所需的密码强制选项(“强制实施密码策略”、“强制密码过期”和“用户在下次登录时必须更改密码”)。 Select the password enforcement options required (Enforce password policy, Enforce password expiration, and User must change password at next login). 如果使用此登录名,则无需在下次登录时更改密码。If you are using this login for yourself, you do not need to require a password change at the next login.

  7. “默认数据库” 列表中,选择登录名的默认数据库。From the Default database list, select a default database for the login. “master”是此选项的默认值。 master is the default for this option. 如果尚未创建用户数据库,则保留此设置为“master”。 If you have not yet created a user database, leave this set to master.

    登录名属性

  8. 如果这是你创建的第一个登录名,可能会需要将此登录名指派为 SQL Server 管理员。If this is the first login you are creating, you may want to designate this login as a SQL Server administrator. 这样的话,请在“服务器角色”页面上选中“sysadmin”。 If so, on the Server Roles page, check sysadmin.

    备注

    sysadmin 固定服务器角色的成员对数据库引擎具有完全控制权限。Members of the sysadmin fixed server role have complete control of the Database Engine. 应谨慎限制此角色中的成员资格。You should carefully restrict membership in this role.

    sysadmin

  9. 单击“确定”。Click OK.

有关 SQL Server 登录名的详细信息,请参阅创建登录名For more information about SQL Server logins, see Create a Login.

确定虚拟机的 DNS 名称Determine the DNS name of the virtual machine

若要从另一台计算机连接到 SQL Server 数据库引擎,必须知道虚拟机的域名系统 (DNS) 名称。To connect to the SQL Server Database Engine from another computer, you must know the Domain Name System (DNS) name of the virtual machine. (这是 Internet 用于识别虚拟机的名称)。(This is the name the internet uses to identify the virtual machine. 可以使用 IP 地址,但 IP 地址在 Azure 为冗余或维护而移动资源时可能会变更。You can use the IP address, but the IP address might change when Azure moves resources for redundancy or maintenance. DNS 名称将保持不变,因为可将该名称重定向到新的 IP 地址。)The DNS name will be stable because it can be redirected to a new IP address.)

  1. 在 Azure 门户中(或在完成前一步后),选择“虚拟机(经典)”。 In the Azure Portal (or from the previous step), select Virtual machines (classic).

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

  3. 在“虚拟机”边栏选项卡中,复制虚拟机的“DNS 名称”。 On the Virtual machine blade, copy the DNS name for the virtual machine.

    DNS 名称

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

  1. 在连接到 Internet 的计算机上,打开 SQL Server Management Studio。On a computer connected to the internet, open SQL Server Management Studio.

  2. 在“连接到服务器”或“连接到数据库引擎”对话框的“服务器名称”框中,按“DNSName,portnumber”的格式输入虚拟机的 DNS 名称(在前一任务中确定)和公共终结点端口号,例如“mysqlvm.chinacloudapp.cn,57500”。******************In the Connect to Server or Connect to Database Engine dialog box, in the Server name box, enter the DNS name of the virtual machine (determined in the previous task) and a public endpoint port number in the format of DNSName,portnumber such as mysqlvm.cloudapp.net,57500.

    使用 SSMS 进行连接

    如果不记得之前创建的公共终结点端口号,可以在“虚拟机”边栏选项卡的“终结点”区域找到。 If you don't remember the public endpoint port number you previously created, you can find it in the Endpoints area of the Virtual machine blade.

    公用端口

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

  4. 在“登录名”框中,键入在前面的任务中创建的登录名。 In the Login box, type the name of a login that you created in an earlier task.

  5. 在“密码”框中,键入在前面的任务中创建的登录名的密码。 In the Password box, type the password of the login that you create in an earlier task.

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

后续步骤Next Steps

如果还打算针对高可用性和灾难恢复使用 AlwaysOn 可用性组,应该考虑实施侦听器。If you are also planning to use AlwaysOn Availability Groups for high availability and disaster recovery, you should consider implementing a listener. 数据库客户端将连接到侦听器,而不是直接连接到一个 SQL Server 实例。Database clients connect to the listener rather than directly to one of the SQL Server instances. 侦听器将客户端路由到可用性组中的主副本。The listener routes clients to the primary replica in the availability group. 有关详细信息,请参阅在 Azure 中配置 AlwaysOn 可用性组的 ILB 侦听器For more information, see Configure an ILB listener for AlwaysOn Availability Groups in Azure.

请务必查看 Azure 虚拟机上运行的 SQL Server 的所有安全最佳实践。It is important to review all of the security best practices for SQL Server running on an Azure virtual machine. 有关详细信息,请参阅 Azure 虚拟机中 SQL Server 的安全注意事项For more information, see Security Considerations for SQL Server in Azure Virtual Machines.

探索学习路径 :Azure 虚拟机上的 SQL Server。Explore the Learning Path for SQL Server on Azure virtual machines.

有关其他与在 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.