How to VPN to Azure SQL database

EsthThala 20

I have created a SQL server and database in Azure and i have established and configured a Private Gateway and configured Azure VPN client on my on prem computer.
I get a succesfull connection in the VPN client vut i still can not reach the dtabase in SMSS despite the VPN being connected.
Any solutions?

KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-01T10:17:30.3133333+00:00
@EsthThala

Welcome to the Microsoft Q&A Platform. Thank you for reaching out & I hope you are doing well.

I understand that you would like to connect to your SQL server using Azure VPN P2S.

Please note that:

Azure VPN P2S Connection is used to provide connectivity from remote clients to a resources located in a Virtual Network.

This means, your SQL server should

either be created as a VM connected to this Virtual Network

or use a Private Endpoint in this Virtual Network (if the SQL server is a PaaS service)

May I ask

If your SQL server is a PaaS service? or

If it is a SQL Server on Azure VMs ?

Cheers,

Kapil
EsthThala 20 Reputation points

2023-11-01T10:46:59.9933333+00:00

Hello @KapilAnanth-MSFT

The SQL server is a PaaS server with a Private Endpoint in the VM.

Kind regards, Esther
KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-01T10:55:11.2366667+00:00
@EsthThala

In that case,

May I ask if this PE is working if you try to connect to this from a VM in Azure?

Also, from the P2S Client, are you using the IP of the Private EndPoint or the FQDN of the SQL server to connect?

Note : It is mandatory that you use the FQDN and not the private IP of the Private EndPoint in the SSMS tool.

i.e., you must make sure <yourSqlServerName>.database.windows.net resolves to the private IP of the Private EndPoint

Next Steps:

Update the host file of your remote machine.

Navigate to C:\Windows\System32\drivers\etc\hosts and edit the "hosts" file

Add a line
<PrivateIpOfPE> <yourSqlServerName>.database.windows.net

Then try to connect using SSMS

Open CMD and run, ping <yourSqlServerName>.database.windows.net

And share the results please

Cheers,

Kapil
KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-02T08:12:43.0166667+00:00

@EsthThala

May I know if you got a chance to review my previous comment?

Please let me know if you are facing any challenges or if there are any follow-up questions, I shall be glad to address them.

Thanks,

Kapil
EsthThala 20 Reputation points

2023-11-02T08:29:51.1166667+00:00

Hello again and thank you for your answer.

I always use the FQDN when trying to connect to the database in SMSS.

I have tried adding what you suggested to the host file but it made no difference. SMSS still requires that i add my client ip-adress this despite having a VPN connection.

Kind regards, Esther
KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-02T08:34:10.6566667+00:00
@EsthThala

Open an elevated CMD and run,

ping <yourSqlServerName>.database.windows.net

nslookup <yourSqlServerName>.database.windows.net

And share the results please

Make sure the host file entry was added before you do the above step.

Cheers,

Kapil
EsthThala 20 Reputation points

2023-11-02T08:47:59.1433333+00:00

@KapilAnanth-MSFT

I also did a tracert
KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-02T09:33:28.9966667+00:00
@EsthThala

The above ping command indicates that you did not properly configure the host file.

Because, if you did, the <yourSqlServerName>.database.windows.net should have resolved to the Private IP of the Private EndPoint of the SQL server

And not the public IP of the SQL server as above.

Here's an example from my local system:

Please refer to this document on How to edit host file in Windows machines.

Once done,

Make sure ping is resolving to the Private IP of the PE

P.S : nslookup won't work , check only with Ping

And then try to open using SSMS

Cheers,

Kapil
EsthThala 20 Reputation points

2023-11-02T10:46:43.52+00:00

It is now working as you showed me. Thank you!

Now i am getting a error 18456 in SMSS despite having SQL authetication enabled.

Kind regards, Esther
KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-03T05:53:12.1266667+00:00
@EsthThala

I am glad the set up is working now.

I see you are facing authentication issue with an error 18456 in SMSS.

Unfortunately, this seems to be related to SQL authentication and not a networking issue.

In that case, I would suggest you to post a new thread with only using the "Azure SQL Database" tag and not "Azure VPN Gateway"

This way, community experts in Azure SQL will be able to add their inputs.

I did find this troubleshooting guide here : Unable to log in to the server (errors 18456, 40531)

You may refer to this once

Meanwhile, I shall summarize my comments along with best practices for DNS and post it as a new answer.

I would highly appreciate if you could Accept the answer and close this thread with respect to Networking.

Should there be any follow-up questions or concerns, please let us know and we shall try to address them.

Thanks,

Kapil
KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-06T06:23:05.1766667+00:00

@EsthThala

Reaching out to check if there are any questions on this.

Please let us know if we can be of any further assistance here.

Thanks,

Kapil

Please Accept an answer if correct.

Original posters help the community find answers faster by identifying the correct answer.
KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-07T11:44:29.6833333+00:00

@EsthThala

Just checking in to see if the below answer helped. If this answers your query, please don’t forget to click "Accept the answer" and Up-Vote for the same, which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Thanks,

Kapil
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Accepted answer

KapilAnanth-MSFT 45,451 Reputation points Microsoft Employee

2023-11-03T06:09:34.3366667+00:00
@EsthThala

Welcome to the Microsoft Q&A Platform. Thank you for reaching out & I hope you are doing well.

I understand that you would like to connect to your SQL server using Azure VPN P2S.

Please note that:

Azure VPN P2S Connection is used to provide connectivity from remote clients to a resources located in a Virtual Network.

This means, your SQL server should

either be created as a VM connected to this Virtual Network

or use a Private Endpoint in this Virtual Network (if the SQL server is a PaaS service)

You informed you were using a Private EndPoint.

From your ping results, we can see that,

<yourSqlServerName>.database.windows.net is resolving to the Public IP of the SQL server

This is not ideal, we need it to resolve to the Private EndPoint's IP of the SQL server

If you are trying to access a PaaS service from OnPrem via S2S or from a remote client via P2S, it is mandatory that you configure the DNS resolution at your end to resolve to the Private IP of the Private EndPoint

Modifying the Windows Host file is one of the ways to do the above.

For scale and production, you can consider using one of the below

#1 If you have a custom DNS server at the P2S Remote Client

Make sure you create a DNS Zone with "database.windows.net"

And add the entry "<yourSqlServerName>" and make it resolve to the Private IP of the Private EndPoint

#2 If you have a custom DNS server in Azure,

Consider : On-premises workloads using a DNS forwarder

In the P2S VPN Client configuration file, you should

Add DNS suffixes as ".database.windows.net"

Add custom DNS servers as the IP of the custom DNS server in Azure

Please let us know if we can be of any further assistance here.

Thanks,

Kapil

Please Accept an answer if correct.

Original posters help the community find answers faster by identifying the correct answer.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to VPN to Azure SQL database

0 additional answers

Your answer