How to connect Azure VM SQL Server in Local SSMS - techie

Latest

Friday, March 23, 2018

How to connect Azure VM SQL Server in Local SSMS

Microsoft Azure VM with SQL Server there are a couple of more advances that you have to take to make remote associations. The technique beneath begins with a fresh Azure VM provisioned and walks through the way toward building up an association by means of SQL Server Management Studio, introduced on an on-premises work station.

  1. Select the SQL VM  image from gallery.

  • Using your account with Log in to the Azure portal
  • Click New. On the Azure portal
  • Click Compute  and Search SQL Server
  • Press / Enter

    • Basic configure setting

      • Enter a virtual machine Name.
      • Select the SSD 
      •  Put the User name for the Local administrator account on the VM. This account is the SQL Server sysadmin server role.
      • Put the strong Password.
      • Select & Verify the correct subscription for the new VM.
      • The Resource group box, type a name for a new resource group. Alternatively, to use an existing resource group click Use existing.
      • Select the Location for your host deployment.
      • Click OK and save the settings.

      • Choose virtual machine size.
        • On the Size step,choose a virtual machine size in the Choose a size windows.

        • Configure optional features.
          • Under Storage, select Yes under use Managed Disks.


          • Configure SQL server settings
            • SQL connectivity
            • If you require SQL Server Authentication, click Enable under SQL authentication.
            • Click Storage configuration to specify the storage requirements.
            • Enable automatic database backups for all database under Automated backup. Automated backup is disabled by default.

            • Click Create.

            2.Open the VM with Remote Desktop

            • The following steps to connect to the SQL Server virtual machine with Remote Desktop:
              • After the Azure virtual machine is created and running, click the Virtual machine icon in the Azure portal to view your VMs:

              • Click Connect.
              • Open RDP file that your browser downloads for the VM. 

              • The Remote Desktop Connection notifies you that the publisher of this remote connection cannot be identified. Click Connect to continue.

              • In the Windows Security dialog, click Use a different account. you might have to click More choices to see this. Specify the user name and password that you configured when you created the VM.
              • Click OK to connect.

              • After connecting VM view like:

              3.Enable TCP/IP [SQL Server Configuration Manager]

              • While connected to the virtual machine with remote desktop, Search for Configuration Manager.

              • The SQL Server Configuration Manager, in the console pane, expand SQL Server Configuration.
              • 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.

              • In the console pane, click SQL Server Services. 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.

              • Close SQL Server Configuration Manager.

                4.Connect to SQL Server Remotely

                Selected Public access for the virtual machine and SQL Server Authentication. These settings automatically configured the virtual machine to allow SQL Server connections from any client over the internet (assuming they have the correct SQL login).

                • Configure a DNS Label for the public IP address

                  • In the virtual machine overview, select your Public IP address.
                  • In the properties for your Public IP address, expand Configuration.
                  • Enter a  DNS Label name. 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.
                  • Click the Save button.

                  • Connect to the Database Engine from computer

                    • On a computer connected to the internet, open SQL Server Management Studio (SSMS).

                    • In the Connect to Server or Connect to Database Engine dialog box, edit the Server name value. Enter the IP address or Full DNS name of the virtual machine (determined in the previous task). You can also add a command provide SQL Server's TCP port. For example,
                        • mysqlvmlabel.eastus.cloudapp.azure.com,1433


                      • In the Authentication box, select SQL Server Authentication.
                      • In the Login box, type the name of a valid SQl login.
                      • In the Password box, type the password of the login.
                      • Click Connect.

                      • After connecting the SQL Server.




                      No comments:

                      Post a Comment

                      Note: Only a member of this blog may post a comment.