Azure database access denied when using virtual networks

Doug Haining 0

I have created Azure AD users and roles and set up a SQL Server in Azure with a database. When I set up firewall rules in the Azure database under the Networking blade, users can access the database fine. However, if I try to set up a virtual network instead, user authentication works, but database access is denied without specific IP ranges added to a firewall rule.

Is it possible to make it so a user can access the database from any location without necessarily knowing their specific IP address? If so, what are the steps to make it happen?

Authentication needs to be AD Interactive so that we can use MS Authenticator on user's phones to get authenticated to Azure. (AD Integrated does not work).

Thanks.

Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2023-09-07T22:13:09.3733333+00:00

@Doug Haining Thank you for reaching out.

Are you trying to use service endpoints or private link.?

Please provide more details.

Regards,

Oury
Doug Haining 0 Reputation points

2023-09-08T15:24:14.38+00:00

@Oury Ba-MSFT Service endpoints. I set that up in the Database Server page, on the Networking blade. There are 3 tabs - I set the endpoints on the "Public Access" tab. I did not do anything on the other two tabs. The address range I specified is 10.0.0.0/24, which is the only option available to select. Right under the "Virtual Network" section is a firewall section. If I add my IP range there, I can access the database. I don't want to have to do that though because when my users are off site (i.e., business travel, work from home, etc.), their IP address may be unknown.
Doug Haining 0 Reputation points

2023-09-08T16:30:44.1733333+00:00

@Oury Ba-MSFT I just tried adding a private endpoint, and removing the firewall IP address ranges, and after waiting for the IP ranges to filter out through Azure, I tried accessing the database, and it does not work. what else is needed to get a private endpoint to work?
Doug Haining 0 Reputation points

2023-09-12T20:18:56.6066667+00:00

@Oury Ba-MSFT What is needed to get a virtual network to allow connections?
Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2023-09-18T21:05:47.47+00:00

@Doug Haining

Could you please share the error message you are getting.

How are you connecting? What does the server FQDN resolve to?

Regards,

Oury
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.
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.
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.
Doug Haining 0 Reputation points

2023-09-28T23:07:07.3266667+00:00

@Oury Ba-MSFT

The server FQDN resolves to an Azure database. The connection string is:
tcp:(myservername).database.windows.net:1433.

-Doug
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.
Doug Haining 0 Reputation points

2023-09-28T23:21:46.9833333+00:00

@Oury Ba-MSFT

Apologies for the delayed reply. I was out of town for a week. I've been trying to post a response and it keeps getting deleted by the server, so I'm going to break it into chunks.

I am attempting to connect to Azure-SQL from Microsoft Access using Azure-Interactive as the authentication mode. When I attempt to open a database object, a browser window opens and prompts for my Azure credentials. I enter them, and MS Authenticator app verifies my identity. That part works great. Then, back in Access, I get an error message from SQL. After clearing that message I get a second prompt from Access to sign in. That throws a second error. I'll post each error in 2 additional messages.
Doug Haining 0 Reputation points

2023-09-28T23:22:38.9033333+00:00

@Oury Ba-MSFT

The first error message is:

Connection failed:

SQL State: S1000
SQL Server Error: 40613
(Microsoft)(ODBC Driver 18 for SQL Server)(SQL Server) Database (my db name) on server (my server FQDN) is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of (message GUID).
Doug Haining 0 Reputation points

2023-09-28T23:23:34.31+00:00

@Oury Ba-MSFT

The second error message is:

Connection failed:
SQL State: 37000
SQL Server Error: 40615
(Microsoft)(ODBC Driver 18 for SQL Server)(SQL Server)Cannot open server (my server name) requested by the login. Client with IP address (my IP address) is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
Doug Haining 0 Reputation points

2023-09-28T23:24:06.19+00:00

@Oury Ba-MSFT

I realize that adding a firewall rule with appropriate ranges fixes this issue. However, I want to figure out how to make it work with a virtual network or a private endpoint. This is because my remote users will not know their IP addresses or ranges when they are traveling.

If this is not possible please let me know so I can investigate other alternatives.

Thanks

Doug
Doug Haining 0 Reputation points

2023-10-12T19:14:06.6833333+00:00

@Oury Ba-MSFT Any suggestions to the above? I'd appreciate a reply. Thank you.

1 answer

Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2024-01-08T21:00:20.9566667+00:00

Doug Haining

Sorry for the delay in response to the above questions.

Great to hear that adding a firewall rule with appropriate ranges fixed the issue.

I want to figure out how to make it work with a virtual network or a private endpoint.

Connecting via a VNET rule or a private endpoint both require connecting into an Azure VNET. For this you would most likely want to set up a P2S or S2S VPN that your remote users can also use so that their traffic is propagated through an approved virtual network.

Configure a point-to-site VPN connection to a VNet using multiple authentication types: Azure portal

https://learn.microsoft.com/en-us/azure/vpn-gateway/tutorial-site-to-site-portal

https://learn.microsoft.com/en-us/azure/vpn-gateway/point-to-site-about

Regards,

Oury
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

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

Share via

Azure database access denied when using virtual networks

1 answer

Your answer