Connect to Cosmos DB from local machine

Tomas E 1

Hi,

Can I connect to Cosmos DB via a VPN Gateway without adding my public IP to the firewall?

I'm working from home (laptop) and connect to CosmosDB and SQL server instances during development. I connect using the public IP. The drawback is that I currently don't have a fixed IP and I need to update the firewall almost every day (CosmosDB takes some time to update).

I hoped that a VPN connection to an Azure virtual network would solve the problem and also improve security as I could shut down public access. So I created a VPN Gateway and a Virtual Network (vnet), but I can't get it working because it still uses the public IP from the laptop, and the request is denied.

MongoServerError: Error=13, Details='Request originated from IP xx.xx.xx.xx through public internet. This is blocked by your Cosmos DB account firewall settings. More info: https://aka.ms/cosmosdb-tsg-forbidden

Here is my setup

The CosmosDB and SQL server databases are connected to the vnet (let's call it vnetX) using a private endpoint. I also have a VM on a different vnet (vnetY). I peered vnetX with vnetY and I can connect with ssh to the VM using the private IP, so I know the gateway and vnet works. When I do echo $SSH_CLIENT when I'm connected to the VM I get the private IP the laptop is assigned from the VPN Gateway. I've mapped the addresses of the databases to their private IPs in my hosts file.

Laptop -> VPN Gateway -> vnetX
Cosmos DB -> Private endpoint -> vnetX
SQL server -> Private endpoint -> vnetX
VM -> vnetY -> vnetX

I connect from a Pop_OS! laptop using the build in OpenVPN-client. I would like to be able to use mongosh and sqlcmd to connect to the database.

Adding my public IP to the firewall kind of defeats the purpose of the VPN. So is there a way to connect to Cosmos DB and SQL server using VPN, without adding my public IP to the firewall?

3 answers

msrini-MSFT 9,261 Reputation points Microsoft Employee

2022-09-14T09:23:28.01+00:00

Hi,

yes you can access the SQL DB from your laptop which is connected to the VNET which has the Private Endpoint of the SQL in it.

You will need to create a host name entry to your actual SQL FQDN pointing to the Private Endpoint IP of the SQL. Then open your sqlcmd and try to connect to the SQL using the FQDN which should resolve to the Private Endpoint IP.

Let me know if you have any questions.

Regards,
Karthik Srinivas
Please sign in to rate this answer.
Tomas E 1 Reputation point

2022-09-14T12:22:46.213+00:00

Hi,

Thanks for the reply. This is how my hosts file look like:

172.18.0.4 sqlservername.database.windows.net 172.18.0.5 cosmosdbname.mongo.cosmos.azure.com

I restarted my terminal and now I get to the point where I actually connect to the database, but login fails all the time or I get a connection failed error (timeout). I can connect using the very same credentials using Azure Data Studio. I will test real development as well, hopefully the connection issues are isolated to sqlcmd.

Tomas E 1 Reputation point

2022-09-14T12:47:17.27+00:00

I created a VM and installed sqlcmd. I get the same issue there, when I run a commands (that works on an identical local SQL database) I get connection failed (timeout). The sqlcmd looks like this

/opt/mssql-tools18/bin/sqlcmd -s sqlservername.database.windows.net -U SA -C -d thedb -Q "select * from tablename" -l 30

The error is this

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2749. Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not

accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

However, I tried Azure Data Studio and I am able to connect to the database from my laptop.

Tomas E 1 Reputation point

2022-09-14T15:38:47.623+00:00

The status right now is that I can access the SQL database using Azure Data Studio and during development. sqlcmd does not work, but I can live with that (but maybe not knowing why it doesn't work). So maybe it was working all along, just not with sqlcmd. I should have tested other methods first of course.

If you know why sqlcmd doesn't work, I would be glad to know. But at least the main issue is solved.

The error message I get now is

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login failed for user 'SA'..

It seems that sometimes it times out, other times it fails to log in, but the same credentials work in other applications.
Sign in to comment
KapilAnanth-MSFT 36,861 Reputation points Microsoft Employee

2022-09-14T10:00:52.313+00:00
Hi @Tomas E ,

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 Cosmos DB with Private EndPoint via P2S.

I see you have created the CosmosDB PrivateEndPoint in VNetX

As stated by @msrini-MSFT , you should be able to connect to the DB via P2S as long as the PE and VPN Gateway are in same VNet.

Kindly make sure you are able to successfully connect to the CosmosDB from a VM in the VNetX (This will help us eliminate any issues from CosmosDB side)

Once the above is confirmed, make sure you are using the correct FQDN of the cosmosDB in your host file

Do "ping <cosmosDBFQDN>" and make sure correct IP is getting resolved

Please do let me know if this works or you require additional information on the above.

Cheers,
Kapil.

----------------------------------------------------------------------------------------------------------------

Please don’t forget to close the thread by clicking "Accept the answer" wherever the information provided helps you, as this can be beneficial to other community members.
Please sign in to rate this answer.
Tomas E 1 Reputation point

2022-09-14T14:26:49.86+00:00

(I deleted my previous replies as they were about SQL Server.)

I am able to connect to CosmosDB using mongosh when I have added my public IP to the firewall and is connected to the VPN. If I'm not connected to the VPN, I cannot connect to CosmosDB either, because the hosts file point out the private IP. So I'm pretty sure the only issue I have is that I need to add my public IP to the firewall.

Tomas E 1 Reputation point

2022-09-14T15:54:21.85+00:00

anonymous user-MSFT The status right now:

I have disabled public access to CosmosDB.

I am able to connect to the CosmosDB using the VM.

I cannot access it from my laptop anymore, since the IP isn't in the firewall (and there is no option to add it in private networks).

I could access it (using the VPN) from my laptop when I had public access and my public IP was in the firewall settings.

The error I get is this

MongoDB.Driver.MongoCommandException: Command find failed: Error=13, Details='Request originated from IP xx.xx.xx.xx through public internet. This is blocked by your Cosmos DB account firewall settings. More info: https://aka.ms/cosmosdb-tsg-forbidden

KapilAnanth-MSFT 36,861 Reputation points Microsoft Employee

2022-09-14T16:30:07.617+00:00

Hi @Tomas E ,

I see you are not able to connect to the CosmosDB when you have Public Access disabled.

If you have updated the Host file, then it should only resolve to a Private IP

I am not sure why the request would route over Internet and not via the P2S Tunnel?

Can you please share the output of "ping cosmosdbname.mongo.cosmos.azure.com" and share the output?

Cheers,
Kapil

Tomas E 1 Reputation point

2022-09-15T06:11:04.7+00:00

anonymous user-MSFT Yeah, it's weird. I cannot connect to the database if I'm not connected to the VPN, because I have mapped the database to 172.18.0.5 in my hostst file. So the only way to get access is through the VPN.

Here is the output from ping. I don't get a response from the server, but I guess ping responses is not enabled on the cosmosdb machine (same result from my VM in vnetx).

PING cosmosdb.mongo.cosmos.azure.com (172.18.0.5) 56(84) bytes of data.

Can it be rerouted via Internet somehow, because I use VPN? I mean, how can the CosmosDB know my public IP if I'm connected to the VPN? Does the drivers report my public IP instead of the IP of the interface it communicates via? I will do another test and change the VPN client so that it routes all traffic through the VPN. That way it shouldn't be able to go via Internet.

I have a production vnet that can connect to the database from AKS. So it would seem that this issue has something to do with the VPN and the fact that my public IP address is different from the VPN assigned address.

KapilAnanth-MSFT 36,861 Reputation points Microsoft Employee

2022-09-15T06:52:43.47+00:00

Hi @Tomas E ,

Thanks for the update.

You are right.

The traffic should go via VPN only as it's resolving to a Private IP.

Can you please check if there is any proxy on the client machine in which you are trying this?

If so, is it feasible that we disable this and give it a try

As mentioned, you can configure all traffic to route via VPN and let me know if it helps.

Cheers,
Kapil

Tomas E 1 Reputation point

2022-09-15T07:24:56.28+00:00

Hi anonymous user-MSFT

I don't have a proxy. The tests below show that I need to enable VPN if I have an entry in the hosts file (as expected). I'm running out of ideas now.

If VPN was connected and configured with split tunneling disabled

No entry in hosts file -> I cannot connect

With entry in hosts file -> I cannot connect

If VPN was connected and configured with split tunneling enabled

No entry in the hosts file -> I can connect (but firewall denies access)

With entry in hosts file -> I can connect (but firewall denies access)

If VPN is disconnected

No entry in hosts file -> I can connect (but firewall denies access)

With entry in hosts file -> I cannot connect

Here is the result of traceroute from the cases where I can connect.

Entry in hosts file

traceroute to cosmosdb.mongo.cosmos.azure.com (172.18.0.5), 30 hops max, 60 byte packets 1 * * * 2 * * * (repeat asterisks)

No entry in hosts file

traceroute to traffic-platform-cosmos.mongo.cosmos.azure.com (52.146.131.0), 30 hops max, 60 byte packets 1-11 (omitted) 12 be-7-0.ibr01.dub07.ntwk.msn.net (104.44.17.56) 73.421 ms be-7-0.ibr02.dub07.ntwk.msn.net (104.44.17.58) 82.447 ms 72.670 ms 13 be-1-0.ibr02.dub08.ntwk.msn.net (104.44.7.242) 70.988 ms 71.408 ms 71.330 ms 14 ae120-0.icr01.dub08.ntwk.msn.net (104.44.11.86) 72.703 ms ae122-0.icr02.dub08.ntwk.msn.net (104.44.11.82) 75.130 ms ae104-0.icr03.dub08.ntwk.msn.net (104.44.11.229) 83.283 ms 15 * * * (repeat asterisks)

Tomas E 1 Reputation point

2022-09-15T09:23:55.587+00:00

anonymous user-MSFT Another update.

My colleague is on Windows 10 using the Azure VPN Client and she's experiencing the same issue. She can connect to the SQL Server database but not CosmoDB.

Is it possible that the VPN Gateway reports the public IP to the CosmosDB and not the internal IP, and that is the reason we cannot connect even if we are on the VPN? I can't see how it would be possible otherwise.

KapilAnanth-MSFT 36,861 Reputation points Microsoft Employee

2022-09-15T09:54:25.473+00:00

Hi @Tomas E ,

This issue seems to be strange and I am afraid this would require a deeper investigation, so if you have a support plan, I request you file a support ticket, else please do let us know, we will try and help you get a one-time free technical support.

Cheers,
Kapil

Tomas E 1 Reputation point

2022-09-15T10:42:43.187+00:00

Thanks anonymous user-MSFT,

I will do that.
Sign in to comment
Tomas E 1 Reputation point

2023-02-08T12:11:19.5266667+00:00

I've finally found out what the issue was. It was a missing/incorrect record in the local hosts file.

I had:

<IP> <account-name>.mongo.cosmos.azure.com

<IP2> <account-name>-<region>.privatelink.mongo.cosmos.azure.com

in my hosts file. I was told by MS support that it was important to have the "privatelink" record. That was incorrect, and unfortunately I didn't try without it until recently. So the hosts file should look like this:

<IP> <account-name>.mongo.cosmos.azure.com

<IP2> <account-name>-<region>.mongo.cosmos.azure.com

Now it works!
Please sign in to rate this answer.
KapilAnanth-MSFT 36,861 Reputation points Microsoft Employee

2023-02-08T12:26:32.35+00:00

@Tomas E

Thank you for updating the root cause.

Glad to see that you were able to resolve the issue.

Appreciate your patience with us throughout the troubleshooting process.

Cheers,

Kapil
Sign in to comment

Share via

Connect to Cosmos DB from local machine

3 answers