Azure accounts

Azure accounts and how to add/modify Azure administrator accounts

AzureHierarchy

Enterprise Admin

  • Root user of EA
  • Enterprise admin can create departments and assign department admins
  • EA admin can view consumption usage, billing charges across all departments, accounts and subscriptions
  • Ability to add or associate accounts to enrollment
  • Can view usage data across all accounts
  • Can view monitory commitment balance associated to enrollment
  • No limit on the number of enterprise administrators on an enrollment
  • There can be multiple EA administrators, including read-only administrators

How to add/change EA administrator

  • Login to ea.azure.com/enrollment details tab/add-modify administrator accounts

Department Admin

  • Can edit details on department like cost center and create EA accounts under department
  • EA admin can assign department admin permissions to view billing charges
  • There can be multiple department administrators, including read-only administrators

How to add/change department administrator

  • Login to ea.azure.com/department tab/add-modify department administrator

Account owner

  • Adds credit card info to subscription and change/update credit card info when it expires
  • Can create subscriptions and assign service admin for that subscription
  • Does not have root level access to resources in subscription
  • Can replace service admin and can also put himself as service admin if needed
  • Account admin can be granted by EA admin to view billing charges at account level.
  • Without this permission account admin see data usage but not how much money it costs.
  • In an EA enrollment environment, EA account owner creates a subscription, hence by default has two roles-EA account owner and subscription account owner.
  • Account owner by default will not have visibility to monetary commitment balance unless they also have enterprise administrator rights
  • Enterprise administrator can assign account owner rights to view the monetary commitment
  • There can be only one account owner for an account

How to add account owner

  • You add account owner when creating account.
  • Login to ea.azure.com/account tab/add account/add email address of account owner.

How to modify account owner

  • Login to ea.azure.com/account tab/select existing account/hover mouse to the right end and you see an icon to change account owner.

Service admin

  • Root user at subscription level but does not have access to billing information
  • One service administrator per subscription
  • Cannot perform any actions on EA portal
  • Can replace himself/herself as service admin
  • Can add co-admins to a subscription
  • Multiple co-administrators can be added per subscription
  • Co-admins have all powers as service admins but cannot replace original service admin
  • Service administrators and co-administrators, use manage.windowsazure.com to work on the subscription and resources

How to change service administrator

  • You assign service administrator when you create subscription.
  • To change service administrator, login to account.windowsazure.com/click on account tab on the top/click on any existing subscription/click on edit subscription details/type new name and/or service administrator email address

How to add/modify co-administrator

  • To add/modify co-administrators, login to manage.windowsazure.com/settings/administrators/add or edit buttons at the bottom of the page.

 

Availability groups and SQL Authentication

Login account with SQL authentication created on SQL database uses internal SID.

If you have a requirement to use an account with SQL authentication when connecting to a database that is member of availability group, you will end up in a situation where you cannot connect to the database on replicated database server. This happens because SID of SQL account on primary and replicated database server are different.

You can match the SID using below mentioned procedure.

  • Ensure SQL authentication is enabled on both the database servers.
    Create SQL authentication login id on the first server. account name is webadmin in below example.
  • Execute below script to list the details of all SQLUse master
    Go
    Select sid, name, dbname from syslogins
  • Copy the sid from the SQL account you want
  • Go to second DB server and execute below script to create SQL authentication login id with same sidCreate login webadmin with password = ‘required password’, sid = sid from previous step

You can execute below mentioned script on primary and replicated database server to check if the SID of account (webadmin in above example) is same on both the servers.

Use master
Go
Select sid, name, dbname from syslogins
Login to both servers with same sql auth and see if you can login
Assign permissions on the primary copy of the database, security permissions are replicated to the secondary copy

Manage Azure AD connect

Azure AD connect is the successor to DirSync (not supported from Apr 2017)
Azure AD connect sync will synchronize changes happening in your on-premises directory using a scheduler.
There are two scheduler processes
Password sync
Object/attribute sync and maintenance tasks

Check current configuration

PS C:\Windows\system32> Get-ADSyncScheduler

AllowedSyncCycleInterval            : 00:30:00
CurrentlyEffectiveSyncCycleInterval : 00:30:00
CustomizedSyncCycleInterval         :
NextSyncCyclePolicyType             : Delta
NextSyncCycleStartTimeInUTC         : 7/25/2016 9:06:00 PM
PurgeRunHistoryInterval             : 7.00:00:00
SyncCycleEnabled                    : True
MaintenanceEnabled                  : True
StagingModeEnabled                  : False

If you receive error that the cmdlet is not available, execute command import-module adsync

AllowedSyncCycleInterval-most frequently Azure AD will allow synchronizations
CurrentEffectiveSyncCycleInterval-schedule currently in effect
CustomizedSyncCycleInterval-If you want scheduler to run at a different frequency, you set this value
NextSyncCyclePolicyType-Delta/Initial
NextSyncCycleStartTimeInUTC -Next time scheduler will start
PurgeRunHistoryInterval-time operational logs should be retained, default 7 days
SyncCycleEnabled-indicates if scheduler is running import, sync and export processes as part of this operation
MaintenanceEnabled-indicates if maintenance process is enabled
StagingModeEnabled-shows if staging mode is enabled
Change some of these values using command set-ADSyncScheduler

Start the scheduler

Open CMD as administrator

Change directory to C:\Program Files\Microsoft Azure AD Sync\Bin\ADSync>

Import-Module ADSync.psd1

Delta sync (delta imports all connectors, delta sync all connectors, export on all connectors)

Start-ADSyncSyncCycle -PolicyType Delta

Full sync (full import all connectors, full sync all connectors, export all connectors)

Start-ADSyncSyncCycle -PolicyType Initial

Stop AD Sync

Note: stopping the current cycle is not harmful and any changes still not processed will be processed with next run

Stop-ADSyncSyncCycle

Check status of synchronization service-GUI

Start application with name synchronization service and click on operations tab

Update soon to expire ADFS certificates

Token signing and decryption certificates are very important components and expire once in a while. If these certificates are not kept up to date, you will get into issues where federated applications will not perform sign-on. By default in ADFS these certificates are self-signed with expiration of 365 days.

If auto-rollover is enabled, these certificates renew 20 days before the expiration date. Newly renewed certificate will be made as secondary. By default secondary certificate will be promoted as primary 5 days before expiry.

If you cannot wait until ADFS renews certificate automatically, follow below process

Determine if auto roll over is set to true or false

Open PowerShell as Administrator on the Primary ADFS server.

Get-ADFSProperties | select AutoCertificateRollover

Result:
PS C:\Windows\system32> get-adfsproperties | select AutoCertificateRollover

AutoCertificateRollover
———————–
True

Set auto rollover to false

You cannot modify certificate properties when auto rollover is set to true.

PS C:\Windows\system32> set-adfsproperties -autocertificaterollover $false

Renew certificates manually

PS C:\Windows\system32> Update-AdfsCertificate -CertificateType Token-Signing
PS C:\Windows\system32> Update-AdfsCertificate -CertificateType Token-decrypting

Had to use -urgent option at the end of above two commands. This did not generate any secondary certs and the primary certs were updated with new cert.

Change Secondary Certs to Primary

Open ADFS console, Refresh ADFS console/service/certificates
Right click secondary certs of certificate signing and decrypting and select the option “set as primary”

Delete secondary certs

You can right click old secondary certs and delete (you cannot delete certificates when auto rollover is set to true)

Set auto rollover to true

PS C:\Windows\system32> set-adfsproperties -autocertificaterollover $true

Update Azure AD

Note: I had to use onmicrosoft account to complete below task

PS C:\Windows\system32> $msolcred = get-credential

Type credentials of login id with global admin permissions on Azure AD/Office365

PS C:\Windows\system32> Connect-MsolService -Credential $msolcred

PS C:\Windows\system32> get-msolfederationproperty -DomainName acme.com

Source                          : ADFS Server
ActiveClientSignInUrl           : https://sts.acme.com/adfs/services/trust/
2005/usernamemixed
FederationServiceDisplayName    : Acme
FederationServiceIdentifier     : http://sts.acme.com/adfs/services/trust
FederationMetadataUrl           : https://sts.acme.com/adfs/services/trust/
mex
PassiveClientSignInUrl          : https://sts.acme.com/adfs/ls/
PassiveClientSignOutUrl         : https://sts.acme.com/adfs/ls/
TokenSigningCertificate         : [Subject]
CN=ADFS Signing – sts.acme.com

[Issuer]
CN=ADFS Signing – sts.acme.com

[Serial Number]
2FD75DF74922F0A54F96A55340079B25

[Not Before]
4/18/2015 10:06:32 AM

[Not After]
4/17/2016 10:06:32 AM

[Thumbprint]
8218CCC77R8BB2637AF25D9F5909A1D956093ADA

NextTokenSigningCertificate     :
PreferredAuthenticationProtocol :

Source                          : Microsoft Office 365
ActiveClientSignInUrl           : https://sts.acme.com/adfs/services/trust/
2005/usernamemixed
FederationServiceDisplayName    : Acme
FederationServiceIdentifier     : http://sts.acme.com/adfs/services/trust
FederationMetadataUrl           : https://sts.acme.com/adfs/services/trust/
mex
PassiveClientSignInUrl          : https://sts.acme.com/adfs/ls/
PassiveClientSignOutUrl         : https://sts.acme.com/adfs/ls/
TokenSigningCertificate         : [Subject]
CN=ADFS Signing – sts.acme.com

[Issuer]
CN=ADFS Signing – sts.acme.com

[Serial Number]
2FD75DF74922F0A54F96A55340079B25

[Not Before]
4/18/2015 10:06:32 AM

[Not After]
4/17/2016 10:06:32 AM

[Thumbprint]
8218CCC77R8BB2637AF25D9F5909A1D956093ADA

NextTokenSigningCertificate     : [Subject]
CN=ADFS Signing – sts.acme.com

[Issuer]
CN=ADFS Signing – sts.acme.com

[Serial Number]
4514AB944F0B43964EE96F652915490A

[Not Before]
5/8/2014 9:56:57 AM

[Not After]
5/8/2015 9:56:57 AM

[Thumbprint]
2BD9ACD5568Z0D896144F68BD1AB9A8A98D26CE2

PreferredAuthenticationProtocol : WsFed

Note: Command output is divided into two parts. First section has ADFS server as the source and represents the configuration that is stored in local federation service. Second section has source as Office 365 which represents the configuration that is stored in Azure AD. Compare the values of TokenSigningCertificate attribute in the two sections.

Update certificate in Azure AD

Update-MsolFederatedDomain -domainName acme.com

Reference:

https://technet.microsoft.com/en-us/library/dn781426.aspx

Azure management portals and permissions

Classic azure management portal (https://manage.windowsazure.com)

  • Classic management portal does not use RBAC
  • You can control permissions separately in each console
  • one service administrator per subscription, additional co-administrators can be added.
  • To grant administrative access to an account in Azure management portal, you add the user’s account as a co-administrator to the subscription.
  • Co-administrator has all the permissions of the original owner at subscription but it does not allow the user to change the service administrator or add and remove other co-administrators

New management portal (https://portal.azure.com)

  • New management portal uses RBAC
  • You can control permissions separately in each console
  • Subscription admins defined in ea.azure.com and co-admins defined in classic management portal are assigned inherited owners permissions in portal.azure.com
  • Inherited permissions can only be removed at the root level where they are assigned.
  • You can also assign additional permissions at the resource group level.

Enterprise management portal (https://ea.azure.com)

Modify enrollment details tab

  • Create/modify administrator. Administrator can be with full or read only permissions to ea.azure.com
  • Set Auth Level-can be set to Work or School account only/Work or School account cross tenant/Microsoft account only/Mixed account
  • Enable/Disable- Azure Marketplace, DA view charges, AO view charges
  • Add notification contact (in case you want to notify billing details without providing read-only/admin permissions to console)

Department tab

  • Add Department
  • Modify department-change department name, set cost center, spending quota
  • Add department administrator. You can create read-only department administrator

Account tab

  • Add account (department, authentication type, account name, email address/confirm email address of account owner are mandatory fields, cost center is optional)
  • Modify existing account- you can modify account name, select different department, choose if the account is dev/test and change cost center)
  • You can change account owner by selecting account and hover mouse to the right side of the account under cost center, a new icon for change owner will appear.
  • You can transfer subscriptions from existing account to another account. Select account, hover mouse to the right side of the account under cost center, a new icon for transfer subscriptions will appear.

Subscription tab

  • View subscription details (name, subscription GUID, start date, status)
  • Add cost center to subscription
  • Click on view managed subscriptions link to view the mapping between subscription and the account
  • Even if have administrator permissions on ea.azure.com, you can only see specific subscriptions for which you are service administrator.

Account management portal (https://account.windowsazure.com)

  • You add new subscription from account.windowsazure.com
  • Once subscription is created, I will take up to 10-15 minutes for the subscription processing to be completed. Once processing is completed it will appear in account.windowsazure.com and ea.azure.com
  • Set service administrator for a subscription, by going to account/click on the subscription/click on edit subscription details/type new name and/or service administrator email address
  • Set subscription address, by going to account/click on the subscription/click on change subscription address
  • https://account.windowsazure.com used for tracking usage and manage subscription(s).
  • Account owner by default will not have visibility of monetary commitment balance unless they also have enterprise administrator rights. Enterprise administrator can choose to grant the account owner the rights to view monetary commitment.
  • Limit of one account owner for each account.
  • Account owner can add subscriptions, add/update service administrator and co-administrators for an individual subscription.
  • To cancel the subscription, click on the subscription, click on cancel subscription.

 

Install and Login to Azure using PS

One time steps

You have to perform below mentioned steps once to install Azure modules on your computer.

Launch PS as administrator

Check PS modules path.

Note: Azure modules once installed will be added under C:\Program Files\WindowsPowerShell\Modules

PS C:\WINDOWS\system32> $env:PSModulePath
C:\Users\<Your Login ID>\Documents\WindowsPowerShell\Modules;C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system3
\WindowsPowerShell\v1.0\Modules

Installing Azure PowerShell from PowerShell gallery

Note: Install-Module AzureRM installs a rollup module for Azure resource manager cmdlets. Install-Module Azure installs service management module

PS C:\WINDOWS\system32> Install-Module AzureRM

NuGet provider is required to continue
PowerShellGet requires NuGet provider version ‘2.8.5.201’ or newer to interact with NuGet-based repositories. The
NuGet provider must be available in ‘C:\Program Files\PackageManagement\ProviderAssemblies’ or
‘C:\Users\<Your Login ID>\AppData\Local\PackageManagement\ProviderAssemblies’. You can also install the NuGet provider by
running ‘Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force’. Do you want PowerShellGet to install
and import the NuGet provider now?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is “Y”): y

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its
InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from
‘PSGallery’?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is “N”): y

PS C:\WINDOWS\system32> Install-Module azure

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its
InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from
‘PSGallery’?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is “N”): y
PS C:\WINDOWS\system32> $env:PSModulePath
C:\Users\<Your Login ID>\Documents\WindowsPowerShell\Modules;C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system3
\WindowsPowerShell\v1.0\Modules

Login to Azure PS

If you are using Azure resource manager (new portal)

PS C:\WINDOWS\system32> Login-AzureRmAccount

You will be presented with a dialog box where you have to type your Azure login id and password.

Environment           : AzureCloud
Account               : loginid@domain.com
TenantId              : 17f6ds12-9322-49ea-bf4a-77aa2d56384b
SubscriptionId        : 4f550d25-217f-478e-a963-17906e90f949
SubscriptionName      : AzureSubscription
CurrentStorageAccount :

If you are using Azure classic portal (legacy)
PS C:\WINDOWS\system32> Add-AzureAccount

Id                             Type Subscriptions                        Tenants
—                             —- ————-                        ——-
loginid@domain.com User 4f530321-219f-471e-a933-1d844910f949 {17f6a671-9522-4aea-bf4a-77112d54384b}

You will be presented with a dialog box where you have to type your Azure login id and password.

Type below command to verify the Azure subscription

PS C:\WINDOWS\system32> Get-AzureSubscription

SubscriptionId            : 45671234-1234-123e-a963-123456789123
SubscriptionName          : Azure
Environment               : AzureCloud
DefaultAccount            : userid@domain.com
IsDefault                 : True
IsCurrent                 : True
TenantId                  : 17f6a671-9522-4aea-bf4a-77112d54384b

 

Link1 (Azure PS install and configure): https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

Link2 (Azure PS reference): https://msdn.microsoft.com/library/azure/jj554330.aspx

 

Tracing SQL server deadlock

Enable deadlock logging

DBCC TRACEON (1222, -1) (This enables the trace flag globally)

Get the -T1222 output from the SQL errorlog after the deadlock has occurred

Disable deadlock logging

DBCC TRACEOFF (1222, -1)

Note: More information available at URL

Permissions to view execution report in SSIS

If you have to provide permissions to users to view SQL SSIS executions report without providing ssis_admin role, try below tip. This tip worked for me and did not create any issues until now.

Alter below mentioned two views in SSIS database.

  1. catalog.executions
  2. catalog.event_messages
  3. catalog.execution_parameter_values

Comment out below mentioned script and execute the alter script.

–WHERE      opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
—          OR (IS_MEMBER(‘ssis_admin’) = 1)
—          OR (IS_SRVROLEMEMBER(‘sysadmin’) = 1)

Host to client URL redirection

Using host to client URL redirection, you can force published application that opens a web page to launch from user workstation instead of Citrix XenApp server.

  1. Create a machine catalog with required number of Windows server 2012 R2 servers.
  2. Create a delivery group with servers from the machine group created in step1.
  3. Create a new policy with setting ICA/Host to client redirection – Enabled. Assign the policy to delivery group created in step2.
  4. Publish application(s) in delivery group created in step2, with Path to the executable files as “C:\Program Files (x86)\Citrix\System32\iexplore.exe”, Command line argument as “URL of the website”, working directory as “C:\Program Files (x86)\Citrix\System32”, set other properties of the application as per your requirement.

Note: Used Citrix XenApp 7.9 installed on Windows Server 2012 R2 operating system.

Enable PS Remote

Ultimate goal of PS remote is to be able to execute a command on a remote computer. To achieve this goal at a high level, you need to enable windows remote management and modify windows firewall.

ADMX

Ensure that you have copied group policy admx files (WindowsRemoteManagement.admx) to your active directory domain controllers. If you do not have the required admx files on domain controllers, you will not see corresponding settings in your group policy.

Group Policy

You need to enable windows remote management using group policy. This can be achieved by creating a new or modify existing group policy and linking it to organization unit that contains the computers that you want to manage remotely.

Edit GPO using group policy management console

Go to Computer Configuration/Policies/Administrative Templates/Windows Components/Windows Remote Management (winrm)/winrm service/Enable “Allow Remote Server Management through WinRM and type * for IP4

Go to computer configuration/policies/administrative templates/Windows Firewall/Domain Profile/Enable “Windows Firewall: Allow inbound remote administration exception” and set the IP addresses from where you will be connecting remotely to the servers where remote administration using PS is required. Separate IPs by comma and you can use IP subnets in the format 10.1.2.0/24

Apply GPO Settings

For immediately applying changed group policy settings, login to server/computer to which you have applied GPO policy settings to execute commands remotely and execute command “gpupdate /force” from cmd.exe

Testing

open PS as administrator on a computer with IP address white listed in above step

$service = {get-process | sort cpu -desc | select -first

Invoke-Command xaso1 $service

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id  SI ProcessName
——-  ——    —–      —– —–   ——     —  — ———–
697       0      112        256     3    94.36      4     System
1297      42    21440      36340   148    90.06    740     svchost
340      20     7624      15748    86    56.91   2248     vmtoolsd
468      18    13620      16900    57    15.00    696     svchost
231      10     3316       6700    23     9.55    504     services

In the above command xas01 is the name of the remove computer where you want to execute the command.