question

TomHovens-4955 avatar image
1 Vote"
TomHovens-4955 asked AlbertoMorillo commented

Client IP Address does not have access SQL Server Management Studio

Hi,

When trying to log on to my server I get the error "Your client IP address does not have access to the server. Sign in to an Azure Account and create a new firewall rule enable access." After adding the my client address to the Firewall rules the same error popups. When I look in Azure at the firewall setting I see several rows of rules, all with the same ip addresses. I also add the client ip address but no luck. What do I need to change in setting so I can open my database with SSMS.

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

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered

Change the authentication type please:

164914-image.png

Thank you for your patience.


image.png (13.3 KiB)
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.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered

Please make sure you are adding the correct client IP Address. Please add the IP Address returned by this site on Azure SQL Database firewall.



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.

TomHovens-4955 avatar image
0 Votes"
TomHovens-4955 answered

Hi Alberto, thanks for replying. The IP Address returned by the site is already been added.

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.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered

Please make sure you specify the database when you connect to Azure SQL.

164008-image.png

163967-image.png



image.png (13.7 KiB)
image.png (23.2 KiB)
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.

TomHovens-4955 avatar image
0 Votes"
TomHovens-4955 answered

Still no luck

164439-errorsql.png



errorsql.png (25.2 KiB)
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.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo commented

What about setting the firewall at the logical server level? On the Azure SQL Database blade make a click on the Azure logical server.

164496-image.png

An on the left panel, make a click on "Firewall and virtual networks". Add the client IP address there.

164497-image.png



image.png (25.3 KiB)
image.png (38.6 KiB)
· 2
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.

Thank you @AlbertoMorillo for helping customers on Microsoft Q&A forum.

0 Votes 0 ·

Thank you Oury for your kindness, Happy New Year!

0 Votes 0 ·
TomHovens-4955 avatar image
0 Votes"
TomHovens-4955 answered

164420-errorsqlv1.png



errorsqlv1.png (53.7 KiB)
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.

OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered OuryBa-MSFT commented

Hi @TomHovens-4955 Thank you for posting your question on Microsoft Q&A.

Please change to Redirect and try to connect again.

Connection policy
Servers in SQL Database and Azure Synapse support the following three options for the server's connection policy setting:

Redirect (recommended): Clients establish connections directly to the node hosting the database, leading to reduced latency and improved throughput. For connections to use this mode, clients need to:

Allow outbound communication from the client to all Azure SQL IP addresses in the region on ports in the range of 11000 to 11999. Use the Service Tags for SQL to make this easier to manage.
Allow outbound communication from the client to Azure SQL Database gateway IP addresses on port 1433.

164430-image.png



Please let us know if that works

Regards,
Oury


image.png (67.1 KiB)
· 1
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.

Hi @TomHovens-4955 Do not forget to mark as accepted answer wherever information provided was helpful.

Regards,
Oury

0 Votes 0 ·
TomHovens-4955 avatar image
0 Votes"
TomHovens-4955 answered OuryBa-MSFT commented

Hi @OuryBa-MSFT thanks for helping. But after changing the Connection Policy same result

164827-errorsqlv2.png



errorsqlv2.png (39.6 KiB)
· 1
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.


@TomHovens-4955 Could you please share the error message you are getting in SSMS and also how you are trying to login. Are you using SQL authentication, AAD password, AAD integrated ?

Regards,
Oury

0 Votes 0 ·
TomHovens-4955 avatar image
0 Votes"
TomHovens-4955 answered

@OuryBa-MSFT. The left message keeps turning back.

164942-errorsqlv3.png



errorsqlv3.png (33.5 KiB)
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.