question

JoeDeNicola-5981 avatar image
0 Votes"
JoeDeNicola-5981 asked Shaun-0516 commented

What permissions does a user need to be able to connect to Azure SQL with SSMS and add their IP address to the firewall?

What permissions are needed for a user to be able to connect to Azure SQL via SSMS and add their IP address (which changes) to the Azure SQL Firewall rules?

I can connect via SSMS without a problem.

When one of my people connects, it prompts them for New Firewall Rule. They using the Sign In button to sign into Azure. But they always get the message:

"The server you specified (xxxxx.database.windows.net) does not exist in any subscription in user@azureaccount.onmicrosoft.com. Either you have signed in with an incorrect account or your server was removed form all subscriptions in this account. Please check your account and try again."

I've gone so far as to give this user:

  • SQL Security Manager role on the resource group

  • SQL Server Contributor role on the resource group

  • Contributor role on the subscription (there is only one subscription)

  • Owner role on the subscription

  • Owner role on the Azure SQL database

  • Network administrator role in Azure

  • Cloud device administrator role in Azure

  • Cloud application administrator role in Azure

What more can be required??


Since they do not have a fixed IP address, I cannot add their IP address manually.

I cannot find anything in the docs that says what permissions they need to add their address to the firewall, and the error message really doesn't even indicate that this is the issue, though it seems to be.










azure-sql-database
· 7
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.

Hello @JoeDeNicola-5981Welcome to Microsoft Q&A and Thank you for posting your answer.
You need to update the IP on the firewall when ever it changes as azure public IPs are not static.
Permissions
To be able to create and manage IP firewall rules for the Azure SQL Server, you will need to either be:
• in the SQL Server Contributor role
• in the SQL Security Manager role
• the owner of the resource that contains the Azure SQL Server

You can also find more information in this document


0 Votes 0 ·

@JoeDeNicola-5981 Were you able to check the above information I provided. Let us know if you need further info.
Thanks

0 Votes 0 ·

This still is not working for me.

I have given the user those roles. I can even log in to portal.azure.com as that user, and manually add the client IP address to the firewall as that user! But it doesn't help.

But when the user tries to connect through SSMS 18 or Azure Data Studio, he receives this error:

The server you specified xxxxxx.database.windows.net does not exist in any subscription in b9b1ea...GUID....0a5a. Either you have signed in with an incorrect account or your server was removed from the subscription(s) in this account. Please check your account again.

When I look at the subscription in the Azure portal, that is not the GUID of the subscription. That GUID in the error message appears under Object ID field when I look at the user's account in Azure Active Directory.

Do I need to add the user in some other way?

0 Votes 0 ·

Hello @JoeDeNicola-5981 Based on the error message, It looks like your Database does not exist within "b9b1ea...GUID....0a5a", Could you please check if that ID matches your tenantID? If so, are you able to select a specific tenant when logging in with SSMS? In some cases , if you don't select a TenantID when logging in, and your user's associated with more than one tenant, the login can default to your default selected directory.
You can also test: creating a new user, specific to your SQL server's tenant, then logging in with that user to see if that helps resolve the issue.
See instructions on how find your TenantID in this document. Please let us know if this is helpful. Thanks


0 Votes 0 ·

This isn't making much sense to me.

In Azure Active Directory, Properties, it shows the Tenant ID as 16326xxxxxxxxxx9d5.

The subscription ID is 131aebeexxxxxxxxxxxa01.

The GUID that I got in the error message is the Object ID of the user I logged in as in the SSMS login AFTER I specify the SQL login and password - it asks to log in to Azure after that so it can add the client IP address to the firewall. When it fails, it says "The server you specified xxxx.database.windows.net does not exist in any subscription in b9b1ea...." but "b9b1ea...." is the Object ID of the Azure AD user I tried to log in as. It's neither a Tenant ID nor a Subscription ID. It's the Object ID of a AD user.

How does a database exist within a tenant? It exists within a subscription, does it not?

There is only one subscription, and there is only one tenant, and there is only one database.













0 Votes 0 ·

Hello @JoeDeNicola-5981 Could you please raise a support ticket Here so our support team can further investigate because we don't have access to your resources. Also share the ticket number so we can track the issue. Thanks


0 Votes 0 ·
Show more comments

0 Answers