Azure SQL Database
An Azure relational database service.
4,323 questions
Azure database access denied when using virtual networks
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 77%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDH%3C/text%3E%3C/svg%3E)
Doug Haining
0
Reputation points
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.
{count} votes
-
Oury Ba-MSFT 13,516 Reputation points Microsoft Employee2023-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 13,516 Reputation points Microsoft Employee
2023-09-18T21:05:47.47+00:00 Could you please share the error message you are getting.
How are you connecting? What does the server FQDN resolve to?
Regards,
Oury
-
Doug Haining 0 Reputation points
2023-09-28T23:07:07.3266667+00:00 The server FQDN resolves to an Azure database. The connection string is:
tcp:(myservername).database.windows.net:1433.-Doug
-
Doug Haining 0 Reputation points
2023-09-28T23:21:46.9833333+00:00 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 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 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 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
Sign in to comment