question

Chapter7-2723 avatar image
0 Votes"
Chapter7-2723 asked bhanujkn commented

Microsoft SQL Server studio Management

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?

azure-sql-virtual-machines
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

bhanujkn avatar image
0 Votes"
bhanujkn answered

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://docs.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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Chapter7-2723 avatar image
0 Votes"
Chapter7-2723 answered bhanujkn commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you installed SQL on Azure Virtual machine, then yes, you need to open port (1433) and same thing, you need to allow your IP address in Firewall configuration of Azure virtual machine.

Take a look at these articles.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/ways-to-connect-to-sql


https://docs.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server-azure-vm?view=sql-server-ver15

0 Votes 0 ·
Chapter7-2723 avatar image
0 Votes"
Chapter7-2723 answered Chapter7-2723 edited

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. 129827-111.png



111.png (18.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

bhanujkn avatar image
0 Votes"
bhanujkn answered

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)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Chapter7-2723 avatar image
0 Votes"
Chapter7-2723 answered bhanujkn commented

Hi,

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1433 is port which is used to connect to SQL Server.

0 Votes 0 ·