Password is expired when using Visual Studio Release Management

Today I was investigating an issue on Visual Studio Online Release Management, getting a deployment error related to the Azure credentials used for the deployment operation

Password is Expired summary

When going to the log details, the error happens on a resource manager task. The logs show that the password of the user account used to connect to Azure has expired.

Password is Expired

And here comes something to highlight, because the configuration of the service connection between VS Online and Azure has evolved in the latest months, to support both Azure Classic and Resource Manager models. Also note that the tasks you can configure on Visual Studio Release Management can work with one of these models or both. Let me show with an example.

Configuring Azure service connection on VS Online Release Management

To setup the Azure connection on Release Management, you need to click on the “Manage Project”

ManageProject

Once there, go the “Services” tab and when clicking on the “New Service Endpoint” you will see two ways to connect to Azure: Azure Classic and the new Azure Resource Manager. A few months back there was an only option to support Classic and RM scenarios, but this changed later. The main difference now is the way each connection authenticates with Azure

SNAGHTMLd1dcd0b

So a big difference here is the use of a service principal on Azure Resource Manager connections instead of using a user principal when using Azure Classic connections. This is important for our case, because the “Password is expired” error message we got refers to a user principal, not to a service principal where the “password” and “expiration” concepts are different.

Note that depending on the task used on Release Management, you can use one or other connection, or only the Classic connection. For example:

Fixing the “password is expired” issue

Once we understand these concepts, we have just to fix the password expiration issue. The solution has two steps:

  1. Change the password of the user principal and then update the Azure Classic connections with the new password. Note: You should use a long/very strong password for these user principals because of the second step; service principals come to play to stop using user principals in the future;
  2. To avoid this to happen again, change the password expiration policy for this account to don’t expire.

The first step can be easily done manually. For the second step, we need the help of Azure AD PowerShell module.

Install Azure AD PowerShell module

On this MSDN article you can find all the information related to managing Azure AD via PowerShell: https://msdn.microsoft.com/en-us/library/jj151815.aspx The Azure AD module is supported on the following Windows operating systems with the default version of Microsoft .NET Framework and Windows PowerShell: Windows 8.1, Windows 8, Windows 7, Windows Server 2012 R2, Windows Server 2012, or Windows Server 2008 R2. You need to install two utilities:

Connecting to Azure AD

Once you have installed both utilities, to start using all the Azure AD cmdlets just open a PowerShell command prompt.

$msolcred = get-credential
connect-msolservice -credential $msolcred

Obviously you need to introduce admin credentials if you want to use the administrative cmdlets later, like changing the password expiration policy.

Change the user principal password expiration policy

Once logged in, we can just change the password expiration policy for the user with this script:

# Gets the current policiy value
Get-MsolUser -UserPrincipalName «releasemanagement@mytenant.onmicrosoft.com» | select PasswordNeverExpires

# Changes the policy to never expire
Get-MsolUser -UserPrincipalName «releasemanagement@mytenant.onmicrosoft.com» | Set-MsolUser -PasswordNeverExpires $true

There is a good blog post about this at https://azure.microsoft.com/en-us/documentation/articles/active-directory-passwords-set-expiration-policy/

What happens with service principals? Passwords never expire?

Service principals works in a different way. When you create a service principal, you can specify the StartDate and EndDate for the security principal credential (by default, StartDate=Now; EndDate = Now + 1 Year). You can change the EndDate in a similar way (is not a boolean flag, you need to set the EndDate).

For more information, visit the MSDN article https://msdn.microsoft.com/en-us/library/dn194091.aspx

Rebuilding SQL Database indexes using Azure Automation

Some days back I had a discussion with an Azure SQL Database engineer about the need of rebuilding indexes on SQL Database. I thought that was a best practice but the engineer told me that the task was accomplished automatically as part of the managed service, in the same way you haven’t had to execute any file related task.

I remembered the thing today and found two interesting articles from Alexandre Brisebois just confirming my initial thoughts, that the indexing management is under your responsibility, and you need to pay attention to how fragmented they are.

There are three good blog posts from Alexandre on the matter I found interesting:

Running the following T-SQL on a SQL Database, you can get the index fragmentation on a specific table in percent:

SELECT name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

       DB_ID(N’MyDatabaseName’)

     , OBJECT_ID(‘MyTableName’)

     , NULL

     , NULL

     , NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

If you want to get all the indexes in a database with more than a 30% of fragmentation, you can run this other one:

SELECT name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

       DB_ID(N’MyDatabaseName’)

     , NULL

     , NULL

     , NULL

     , NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE avg_fragmentation_in_percent > 30

image

And now don’t be scared when seeing the results. The good news is that you are going to get better performance after rebuilding the indexes without having to scale to another SQL Database tier. Alexandre itself commented something about the possibility of using Azure Automation to do the task.

Reindexing using Azure Automation

I’m in love with Azure Automation and all the small things that can be automated to do our daily job easier, mostly based on running a small script on a schedule. Curiously there is a PowerShell Workflow Runbook on the Automation Gallery that allows to fully automate the SQL Database reindexing. Let’s see how to configure it step by step:

1) Provision an Automation Account if you don’t have any, by going to https://portal.azure.com and select New > Management > Automation Account

image

2) After creating the Automation Account, open the details and now click on Runbooks > Browse Gallery

image

3) Type on the search box the word “indexes” and the runbook “Indexes tables in an Azure database if they have a high fragmentation” appears:

image

4) Note that the author of the runbook is the SC Automation Product Team at Microsoft. Click on Import:

image

5) After importing the runbook, now let’s add the database credentials to the assets. Click on Assets > Credentials and then on “Add a credential…” button.

image

6) Set a Credential name (that will be used later on the runbook), the database user name and password:

image

7) Now click again on Runbooks and then select the “Update-SQLIndexRunbook” from the list, and click on the “Edit…” button. You will be able to see the PowerShell script that will be executed:

image

8) If you want to test the script, just click on the “Test Pane” button, and the test window opens. Introduce the required parameters and click on Start to execute the index rebuild. If any error occurs, the error is logged on the results window. Note that depending on the database and the other parameters, this can take a long time to complete:

image

9) Now go back to the editor, and click on the “Publish” button enable the runbook. If we click on “Start”, a window appears asking for the parameters. But as we want to schedule this task, we will click on the “Schedule” button instead:

image

10) Click on the Schedule link to create a new Schedule for the runbook. I have specified once a week, but that will depend on your workload and how your indexes increase their fragmentation over time. You will need to tweak the schedule based on your needs and by executing the initial queries between executions:

image

11) Now introduce the parameters and run settings:

image

NOTE: you can play with having different schedules with different settings, i.e. having a specific schedule for a specific table.

With that, you have finished. Remember to change the Logging settings as desired:

image

Conclusion

Cool? SUPERCOOL!!

Un saludo y happy coding!