Microsoft SQL Server studio Management

Question

Hi

I have a VM on azure and I have installed SQL server 2019 in that virtual machine. I want to connect sql server from my local laptop. I have a point to site vpn and also installed Microsoft SQL Server Management Studio 18.9.2.

What ports should be opened from VM NSG?

Answer 1

If you have installed everything and want to connect to SQL Server using local machine first install SSMS, which you have already done.

Now, when you will open SSMS, you need to add credentials of Azure DB

Username and password.

As you will not be authorized to view database, using your local IP, so you would have to add your IP in Azure DB

https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure

Azure Portal -> Databases -> Servers -> Configure and add new rule, with your IP.

Then try to login into your Azure DB using SSMS, it should work.

If you want to create a local instance of SQL Server, you can do as explained here

https://qawithexperts.com/article/sql/download-and-install-sql-server-step-by-step-procedure/311

Although you don't need to create a new local instance to connect to Azure, but in case, you need it, you cacan create local instance and check if SSMS is working fine.

Answer 2

Hi,

I am not using Azure DB. I have installed SQL 2019 on azure virtual machine. Just want to connect that sql from my laptop.

Note:

All users are created in Azure AD.

My questions are:

Any port is need to be opened?

How can I check which Azure AD user has rights to access database?

Answer 3

Hi,

Is it safe if I will open 1433 port from Azure NSG? Actually I do not want to use it from the Internet. I am using Point to Site VPN.

We have not created a SQL VM on that way which is described in the above link. We created a normal VM on azure. then we installed a SQL Server 2019 with our custom Application.

Second question is what information I will put here. I have user in my Azure AD.

Answer 4

yes, safe to open 1433 but when you will be adding your IP in firewall of Azure Virtual machine, then it will be open for you IP only.

about image you need to enter details like URL of Azure Server, which will be something like this

Server name: sqlvmlabel.eastus.cloudapp.azure.com
Login : YourLoginID (you created while creating SQL Server instance on Azure VM)
password: yourpassword (you created while creating SQL Server instance on Azure VM)

Answer 5

Hi,

Why we need to open 1433 if we have P2S VPN?