Get-AzureRmSqlServer cmdlet - Should provide an "Client IP Address" property.

[MaximoTrinidad]

Description

Not getting the Azure SQL Client IP in PowerShell its been bothering me since the Azure Classics, and it still an issue in the AzureRM Sql cmdlets.

I just can't find this information. It force me to use the Azure Portal disrupting my script automation steps.

Of course, If there's a cmdlet that can provide me this information, then let me know. I have searched on "How to get the AzureRM SQL Client IP Address?", but the only place I found it is in the Azure portal.

I don't understand! If the portal has the ability to "Add Client Id" to the Firewall Rule, then why not make available in the New-AzureRmSqlServerFirewallRule Cmdlet.

It will work if I have an ClientIpAddress property accessible at least in the Get-AzureRmSqlServer cmdlet. This will make it much easier to create an Azure Sql Server and then add the firewall rule using the "ClientIpAddress" given to the new SqlServer

Script/Steps for Reproduction

The "Client IP Address" should be included after creating the Azure SQL asset:

## - Providing existing AzureRmSqlServer information:
Get-AzureRmSqlServer -ResourceGroupName azResourceEast01

## - Providing existing AzureRmSqlServer Firewall information:
Get-AzureRmSqlServerFirewallRule -ServerName azsqls01 -ResourceGroupName azResourceEast01

Module Version

PS [98] > Get-Module -ListAvailable AzureRM | Select Name, Version

Name    Version
----    -------
AzureRM 5.4.1

PS [105] > Get-Module -ListAvailable AzureRM.Sql | Select Name, Version

Name        Version
----        -------
AzureRM.Sql 4.2.0

Environment Data

PS [69] > $PSVersionTable

Name                           Value
----                           -----
PSVersion                      5.1.17120.1
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17120.1
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

Debug Output

[praries880]

@jaredmoo Can you comment on this?

[jaredmoo]

This is a great suggestion, thank you! :)

One challenge here is that powershell cmdlets connect to our REST API which is a different endpoint than the server itself, and these two endpoints may have different opinion of what your IP address is depending on virtual networks, etc.

For now, the most accurate solution is to actually try connecting to the server using command line tools such as sqlcmd or https://github.com/dbcli/mssql-cli . If you do not have firewall permission, the error message will tell you what it thinks your IP address is.

Of course this solution is certainly not as convenient as support built into powershell, so we will track this as a new feature request.

[MaximoTrinidad]

@jaredmoo

Greatly appreciate your suggestion! I have no problem connecting.

The main issue is, automating the steps for creating a Azure SQL Server asset. If the initial Client IP is available after creating the Azure SQL Server, why not saving into a property value. This way when the asset is created, I can provide the IP address to create the initial Firewall rule. All in one shot! Then, I can go back and add more firewall rule(s) if needed.

I think it would be great!

In PowerShell, using Azure Classic module, I trapped the error message and extract the IP Address, then I would create the firewall rule without interrupting the automation process. But now, I want to use AzureRM module.

For me it's all about automation! :)

[jaredmoo]

I understand and agree that this would greatly improve automation of the crease server experience.

The problem is that the cmdlets and the REST endpoint that the cmdlets connect to actually don't know what your IP address is. It's true that the portal does this, but the portal implementation is not accessible by cmdlets at the moment.

This is really great feedback and I would love to address this. But I want to be realistic and let you know that it might take some time to implement in a way that is reliable and sustainable in the long term.

Thanks again :)

[MaximoTrinidad]

Thanks again @jaredmoo!

I will patiently wait for for it. And, If I can help in any way, let me know. :)