Friday, February 26, 2016

Update Azure SQL Firewall Rule using Powershell


Microsoft Azure SQL only allows connections from whitelisted IP addresses. My ISP seemed to change my external/public IP address daily. The combination of these two things was very annoying for me.

I assembled the powershell script below to make my life a little easier. It updates the firewall rule for each Azure SQL instance, and ensures they are all set to my current IP address.


1. Install and Configure Azure Powershell
https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

2. Execute Get-AzurePublishSettingsFile to get the publishsettings file for the subscription you're going to be working with (a browser window will open, select the profile there. Note: you can add multiple publish settings.).




3. Execute Import-AzurePublishSettingsFile and provide the path to the file downloaded from the Get-AzurePublishSettingsFile command.

4. Get your Public IP address

5. Execute the following commands for each Subscription/Azure SQL database combination

Select-AzureSubscription -SubscriptionId "[yoursubid]"
Set-AzureSqlDatabaseServerFirewallRule -ServerName "[yourservername]" -RuleName "[yourrulename]" -StartIpAddress $ip -EndIpAddress $ip


Hope this helps,
Aaron


Things you might want to check out:


Azure Resource Management vs Service Management
 - the script above uses the classic Service Management because it does not appear the Resource Management supports this functionality at this time.
https://azure.microsoft.com/en-us/documentation/articles/resource-manager-deployment-model/

Note: Even though, this script does not use Resource Management, If you want to use RM in an automated script (non-interactive), you'll need to create a Service Principal account in Active Directory, because the AzureRM cmdlets don't allow for automation with a Microsoft Account (ex: live.com) login flow
 - https://github.com/Azure/azure-powershell/issues/1309
 - http://blog.davidebbo.com/2014/12/azure-service-principal.html
 - https://azure.microsoft.com/en-us/documentation/articles/resource-group-authenticate-service-principal/

Service being used to discover external IP address
http://icanhazip.com/








Monday, February 22, 2016

Convert SQL Server Varbinary to Byte Array

I had a need to convert the HEX output from SQL Server Management Studio's Results to Grid output of a varbinary column back to the byte array of the file it represented. I couldn't find an all-in-one script online, so I thought I'd create this C# gist. (original parse script here)

https://gist.github.com/aaronhoffman/ad16b27d14e2f5d7e16c


Hope this helps,
Aaron

Saturday, February 13, 2016

PowerShell - List File Line Count For Files in a Directory that Match a Given Pattern

I had a need to get the total count of all lines, for a given set of .csv files. For example, there are 5 files each with 1000 lines, I wanted a script that would output: 1000, 1000, 1000, 1000, 1000.

I could not find a script or command to do this, so I wrote one in PowerShell. Enjoy


List File Line Count For Files in a Directory that Match a Given Pattern

https://gist.github.com/aaronhoffman/416b88e64b970c4c9cb4


hope this helps,
Aaron