Migrating Azure Subscriptions to Organization Accounts (Including Visual Studio Online)

We started creating Azure subscriptions before organization accounts were available, but now when even Visual Studio Online supports organization accounts, we wanted to switch from Microsoft accounts. (Microsoft accounts have the obvious drawbacks: more passwords to remember and access does not go away automatically when people quit.)

The subscription owner (service account) was a Microsoft account – let us call this account1@company.se. The subscriptions were linked to a domain that I refer to here as domain1.onmicrosoft.com, and the new domain, which is synced with our Active Directory, I refer to as company.onmicrosoft.com.

Azure Co-Administrators

So here is what we had to do make the switch with co-administrators:

  1. Change the AD account name of account1@company.se to account2@company.se. Why? Because this account was synced to company.onmicrosoft.com, and there can only be one account with the same name in the same Azure Active Directory (AAD). (The Microsoft account is added in step 4 below.)
  2. Go to manage.windowsazure.com and login as account1@company.se.
  3. Create a new directory. Choose custom create. Select Use existing directory.
  4. Log in as a global administrator of the company.onmicrosoft.com domain. What happens now is that account1@company.se (the Microsoft account) is automatically added as a global administrator in company.onmicrosoft.com.
  5. Log out and log in again as account1@company.se. There should now be a new directory (company.onmicrosoft.com) visible.
  6. Go to settings, select a subscription and click Edit Directory at the bottom of the page. Change to company.onmicrosoft.com. You get a warning saying all co-administrators will be deleted. That is OK, because that is what we wish to accomplish.
  7. Add organization accounts as co-administrators as appropriate.

Azure Service Administrators

Now, we also wanted to change the service administrator from account1@company.se (Microsoft account) to account2@company.se (organization account). That is accomplished in the account portal.

  1. Go to account.windowsazure.com and log in.
  2. Select a subscription.
  3. Click Edit subscription details.
  4. Change the service administrator.

Account Administrator

I don’t know how to change the account administrator. Maybe it requires a support ticket.

Visual Studio Online

Visual Studio Online was the most difficult service. You can administer some aspects in the Azure managment portal (manage.windowsazure.com). But when you connect it to the new domain (company.onmicrosoft.com in my case), users not in that domain will loose access. Because the Microsoft accounts probably do not exist in that domain, you will have to create new users in Visual Studio Online, and because the probably have the same names as the corresponding Microsoft accounts, you must first delete these.

But beware! Before you start deleting users and switch domain, you must make sure no user has anything checked out. You might also want to delete all workspaces, because when the “new” users start Visual Studio and log in, they must create new workspaces, because the old workspaces are owned by different users. And you cannot have work folder mappings in two workspaces to the same local folder on the same computer (error: The working folder is already in use by the workspace…)

Use the following command to list all workspaces:

tf workspaces /server:<instance>.visualstudio.com\DefaultCollection /owner:*

or the following to see details on a specific computer:

tf workspaces /server:instance.visualstudio.com\DefaultCollection /computer:<computer>/owner:* /format:detailed

You can delete a workspace with the following command:

tf workspace /delete /collection:<instance>.visualstudio.com\DefaultCollection "<computer>;<user>"

Or simpler, use Team Foundation Sidekicks. In fact, I did not delete my work space before making the switch, and the only way I could delete it afterwards was to use Sidekicks.

  1. Ensure no user has anything checked out.
  2. Delete workspaces for Microsoft accounts that you plan to abandon in favour of organization account.
  3. Delete accounts in VisualStudio Online using User Management (https://<instance&gt;.visualstudio.com/_user).
  4. Go to manage.windowsazure.com and login as an administrator. Select your Visual Studio Online service and click Configure. Click the Connect button and connect to the company.onmicrosoft.com domain.
  5. MSDN subscribers must connect their Microsoft accounts with their organization accounts in the My Account section of MSDN (https://msdn.microsoft.com/subscriptions/manage/hh442900). Under Visual Studio Online they can create this link.
  6. In Visual Studio Online using User Management, add users again. This time, their organization accounts will be added. If they have MSDN, you should choose Eligible MSDN Subscriber as license.
  7. Click the cog wheel in the upper right corner in Visual Studio Online to go to the control panel. Select a project, click on your team and add team members and administrators.
  8. In Visual Studio, users must switch user by clicking Connect to Team Projects in Team Explorer and then Select Team Projects… In the bottom of the Connect to Team Foundation Server dialog, click Switch User. Enter the organization account credentials.
  9. If a user doesn’t see any code, just work items, he or she has probably a stakeholder license, not an MSDN license. They can check this by logging in to <instance>.visualstudio.com. See step 5 above. If it still doesn’t work, try changing license to basic and then back to MSDN.
Posted in Azure | Tagged , , | Leave a comment

Generating a Large Excel File from Code

This is one way of generating a large Excel file with random data from code:

  1. Fire up Visual Studio 2013.
  2. Create a new Excel 2013 Workbook project.
  3. Use the following code:
        public partial class ThisWorkbook
        {
            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                var random = new Random();
                var activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
                for (int i = 1; i <= 1024*30; i++)
                {
                    byte[] bytes = new byte[768];
                    random.NextBytes(bytes);
                    string s = Convert.ToBase64String(bytes);
                    var range = activeWorksheet.get_Range("A" + i);
                    range.Value = s;
                }
            }
    
    		// ...
    	}
    

    It will create 1024 * 30 rows of 1024 characters each.

  4. Press F5 to run.
Posted in Uncategorized | Tagged , | Leave a comment

Before Cancelling an Azure Subscription, Remove All Virtual Networks

I recently cancelled an Azure Subscription, and then went on to delete all of its resources (virtual machines, databases, etc.). This went fairly well with a couple of exceptions.

Problem #1: I wasn’t possible to delete my virtual network. I got an error message saying it wasn’t possible because the subscription wasn’t active. I had to submit a support request, and a helpful technician re-enabled my subscription.

Lesson: Delete all resources before you cancel a subscription.

Problem #2: I still cannot delete the associated Active Directory. I get an error message saying

The following issue(s) prevent deletion of this directory:
· Directory has one or more Azure subscriptions.

But that is not true – I changed the subscription to be associated with a different directory. I hope Microsoft Support has a solution for this issue as well…

UPDATE: I discovered there was another subscription, owned by another user, associated with the domain. After changing that, I could delete the domain.

Posted in Azure | Tagged , , | Leave a comment

Starting Minecraft Server (or another program) remotely

I wanted a quick way of starting a Java program (Minecraft Server) from client computers, and the first solution that came to mind was PowerShell remoting. While this is great for background processes, Minecraft Server has a user interface, and so does the NoIP dynamic DNS client, which I am using. The solution was to create a task using task scheduler, and starting this task with PowerShell.

image

This action starts powershell.exe with a script as argument: C:\Mojang\Minecraft_server\StartMinecraftServer.ps1

The script looks like this:

$proc = Get-Process | where { $_.Name -eq "DUC40" }
if ($proc -eq $null) { Start-Process "C:\Program Files (x86)\No-IP\DUC40.exe" }

$proc = Get-Process | where { $_.Name -eq "java" -and $_.MainWindowTitle -eq "Minecraft server" }
if ($proc -eq $null) { Start-Process "C:\Program Files\Java\jre7\bin\java.exe" -ArgumentList "-Xms512m", "-Xmx1024m", "-jar", "minecraft_server.1.8.zip" }

As you see, it checks if “DUC40” (NoIP dynamic DNS client) is started, and if not, starts it. The same goes for Minecraft itself.

Then, I have another scheduled task, which runs at 00:00 each night and starts a script which stops these processes if they are running:

$proc = Get-Process | where { $_.Name -eq "java" -and $_.MainWindowTitle -eq "Minecraft server" }
if ($proc -ne $null) { Stop-Process $proc }
$proc = Get-Process | where { $_.Name -eq "DUC40" }
if ($proc -ne $null) { Stop-Process $proc }

On the client side, the following command needs to be run once from an elevated PowerShell console. (I would like to use the server name rather than the IP address, but it didn’t work for me.)

# The following is needed on the client if computers are not domain joined. It must be run from an elevated process.
Set-Item "wsman::localhost\Client\TrustedHosts" "htpc-dator"
Set-Item "wsman::localhost\Client\TrustedHosts" "192.168.0.101"
Restart-Service WinRM

Then, the following PowerShell script is used to start the task on the server:

# On the server, you must run Enable-PSRemoting.
# Invoke-Command -ComputerName "htpc-dator" -ScriptBlock { Start-ScheduledTask -TaskPath "\Henrik" -TaskName StartMinecraftServer }
Invoke-Command -ComputerName "192.168.0.101" -ScriptBlock { Start-ScheduledTask -TaskPath "\Henrik" -TaskName StartMinecraftServer } -Credential htpc-dator\htpc
Posted in Uncategorized | Tagged , | Leave a comment

Giving Permissions to BUILTIN\Users in SQL Server

I had a script for granting access to a SQL Server Express database to all users that looked like this:

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BUILTIN\Users')
BEGIN
	CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users]
	EXEC sp_addrolemember N'db_datareader', N'BUILTIN\Users'
	EXEC sp_addrolemember N'db_datawriter', N'BUILTIN\Users'
	GRANT EXECUTE TO [BUILTIN\Users]
END

The problem was that this only worked on English Windows. For example, on a Swedish Windows, this group is called “BUILTIN\Användare”. When looking at the sys.server_principals system view, I noticed that the SID is constant (0x01020000000000052000000021020000) but the name varies, so I could solve this with a dynamic SQL script:

DECLARE @builtinUsers nvarchar(128), @sqlString nvarchar(max)
SET @builtinUsers = (SELECT name FROM sys.server_principals where sid=0x01020000000000052000000021020000)
SET @sqlString = 'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @builtinUsers + ''') CREATE USER [' + @builtinUsers + '] FOR LOGIN [' + @builtinUsers + ']'
EXEC sp_executesql @sqlString
SET @sqlString = 'EXEC sp_addrolemember ''db_datareader'', ''' + @builtinUsers + ''''
EXEC sp_executesql @sqlString
SET @sqlString = 'EXEC sp_addrolemember ''db_datawriter'', ''' + @builtinUsers + ''''
EXEC sp_executesql @sqlString
SET @sqlString = 'GRANT EXECUTE TO [' + @builtinUsers + ']'
EXEC sp_executesql @sqlString
GO
Posted in SQL Server | Tagged , | Leave a comment

ReportViewer control in MVC

Continuing from my previous post, I wanted to make my reports available in my ASP.NET MVC site. I read about the ReportViewer control that it works only on webformst (ASPX pages), due to viewstate requirements. Luckily, webforms ges can be added to an MVC project. But when I started passing values to the report parameters, the page started some kind of inifite loop. That was because I had forgotten about webforms development and that you must often have a check for postback in code-behind. Like this:

    public partial class ReportWebForm : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ReportViewer1.ServerReport.ReportServerUrl = new Uri(Properties.Settings.Default.ReportServerUrl);
                var userUnit = Session[LoginModel.EnhetSessionKey] as Enhet;
                if (userUnit == null)
                {
                    ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter("Unit", "[Organisation].[Organisation-Enhet].[All]"));
                }
                else
                {
                    ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter("Unit", string.Format("[Organisation].[Organisation-Enhet].[Enhet].&[{0}]", userUnit.Id)));
                }
            }
        }
    }
Posted in Uncategorized | Leave a comment

Date Ranges in SSRS and SSAS

I have taken my first steps in Reporting Services (SSRS) land. The reports use data from a multidimensional cube in Analysis Services (SSAS). Of course, one requirement was to filter the reports on a date range. Should be simple, right? Well, at least not for me.

When designing the reports, I created a new dataset, used the MDX query designer and added my date dimension as a filter. I choose Range (Inclusive) and checked the two parameter checkboxes. Visual Studio automatically added these parameters both to the dataset and the report.

The problem was that the key of my dimension was a date column, but the tool added text parameters and choose available values from a query. That wasn’t what I wanted. I wanted a calendar drop down. So I changed the parameters to date and as available values I choose None. OK, now I had the calendar. But the report didn’t work. I got “The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated”.

It turned out I had to use a completely different expression for the dataset parameter expression. The expression should return a string that contains a dimension filter. I tried:

="[Datum].[Datum].&[" + Format((Parameters!StartDate.Value), "yyyy-MM-dd") + "]"

(My dimension is called “Datum” and so is my key attribute.)

It didn’t work. I got an error saying 2014-05-01 couldn’t be converted to the type date. Now I thought that SSAS was using US formatted dates internally, so I tried “MM-dd-yyyy”. Didn’t work either. After some Bing research I finally managed to find the solution: include time in the string as well:

="[Datum].[Datum].&[" + Format((Parameters!StartDate.Value), "yyyy-MM-ddTHH:mm:ss") + "]"

By the way, here is how to set the default for the report parameters to start and end of previous month:

=DateAdd(DateInterval.Month, -1, DateSerial(Year(Now()), Month(Now()), 1))
=DateAdd(DateInterval.Day, -1, DateSerial(Year(Now()), Month(Now()), 1))

Posted in SQL Server | Tagged , | Leave a comment