Deleting a large list from SharePoint using PowerShell

I recently ran into an issue trying to delete a list over 5,000 items from SharePoint. I tried using Metalogix Content Matrix to delete the list/site, but they all were bound by the threshold. I realized I had to use PowerShell, but research lead to even this having issues deleting the list. The solution was batches of 1,000 items. It takes a few hours to remove 25,000 items, but I was able to delete the list once the items were removed.

I received this error prior to deleting the items in the list using a script via modern UI Site Contents menu:

“My List- 24453 items- We’re sorry, we had some trouble removing this. You can try again from the settings page.”

Similar error on the list settings page:

“The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”

This script on MSDN was able to remove the items so I could then delete the list in the browser: https://blogs.msdn.microsoft.com/ahmedamin/2017/08/03/bulk-delete-sharepoint-items-in-a-large-list-using-powershell/

Here is the script as well:

[code language=”powershell”]

Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue
$web = get-spweb “https://intranet/sites/home”
$list = $web.lists[“My List Title Here”]
$query = New-Object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = “Scope=’Recursive'”
$query.RowLimit = 1000
$query.ViewFields = “”
$query.ViewFieldsOnly = $true
do
{
$listItems = $list.GetItems($query)
$query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
foreach($item in $listItems)
{
Write-Host “Deleting Item – $($item.Id)”
$list.GetItemById($item.Id).delete()
}
}
while ($query.ListItemCollectionPosition -ne $null)

[/code]

This above script was able to remove the items, then I could delete the list.

All done!

SharePoint Online Azure AD B2B – Custom email invites for users using PowerShell

Big changes have been happening with External User sharing for SharePoint Online over the past few months now that Azure Active Directory Business to Business (Azure AD B2B) is now generally available. Azure AD B2B allows you to share Office 365 content and line of business applications to users outside your organization. If you are new to Azure AD B2B, watch this great intro on this page: https://docs.microsoft.com/en-us/azure/active-directory/active-directory-b2b-what-is-azure-ad-b2b

The drawback however is the user onboarding process. Security groups, permissions, user onboarding, administration, the cumbersome 4-6 step user invite process to accept and signup/sign-in to your site, etc. make it difficult to see a good OOTB solution. However, after reading most of the Azure AD B2B articles and working with Microsoft support, I have found an improved (not perfect) process for inviting users and allowing them to sign up for a Microsoft account.

This script will:

Import your users from a CSV file
Add them to an Office 365 security group (that is permissioned to your app or SharePoint Online site)
Send them a custom email message with the invite link that fully supports HTML

The goal of this script is to make it easier on the user so they see a custom email and not a generic Office 365 Azure AD email (after all, the users have no clue what Azure AD is)

Administration- CSV import file

When inviting the users, create a CSV file in the following format:

InvitedUserEmailAddress	Name
[email protected]	Eric Schrader (Company Optional)
[email protected]	Eric Schrader
Etc.

Above, the first row is the CSV headers which we will reference in PowerShell during the import. The following rows are the actual users valid email where the invite will be sent (but we will intercept this email, so this will just be used as the users identity so we know how to email them in the future). Long story, but the invited email doesn’t have to match the Personal/Organizational account they sign in as, but for this blog post they will match so we don’t get confused with Authorization vs Authentication.

Administration- Create a Security group and set SharePoint Online permissions

The next step is so you don’t have to go into SharePoint Online sites and add each users’ permissions manually.

Similar to AD groups, create an Office 365 Security Group in the O365 Users > Groups admin center.
Add the AD group to SharePoint and set any read/write permissions this group will have (so maybe your group is called External Users and they are read only to the root site, then another called External Members with contribute permissions, etc.

Once we import the CSV of users, we will have to add the new users to the O365 groups accordingly.

PowerShell time!

Be sure to install the Azure AD PS module – https://www.powershellgallery.com/packages/AzureADPreview Otherwise, you cannot run these commands.

Get the ID of your security group

Get the security group name you want the external users to automatically be added to:

get-azureadgroup | where-object {$_.DisplayName -ilike “External Users”}

(replace External Users with the name of your Security group you want the users to be added to)

Then copy the Object ID for the group and save this for our script.

The whole enchilada

#1.) Install Azure AD PS module – https://www.powershellgallery.com/packages/AzureADPreview
#Install-Module -Name AzureADPreview

#2.) provide O365 tenant admin cred

$cred = Get-Credential

Connect-AzureAD -Credential $cred

#2.a) second cred for O365 email account (merge var with above if for non-demo O365 tenant)

$adminemailcred = get-credential

#2.b) External User Security Group ID

#get-azureadgroup | where-object {$_.DisplayName -ilike “External Users”}

$groupID = “c9a04711-e307-4370-af42-f48db58f80c5”

#3.) import CSV, update url and csv location below.

$invitations = import-csv C:\azure_ad_b2b.csv

foreach ($email in $invitations) {

#loop over each user in the CSV and create an invite for that user but does not email the user

$result= New-AzureADMSInvitation -InvitedUserEmailAddress $email.InvitedUserEmailAddress -InvitedUserDisplayName $email.Name -InviteRedirectUrl https://mycompanyhere.sharepoint.com/sites/extranet -InvitedUserMessageInfo $messageInfo -SendInvitationMessage $false

$inviteurl = $result.InviteRedeemUrl

$userid = $result.InvitedUser.Id

#automatically add the new user to your Security Group

Add-AzureADGroupMember -ObjectId $groupID -RefObjectId $userid

#send the user a custom email from your Office 365 tenant. Supports HTML.

#Configuration variables for Email#Configuration variables for Email $EmailTo = $result.InvitedUserEmailAddress
$EmailFrom = “Eric Schrader <[email protected]>”
$EmailSubject = “INVITE: Eschrader Extranet”
$SupportEmail = “[email protected]”
$SmtpServer = “smtp.office365.com”
$InviteUrl = $inviteurl
$DisplayName = $result.Name

#HTML Template $EmailBody = @”
<header>
<img src=”https://eschrader.com/wp-content/uploads/2017/05/sileo-logo.png” />
</header>

Congrats $DisplayName,

You have been invited to access the Eschrader extranet.

</p> <p> If you need further assistance, please contact us <a href=”mailto:$SupportEmail”>$SupportEmail</a> </p>
</div>
“@
Send-MailMessage -To $EmailTo -from $EmailFrom -Subject $EmailSubject -Body $EmailBody -BodyAsHtml -smtpserver $SmtpServer -usessl -Credential $adminemailcred -Port 587

}

Note, above HTML was stripped out of my email template. Here is a PasteBin: https://pastebin.com/e1ziPq9U

In the above script,

Note that #2 and #2a can be combined to just prompt you once. I had to use two because I have two O365 tenants, one is a demo tenant that does not support email and the other is my personal O365 tenant I send the email from.
YOU WILL NEED TO REPLACE A LOT OF THE HARD-CODED VARIABLES ABOVE IN THE SCRIPT. LOOK CLOSELY.
Watch out for the fancy quotes

Here is an example email I sent myself to test (I put the access ID for my demo tenant and accepted it, so don’t bother typing it out haha)

If you want to embed images in the HTML message, I believe send-mailmessage has an “attachments” and “-inline attachments” parameter as outlined in this script, which can easily be adapted to your email template https://gallery.technet.microsoft.com/scriptcenter/Send-MailMessage-3a920a6d

You can also add other columns to the CSV file for send-mailmessage such as company, group ID, etc. and really build this script out.

There are lots of hard coded variables, as this is a rough script. Feel free to clean it up!

Links:

Azure AD User/Group PowerShell commands: https://docs.microsoft.com/en-us/powershell/module/Azuread/?view=azureadps-2.0

Send email from Office 365 in PowerShell: https://absolute-sharepoint.com/2016/03/send-email-from-powershell-in-office-365.html

Azure AD B2B invite process overview (OOTB, not PowerShell): https://docs.microsoft.com/en-us/azure/active-directory/active-directory-b2b-invitation-email

A good import script using a different CSV format, a good read: http://artokai.net/2016/SPOnlineAndAzureB2B/

Automating Azure IaaS SharePoint VM Provisioning via PowerShell Remoting

I have been searching for a way to rapidly create standalone Developer SharePoint 2013 standalone VM’s joined to a central domain for our in-house developers. Our team has created 60+ SharePoint VMs on Azure and continue to create about 10 per month. We are beginning to treat our VMs needing hours of repair like cattle, and no longer like the family dog if they have issues or “get terminally ill”, VM’s are replaced with a brand new shiny cow within 4 hoursJ

The process of manually creating VMs was not fun, taking over a day per VM in the beginning on average. I did not want to use Sysprep, since I would have to maintain multiple VM Images at a single point in time. For some, this might be the best way to go.

My solution was to create a lightweight PowerShell set of scripts that create the VM in Azure, install the applications, and keep everything consistent. I can create 4+ VMs at one time, all under ~4 hours total. This is a process that scales somewhat, to meet our needs. Perfect.

Alternatives to this manual PowerShell process I went through:

Sysprep would save a ton of steps, but is not as easy to update OS patches, etc. as newer software comes out
AzureRM– Azure Resource Manager is a lot easier. However, these topologies seem somewhat isolated and all of our existing VMs and didn’t work well with what we had in place for our network/VPN, etc.
Azure SharePoint QuickStart templates/images- These were preconfigured and had various OS settings changed. Similar to the above Azure RM solution issues we ran into.

Assumptions:

You have an Azure subscription all set up, with a virtual network/DNS/Subnet (we have a site-to-site VPN)
You have domain controller with all of the SharePoint service accounts created for Least Privileges security installation
You have installed the latest version of Azure PowerShell installed, rebooted after installing it, and performed the Add-AzureAccount command
You have used AutoSPInstaller before

What you need:

The following information from Azure
- Subscription ID
- Virtual Network info
  - Network Name
  - Subnet Name
- Resource Group
The following pre-existing VMs:
- DC
  - Service Accounts
- Fileshare VM
  - All of the necessary ISO’s and EXE’s
    - Installer files:
      - 7zipInstall.msi
      - ccleaner.exe
      - fiddler4setup.exe
      - Copy-Item ‘C:\Fileshare\applications\Firefox Setup Stub 36.0.4.exe’
      - iview438_setup.exe
      - LINQPad4Setup.exe
      - npp.6.7.5.Installer.exe
      - paint.net.4.0.5.install.exe
      - PowerGUI.3.8.0.129.msi
      - cutepdf-writer.exe
      - CKS.Dev11.vsix
      - codecompare.exe
  - Stand Alone EXE’s
    - ULSViewer.zip
    - U2U.SharePoint.CQB2010.zip
  - Applications (Extracted into their own folder with configuration.ini files)
    - CamlDesigner2013
    - Visual Studio 2012
    - Visual Studio 2015
    - SharePoint Designer 2013
    - en_sql_server_2014_enterprise_edition_with_service_pack_1_x64_dvd_6669618
    - AutoSPInstaller for dev
    - sql2014config file for dev
    - en_sharepoint_server_2013_with_sp1_x64_dvd_3823428
    - SharePoint 2013 June 2015 CU (note, if you download this from the internet, uncheck the security property so you don’t get prompted during the AutoSPInstaller process for UAC- right click all 3 CU files and go to Security and unblock, you only have to do this one time on the fileshare.)
Silent install for software (one-time prep, then save the folder on the Fileshare VM)
- SharePoint Designer- http://www.itninja.com/software/microsoft/sharepoint-designer/14-597
- SQL Server- https://technet.microsoft.com/en-us/library/dd239405(v=sql.110).aspx
- Visual Studio- https://msdn.microsoft.com/en-us/library/ee225237.aspx
- AutoSPInstaller – https://autospinstaller.codeplex.com/

Configure PowerShell variables for the new standalone developer SharePoint VM

#VM Name will be ASP13D08

#IP will be 192.168.1.87

#Cloud service Company-Redondo-D08 (each developer has their own cloud service so they can power on VMs without having to wait for the other developers to start at the same time)

#service accounts

#Single SharePoint 2013 developer VM

[code language=”powershell”]$varVMLocation = "A"
$varVMServerType = "SP"
$varVMSPVersion = "13"
$varVMType = "D"
$varVMIntanceNum = "08"
$varVMReduxSuffix = "" # I sometimes append a version letter to the end of the developers VM, if they are getting an additional VM of the same role.
$spsetupname = "svc_spsetup"
$spsetuppasstext = "passw0rdspsetup"
$users = @("svc_spsetup", "svc_spfarm", "eric.schrader", "dev1", "etc");
$varVMStaticIP = "192.168.1.87"
$varStorageAccount = "Company" + $varVMLocation + $varVMType + $varVMIntanceNum #unique
$varStorageAccount = $varStorageAccount.ToLower()
$service = "Company-Redondo-" + $varVMType + $varVMIntanceNum
$instancesize = "Basic_A4"
$subscriptionid = "12345678-12345-123456"
$subscriptionName = "Microsoft Azure Enterprise"
$imageFamily = "Windows Server 2012 R2 Datacenter" #Azure VM Image name, the latest will be used below.
$localadminname = "company.admin" #cant be "administrator", etc.
$localadminPassword = "passw0rdlocaladmin"
$joindomain = "Domain.local"
$domainname = "Domain"
$machineOU = "OU=Azure,OU=Development,OU=Servers,OU=Seattle,DC=Company,DC=local"
$timezone = "Pacific Standard Time"
$domainusername = "svc_spsetup"
$domainpassword = "passw0rdspsetup"
$datadiskGB = 127
$datadiskLUN = 0
$datadiskCACHE = "None"
$vmsubnet = "Subnet-1"
$vmaffinitygroup = "VPN-Linked"[/code]

Create the Azure storage account if it doesn’t exist, the set it as the default for PowerShell

#Get-AzureStorageAccount | ft

#Change varStorageAccount to lowercase

[code language=”powershell”]$lowerStorageAccount = $varStorageAccount.ToLower()

Try{

get-azurestorageaccount -storageaccountname $varStorageAccount -ErrorAction Stop

#if this fails to get it, it will create it below. Need above error action

}

Catch {

#you got an error trying to get it, so create it.

Write-output "creating storage account $varStorageAccount"

New-AzureStorageAccount -StorageAccountName $lowerStorageAccount -Label $lowerStorageAccount -AffinityGroup $vmaffinitygroup

}[/code]

#now that it exists, set it as default.

[code language=”powershell”]Set-AzureSubscription -CurrentStorageAccountName $varStorageAccount -SubscriptionId $subscriptionid

Select-AzureSubscription -SubscriptionId $subscriptionid -Current[/code]

Create the VM using above variables

#try to fix the DNS error in the Comapny DC, WARNING: The specified DNS name is already taken.

#New-AzureService -Label $service -Description $service -AffinityGroup $vmaffinitygroup -ServiceName $service

[code language=”powershell”]</span>New-AzureVMConfig -Name $name -InstanceSize $instancesize -ImageName $image | Add-AzureProvisioningConfig -AdminUserName $localadminname -EnableWinRMHttp -TimeZone $timezone -DisableAutomaticUpdates –Password $localadminPassword -WindowsDomain -JoinDomain $joindomain -Domain $domainname -DomainUserName $domainusername -DomainPassword $domainpassword -MachineObjectOU $machineOU | Add-AzureDataDisk -CreateNew -DiskSizeInGB $datadiskGB -DiskLabel $datadiskname -LUN $datadiskLUN -HostCaching $datadiskCACHE | Set-AzureSubnet –SubnetNames $vmsubnet | Set-AzureStaticVNetIP -IPAddress $varVMStaticIP| New-AzureVM –ServiceName $service -AffinityGroup $vmaffinitygroup[/code]

Configures Secure Remote PowerShell Access to Windows Azure Virtual Machines

Download PS1 file from this blog post to your local computer with Azure PowerShell. https://gallery.technet.microsoft.com/scriptcenter/Configures-Secure-Remote-b137f2fe

#CD to location in PowerShell, for example, your desktop:

[code language=”powershell”]Cd C:\users\eric.schrader\desktop[/code]

#Create WInRM Cert to new VM

[code language=”powershell”].\InstallWinRMCertAzureVM.ps1 -SubscriptionName $subscriptionname -ServiceName $service -Name $name[/code]

Connect to remote session

#Connect via remote powershell as local azure.admin
#uses variables from when the VM was created above

[code language=”powershell”]$passwordsec = convertto-securestring $localadminPassword -asplaintext -force

$user = $name +"\"+ $localadminname

$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$passwordsec

$uri = Get-AzureWinRMUri -ServiceName $service -Name $name

Enter-PSSession -ConnectionUri $uri -Credential $cred

$env:computername[/code]

#Run variables Again!!!

# IMPORTANT, COPY AND PASTE THE ABOVE VARIABLES SECTION IN AGAIN. This is a new remote session to the new Azure VM.

[code language=”powershell”]$varVMLocation = "A"

$varVMServerType = "SP"

$varVMSPVersion = "13"

$varVMType = "D"

$varVMIntanceNum = "08"

$varVMReduxSuffix = "" # I sometimes append a version letter to the end of the developers VM, if they are getting an additional VM of the same role.

$spsetupname = "svc_spsetup"

$spsetuppasstext = "passw0rdspsetup"

$users = @("svc_spsetup", "svc_spfarm", "eric.schrader", "dev1", "etc");

$varVMStaticIP = "192.168.1.87"

$varStorageAccount = "Company" + $varVMLocation + $varVMType + $varVMIntanceNum #unique

$varStorageAccount = $varStorageAccount.ToLower()

$service = "Company-Redondo-" + $varVMType + $varVMIntanceNum

$instancesize = "Basic_A4"

$subscriptionid = "12345678-12345-123456"

$subscriptionName = "Microsoft Azure Enterprise"

$imageFamily = "Windows Server 2012 R2 Datacenter" #Azure VM Image name, the latest will be used below.

$localadminname = "company.admin" #cant be "administrator", etc.

$localadminPassword = "passw0rdlocaladmin"

$joindomain = "Domain.local"

$domainname = "Domain"

$machineOU = "OU=Azure,OU=Development,OU=Servers,OU=Seattle,DC=Company,DC=local"

$timezone = "Pacific Standard Time"

$domainusername = "svc_spsetup"

$domainpassword = "passw0rdspsetup"

$datadiskGB = 127

$datadiskLUN = 0

$datadiskCACHE = "None"

$vmsubnet = "Subnet-1"

$vmaffinitygroup = "VPN-Linked"[/code]

Set proper storage account in remote session

# Now that you set the variables, set the storage account for the remote session

[code language=”powershell”]Set-AzureSubscription -CurrentStorageAccountName $varStorageAccount -SubscriptionId $subscriptionid

Select-AzureSubscription -SubscriptionId $subscriptionid -Current[/code]

Format F drive for SharePoint/SQL, permission service accounts

#Format F drive

[code language=”powershell”]$labels = @("DATA1","DATA2")

Write-Host "Initializing and formatting raw disks"

$disks = Get-Disk | Where partitionstyle -eq ‘raw’ | sort number

## start at F: because sometimes E: shows up as a CD drive in Azure

$letters = 70..89 | ForEach-Object { ([char]$_) }

$count = 0

foreach($d in $disks) {

$driveLetter = $letters[$count].ToString()

$d |

Initialize-Disk -PartitionStyle MBR -PassThru |

New-Partition -UseMaximumSize -DriveLetter $driveLetter |

Format-Volume -FileSystem NTFS -NewFileSystemLabel $labels[$count] `

-Confirm:$false -Force

$count++

}

GET-WMIOBJECT –query "SELECT * from win32_logicaldisk where DriveType = ‘3’"[/code]

#add developer, and admins/spsetup/spfarm, set in $users variable above

[code language=”powershell”]foreach($user in $users) {

$domainuser= $domainname + "\"+$user

$Group = "Administrators"

$de = [ADSI]"WinNT://$name/$Group,group"

$de.Add("WinNT://$domainname/$user")

Write-Host "Done, $domainuser has been permissioned to this computer."

}

net localgroup administrators[/code]

#create folder and share for apps

[code language=”powershell”]New-Item -Path F:\tools -ItemType directory -Value Tools

New-SMBShare –Name "Tools" –Path "F:\Tools" -ChangeAccess "Everyone"[/code]

Disable UAC (for developers), restart computer, set execution policy, etc.

#Disable UAC

[code language=”powershell”]Set-ItemProperty -Path HKLM:\Software\Microsoft\Windows\CurrentVersion\policies\system -Name EnableLUA -Value 0[/code]

#allow scripts

[code language=”powershell”]Set-executionpolicy unrestricted -force[/code]

#reboot

[code language=”powershell”]Restart-computer[/code]

#wait 5 minutes for reboot

#Reconnect to powershell

(exit, reconnect to remote powershell, re-run vars)

Connect to remote session

#Connect via remote powershell as local azure.admin
#uses variables from when the VM was created above

[code language=”powershell”]$passwordsec = convertto-securestring $localadminPassword -asplaintext -force

$user = $name +"\"+ $localadminname

$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$passwordsec

$uri = Get-AzureWinRMUri -ServiceName $service -Name $name

Enter-PSSession -ConnectionUri $uri -Credential $cred

$env:computername[/code]

#Run variables Again!!!

# IMPORTANT, COPY AND PASTE THE ABOVE VARIABLES SECTION IN AGAIN. This is a new remote session to the new Azure VM.

[code language=”powershell”]</span>$varVMLocation = "A"

$varVMServerType = "SP"

$varVMSPVersion = "13"

$varVMType = "D"

$varVMIntanceNum = "08"

$varVMReduxSuffix = "" # I sometimes append a version letter to the end of the developers VM, if they are getting an additional VM of the same role.

$spsetupname = "svc_spsetup"

$spsetuppasstext = "passw0rdspsetup"

$users = @("svc_spsetup", "svc_spfarm", "eric.schrader", "dev1", "etc");

$varVMStaticIP = "192.168.1.87"

$varStorageAccount = "Company" + $varVMLocation + $varVMType + $varVMIntanceNum #unique

$varStorageAccount = $varStorageAccount.ToLower()

$service = "Company-Redondo-" + $varVMType + $varVMIntanceNum

$instancesize = "Basic_A4"

$subscriptionid = "12345678-12345-123456"

$subscriptionName = "Microsoft Azure Enterprise"

$imageFamily = "Windows Server 2012 R2 Datacenter" #Azure VM Image name, the latest will be used below.

$localadminname = "company.admin" #cant be "administrator", etc.

$localadminPassword = "passw0rdlocaladmin"

$joindomain = "Domain.local"

$domainname = "Domain"

$machineOU = "OU=Azure,OU=Development,OU=Servers,OU=Seattle,DC=Company,DC=local"

$timezone = "Pacific Standard Time"

$domainusername = "svc_spsetup"

$domainpassword = "passw0rdspsetup"

$datadiskGB = 127

$datadiskLUN = 0

$datadiskCACHE = "None"

$vmsubnet = "Subnet-1"

$vmaffinitygroup = "VPN-Linked"[/code]

Install SQL pre-reqs

#Install .net 3.5 for SQL prereq on SQL server

[code language=”powershell”]Install-WindowsFeature –name NET-Framework-Core[/code]

Now that the VM is ready for software installs, lets copy the software over. Due to the Windows “triple hop” issue of credentials, I cannot remote into the VM then copy from a 3^rd remote location to the vm. I will have to RDP manually

RDP to fileshare computer as svc_SPSetup

Run variables on fileshare computers PowerShell

#Run variables Again!!!

# IMPORTANT, COPY AND PASTE THE ABOVE VARIABLES SECTION IN AGAIN. This is a new VM session.

[code language=”powershell”]$varVMLocation = "A"

$varVMServerType = "SP"

$varVMSPVersion = "13"

$varVMType = "D"

$varVMIntanceNum = "08"

$varVMReduxSuffix = "" # I sometimes append a version letter to the end of the developers VM, if they are getting an additional VM of the same role.

$spsetupname = "svc_spsetup"

$spsetuppasstext = "passw0rdspsetup"

$users = @("svc_spsetup", "svc_spfarm", "eric.schrader", "dev1", "etc");

$varVMStaticIP = "192.168.1.87"

$varStorageAccount = "Company" + $varVMLocation + $varVMType + $varVMIntanceNum #unique

$varStorageAccount = $varStorageAccount.ToLower()

$service = "Company-Redondo-" + $varVMType + $varVMIntanceNum

$instancesize = "Basic_A4"

$subscriptionid = "12345678-12345-123456"

$subscriptionName = "Microsoft Azure Enterprise"

$imageFamily = "Windows Server 2012 R2 Datacenter" #Azure VM Image name, the latest will be used below.

$localadminname = "company.admin" #cant be "administrator", etc.

$localadminPassword = "passw0rdlocaladmin"

$joindomain = "Domain.local"

$domainname = "Domain"

$machineOU = "OU=Azure,OU=Development,OU=Servers,OU=Seattle,DC=Company,DC=local"

$timezone = "Pacific Standard Time"

$domainusername = "svc_spsetup"

$domainpassword = "passw0rdspsetup"

$datadiskGB = 127

$datadiskLUN = 0

$datadiskCACHE = "None"

$vmsubnet = "Subnet-1"

$vmaffinitygroup = "VPN-Linked"[/code]

Copy the software

#Run from Fileshare as SPSetup in PowerShell

#re-run variables

#run installers
#Copy applications from local computer S drive on \\fileshare to server F drive

[code language=”powershell”]Copy-Item C:\Fileshare\applications\7zipInstall.msi -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\ccleaner.exe -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\fiddler4setup.exe -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item ‘C:\Fileshare\applications\Firefox Setup Stub 36.0.4.exe’ -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\iview438_setup.exe -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\LINQPad4Setup.exe -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\npp.6.7.5.Installer.exe -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\paint.net.4.0.5.install.exe -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\PowerGUI.3.8.0.129.msi -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\cutepdf-writer.exe -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\CKS.Dev11.vsix -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\codecompare.exe -Destination <a href="///\\$name\tools">\\$name\tools</a>[/code]

#copy exes to F:\Tools

[code language=”powershell”]Copy-Item C:\Fileshare\applications\ULSViewer.zip -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\U2U.SharePoint.CQB2010.zip -Destination <a href="///\\$name\tools">\\$name\tools</a>

Copy-Item C:\Fileshare\applications\CamlDesigner2013\* -Destination <a href="///\\$name\tools">\\$name\tools</a> -Recurse

Copy-Item "C:\Fileshare\Visual Studio 2012\*" -Destination <a href="///\\$name\tools">\\$name\tools</a> -Recurse

Copy-Item "C:\Fileshare\Visual Studio 2015\*" -Destination <a href="///\\$name\tools">\\$name\tools</a> -Recurse

Copy-Item "C:\Fileshare\SharePoint Designer 2013\*" -Destination <a href="///\\$name\tools">\\$name\tools</a> -Recurse[/code]

#copy SQL to SQL server (Copy ISO CONTENTS)

[code language=”powershell”]Copy-Item C:\Fileshare\en_sql_server_2014_enterprise_edition_with_service_pack_1_x64_dvd_6669618\* -Destination <a href="///\\$name\tools">\\$name\tools</a> -Recurse

Copy-Item C:\Fileshare\AutoSPInstallerDev2013\* -Destination <a href="///\\$name\tools">\\$name\tools</a> -Recurse

Copy-Item C:\Fileshare\sql2014configdevint\* -Destination <a href="///\\$name\tools">\\$name\tools</a> -Recurse -force

Copy-Item C:\Fileshare\en_sharepoint_server_2013_with_sp1_x64_dvd_3823428\* -Destination <a href="///\\$name\tools\AutoSPInstaller\SP\2013\SharePoint">\\$name\tools\AutoSPInstaller\SP\2013\SharePoint</a> -Recurse -force

Copy-Item "C:\Fileshare\SharePoint 2013 June 2015 CU" -Destination <a href="///\\$name\tools\AutoSPInstaller\SP\2013\Updates">\\$name\tools\AutoSPInstaller\SP\2013\Updates</a> -Recurse -force[/code]

Close RDP to fileshare and go back to your local computers PowerShell. We are ready to install the software

#re-run variables

#Run variables Again!!!

# IMPORTANT, COPY AND PASTE THE ABOVE VARIABLES SECTION IN AGAIN. This is a new remote session to the new Azure VM.

[code language=”powershell”]$varVMLocation = "A"

$varVMServerType = "SP"

$varVMSPVersion = "13"

$varVMType = "D"

$varVMIntanceNum = "08"

$varVMReduxSuffix = "" # I sometimes append a version letter to the end of the developers VM, if they are getting an additional VM of the same role.

$spsetupname = "svc_spsetup"

$spsetuppasstext = "passw0rdspsetup"

$users = @("svc_spsetup", "svc_spfarm", "eric.schrader", "dev1", "etc");

$varVMStaticIP = "192.168.1.87"

$varStorageAccount = "Company" + $varVMLocation + $varVMType + $varVMIntanceNum #unique

$varStorageAccount = $varStorageAccount.ToLower()

$service = "Company-Redondo-" + $varVMType + $varVMIntanceNum

$instancesize = "Basic_A4"

$subscriptionid = "12345678-12345-123456"

$subscriptionName = "Microsoft Azure Enterprise"

$imageFamily = "Windows Server 2012 R2 Datacenter" #Azure VM Image name, the latest will be used below.

$localadminname = "company.admin" #cant be "administrator", etc.

$localadminPassword = "passw0rdlocaladmin"

$joindomain = "Domain.local"

$domainname = "Domain"

$machineOU = "OU=Azure,OU=Development,OU=Servers,OU=Seattle,DC=Company,DC=local"

$timezone = "Pacific Standard Time"

$domainusername = "svc_spsetup"

$domainpassword = "passw0rdspsetup"

$datadiskGB = 127

$datadiskLUN = 0

$datadiskCACHE = "None"

$vmsubnet = "Subnet-1"

$vmaffinitygroup = "VPN-Linked"[/code]

RDP to Developer VM using svc_SPSetup and install SQL by PowerSHell.
#via SPSetup , possibly have to sign into RDP instead of remote PS. Takes 20 minutes

[code language=”powershell”]start-process F:\tools\sql\Setup.exe -ArgumentList "/q /SkipRules=VSShellInstalledRule RebootRequiredCheck /ConfigurationFile=F:\Tools\ConfigurationFile.ini /ERRORREPORTING=1 /IACCEPTSQLSERVERLICENSETERMS" -Wait[/code]

Close RDP to developer VM once SQL is done.

From local computer, Connect via remote powershell as spsetup. There is code in here to install SQL remotely, but it too has a triple hop credential issue since I use service accounts. I just RDP to the VM and install SQL via PowerShell there.
#uses variables from when the VM was created above

[code language=”powershell”]$passwordsec = convertto-securestring $spsetuppasstext -asplaintext -force

$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $spsetupname ,$passwordsec

$uri = Get-AzureWinRMUri -ServiceName $service -Name $name

Enter-PSSession -ConnectionUri $uri -Credential $cred

$env:computername[/code]

#FIX – maybe to install SQL remotely
#Invoke-Command -ComputerName $name -Authentication CredSSP -credential $cred -scriptblock {
#F:\tools\sql\Setup.exe -ArgumentList “/q /SkipRules=VSShellInstalledRule RebootRequiredCheck /ConfigurationFile=F:\Tools\ConfigurationFile.ini /ERRORREPORTING=1 /IACCEPTSQLSERVERLICENSETERMS” -Wait
#}

Set SQL max memory to 10GB, set Max Degree of parallelism to 1 (this is a huge script, maybe you can shorten it)

#Set Max degree of parallelism to 1

[code language=”powershell”]## Sets the ‘max degree of parallelism’ value to 1 for the specified SQL server instance
## Port 1433 is used if not specified
## 2012-10-08
## <a href="http://www.pointbeyond.com">www.pointbeyond.com</a>
## NOTE: This function requires at least serveradmin level permissions within SQL server
function SetMaxDegreeOfParallelism()

{

Param(

$server,

$port="1433")

$conn = new-object System.Data.SqlClient.SqlConnection

try

{

$connectionString = "Server="+$server+","+$port+";Database=master;Integrated Security=True;"

$conn.ConnectionString = $connectionString

$conn.Open()

$cmd = new-object System.Data.SqlClient.SqlCommand

$cmd.Connection = $conn

# Ensure advanced options are available

$commandText = "sp_configure ‘show advanced options’, 1;RECONFIGURE WITH OVERRIDE;"

$cmd.CommandText = $commandText

$r = $cmd.ExecuteNonQuery()

# Set the Max Degree of Parallelism value to 1

write-host "Setting ‘max degree of parallelism’ value to 1 for server $server…"

$commandText = "sp_configure ‘max degree of parallelism’, 1;RECONFIGURE WITH OVERRIDE"

$cmd.CommandText = $commandText

$r = $cmd.ExecuteNonQuery()

write-host "Success"

}

catch

{

write-host "An error occurred trying to set the MaxDegreeOfParallelism value to 1 for server $server" -Fore Red

write-host "Ensure that server and port parameters are correct and that the current user has at least serveradmin permissions within SQL" -Fore Red

}

finally

{

$conn.Close()

}

# Call the function passing in SQL server name/instance/alias and port number

SetMaxDegreeOfParallelism -server $name -port "1433"[/code]

#Set SQL Max memory – 3GB

[code language=”powershell”]Function Test-SqlSa {

.SYNOPSIS

Ensures sysadmin account access on SQL Server. $server is an SMO server object.

.EXAMPLE

if (!(Test-SQLSA $server)) { throw "Not a sysadmin on $source. Quitting." }

.OUTPUTS

$true if syadmin

$false if not

[CmdletBinding()]

param(

[Parameter(Mandatory = $true)]

[ValidateNotNullOrEmpty()]

[object]$server

)

try {

return ($server.ConnectionContext.FixedServerRoles -match "SysAdmin")

}

catch { return $false }

}

Function Get-ParamSqlCmsGroups {

.SYNOPSIS

Returns System.Management.Automation.RuntimeDefinedParameterDictionary

filled with server groups from specified SQL Server Central Management server name.

.EXAMPLE

Get-ParamSqlCmsGroups sqlserver

[CmdletBinding()]

param(

[Parameter(Mandatory = $true)]

[string]$Server

)

if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") -eq $null) {return}

if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers") -eq $null) {return}

$cmserver = New-Object Microsoft.SqlServer.Management.Smo.Server $server

$sqlconnection = $cmserver.ConnectionContext.SqlConnectionObject

try { $cmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($sqlconnection)}

catch { return }

if ($cmstore -eq $null) { return }

$newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary

$paramattributes = New-Object System.Management.Automation.ParameterAttribute

$paramattributes.ParameterSetName = "__AllParameterSets"

$paramattributes.Mandatory = $false

$argumentlist = $cmstore.DatabaseEngineServerGroup.ServerGroups.name

if ($argumentlist -ne $null) {

$validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $argumentlist

$combinedattributes = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute]

$combinedattributes.Add($paramattributes)

$combinedattributes.Add($validationset)

$SqlCmsGroups = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("SqlCmsGroups", [String[]], $combinedattributes)

$newparams.Add("SqlCmsGroups", $SqlCmsGroups)

return $newparams

} else { return }

}

Function Get-SqlCmsRegServers {

.SYNOPSIS

Returns array of server names from CMS Server. If -Groups is specified,

only servers within the given groups are returned.

.EXAMPLE

Get-SqlCmsRegServers -Server sqlserver -Groups "Accounting", "HR"

[CmdletBinding()]

param(

[Parameter(Mandatory = $true)]

[string]$server,

[string[]]$groups

)

if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") -eq $null) {return}

if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers") -eq $null) {return}

$cmserver = New-Object Microsoft.SqlServer.Management.Smo.Server $server

$sqlconnection = $cmserver.ConnectionContext.SqlConnectionObject

try { $cmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($sqlconnection)}

catch { throw "Cannot access Central Management Server" }

$servers = @()

if ($groups -ne $null) {

foreach ($group in $groups) {

$cms = $cmstore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$group]

$servers += ($cms.GetDescendantRegisteredServers()).servername

}

} else {

$cms = $cmstore.ServerGroups["DatabaseEngineServerGroup"]

$servers = ($cms.GetDescendantRegisteredServers()).servername

}

return $servers

}

Function Get-SqlMaxMemory {

.SYNOPSIS

Displays information relating to SQL Server Max Memory configuration settings. Works on SQL Server 2000-2014.

.DESCRIPTION

Inspired by Jonathan Kehayias’s post about SQL Server Max memory (<a href="http://bit.ly/sqlmemcalc">http://bit.ly/sqlmemcalc</a>), this script displays a SQL Server’s:

total memory, currently configured SQL max memory, and the calculated recommendation.

Jonathan notes that the formula used provides a *general recommendation* that doesn’t account for everything that may be going on in your specific environment.

.PARAMETER Servers

Allows you to specify a comma separated list of servers to query.

.PARAMETER ServersFromFile

Allows you to specify a list that’s been populated by a list of servers to query. The format is as follows

server1

server2

server3

.PARAMETER SqlCms

Reports on a list of servers populated by the specified SQL Server Central Management Server.

.PARAMETER SqlCmsGroups

This is a parameter that appears when SqlCms has been specified. It is populated by Server Groups within the given Central Management Server.

.NOTES

Author : Chrissy LeMaire

Requires: PowerShell Version 3.0, SQL Server SMO, sysadmin access on SQL Servers

DateUpdated: 2015-May-21

.LINK

<a href="https://gallery.technet.microsoft.com/scriptcenter/Get-Set-SQL-Max-Memory-19147057">https://gallery.technet.microsoft.com/scriptcenter/Get-Set-SQL-Max-Memory-19147057</a>

.EXAMPLE

Get-SqlMaxMemory -SqlCms sqlcluster

Get Memory Settings for all servers within the SQL Server Central Management Server "sqlcluster"

.EXAMPLE

Get-SqlMaxMemory -SqlCms sqlcluster | Where-Object { $_.SqlMaxMB -gt $_.TotalMB } | Set-SqlMaxMemory -UseRecommended

Find all servers in CMS that have Max SQL memory set to higher than the total memory of the server (think 2147483647)

[CmdletBinding()]

Param(

[parameter(Position=0)]

[string[]]$Servers,

# File with one server per line

[string]$ServersFromFile,

# Central Management Server

[string]$SqlCms

)

DynamicParam { if ($SqlCms) { return (Get-ParamSqlCmsGroups $SqlCms) } }

PROCESS {

if ([string]::IsNullOrEmpty($SqlCms) -and [string]::IsNullOrEmpty($ServersFromFile) -and [string]::IsNullOrEmpty($servers))

{ throw "You must specify a server list source using -Servers or -SqlCms or -ServersFromFile" }

if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") -eq $null )

{ throw "Quitting: SMO Required. You can download it from <a href="http://goo.gl/R4yA6u">http://goo.gl/R4yA6u</a>" }

if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers") -eq $null )

{ throw "Quitting: SMO Required. You can download it from <a href="http://goo.gl/R4yA6u">http://goo.gl/R4yA6u</a>" }

$SqlCmsGroups = $psboundparameters.SqlCmsGroups

if ($SqlCms) { $servers = Get-SqlCmsRegServers -server $SqlCms -groups $SqlCmsGroups }

If ($ServersFromFile) { $servers = Get-Content $ServersFromFile }

$collection = @()

foreach ($servername in $servers) {

Write-Verbose "Attempting to connect to $servername"

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername

try { $server.ConnectionContext.Connect() } catch { Write-Warning "Can’t connect to $servername. Moving on."; continue }

$maxmem = $server.Configuration.MaxServerMemory.ConfigValue

$reserve = 1

$totalMemory = $server.PhysicalMemory

# Some servers underreport by 1MB.

if (($totalmemory % 1024) -ne 0) { $totalMemory = $totalMemory + 1 }

if ($totalMemory -ge 4096) {

$currentCount = $totalMemory

while ($currentCount/4096 -gt 0) {

if ($currentCount -gt 16384) {

$reserve += 1

$currentCount += -8192

} else {

$reserve += 1

$currentCount += -4096

}

$recommendedMax = [int]($totalMemory-($reserve*1024))

$object = New-Object PSObject -Property @{

Server = $server.name

TotalMB = $totalMemory

SqlMaxMB = $maxmem

RecommendedMB = $recommendedMax

}

$server.ConnectionContext.Disconnect()

$collection += $object

}

return ($collection | Sort-Object Server | Select Server, TotalMB, SqlMaxMB, RecommendedMB)

}

Function Set-SqlMaxMemory {

.SYNOPSIS

Sets SQL Server max memory then displays information relating to SQL Server Max Memory configuration settings. Works on SQL Server 2000-2014.

.PARAMETER Servers

Allows you to specify a comma separated list of servers to query.

.PARAMETER ServersFromFile

Allows you to specify a list that’s been populated by a list of servers to query. The format is as follows

server1

server2

server3

.PARAMETER SqlCms

Reports on a list of servers populated by the specified SQL Server Central Management Server.

.PARAMETER SqlCmsGroups

This is a parameter that appears when SqlCms has been specified. It is populated by Server Groups within the given Central Management Server.

.PARAMETER MaxMB

Specifies the max megabytes

.PARAMETER UseRecommended

Inspired by Jonathan Kehayias’s post about SQL Server Max memory (<a href="http://bit.ly/sqlmemcalc">http://bit.ly/sqlmemcalc</a>), this uses a formula to determine the default optimum RAM to use, then sets the SQL max value to that number.

Jonathan notes that the formula used provides a *general recommendation* that doesn’t account for everything that may be going on in your specific environment.

.NOTES

Author : Chrissy LeMaire

Requires: PowerShell Version 3.0, SQL Server SMO, sysadmin access on SQL Servers

DateUpdated: 2015-May-21

.LINK

<a href="https://gallery.technet.microsoft.com/scriptcenter/Get-Set-SQL-Max-Memory-19147057">https://gallery.technet.microsoft.com/scriptcenter/Get-Set-SQL-Max-Memory-19147057</a>

.EXAMPLE

Set-SqlMaxMemory sqlserver 2048

Set max memory to 2048 MB on just one server, "sqlserver"

.EXAMPLE

Get-SqlMaxMemory -SqlCms sqlcluster | Where-Object { $_.SqlMaxMB -gt $_.TotalMB } | Set-SqlMaxMemory -UseRecommended

Find all servers in CMS that have Max SQL memory set to higher than the total memory of the server (think 2147483647),

then pipe those to Set-SqlMaxMemory and use the default recommendation

.EXAMPLE

Set-SqlMaxMemory -SqlCms sqlcluster -SqlCmsGroups Express -MaxMB 512 -Verbose

Specifically set memory to 512 MB for all servers within the "Express" server group on CMS "sqlcluster"

[CmdletBinding()]

Param(

[parameter(Position=0)]

[string[]]$Servers,

[parameter(Position=1)]

[int]$MaxMB,

[string]$ServersFromFile,

[string]$SqlCms,

[switch]$UseRecommended,

[Parameter(ValueFromPipeline=$True)]

[object]$collection

)

DynamicParam { if ($SqlCms) { return (Get-ParamSqlCmsGroups $SqlCms)} }

PROCESS {

if ([string]::IsNullOrEmpty($SqlCms) -and [string]::IsNullOrEmpty($ServersFromFile) -and [string]::IsNullOrEmpty($servers) -and $collection -eq $null)

{ throw "You must specify a server list source using -Servers or -SqlCms or -ServersFromFile or you can pipe results from Get-SqlMaxMemory" }

if ($MaxMB -eq 0 -and $UseRecommended -eq $false -and $collection -eq $null) { throw "You must specify -MaxMB or -UseRecommended" }

if ($collection -eq $null) {

$SqlCmsGroups = $psboundparameters.SqlCmsGroups

if ($SqlCmsGroups -ne $null) {

$collection = Get-SqlMaxMemory -Servers $servers -SqlCms $SqlCms -ServersFromFile $ServersFromFile -SqlCmsGroups $SqlCmsGroups

} else { $collection = Get-SqlMaxMemory -Servers $servers -SqlCms $SqlCms -ServersFromFile $ServersFromFile }

}

$collection | Add-Member -NotePropertyName OldMaxValue -NotePropertyValue 0

foreach ($row in $collection) {

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $row.server

try { $server.ConnectionContext.Connect() } catch { Write-Warning "Can’t connect to $servername. Moving on."; continue }

if (!(Test-SqlSa $server)) {

Write-Warning "Not a sysadmin on $servername. Moving on."

$server.ConnectionContext.Disconnect()

continue

}

$row.OldMaxValue = $row.SqlMaxMB

try {

if ($UseRecommended) {

Write-Verbose "Changing $($row.server) SQL Server max from $($row.SqlMaxMB) to $($row.RecommendedMB) MB"

$server.Configuration.MaxServerMemory.ConfigValue = $row.RecommendedMB

$row.SqlMaxMB = $row.RecommendedMB

} else {

Write-Verbose "Changing $($row.server) SQL Server max from $($row.SqlMaxMB) to $MaxMB MB"

$server.Configuration.MaxServerMemory.ConfigValue = $MaxMB

$row.SqlMaxMB = $MaxMB

}

$server.Configuration.Alter()

} catch { Write-Warning "Could not modify Max Server Memory for $($row.server)" }

$server.ConnectionContext.Disconnect()

}

return $collection | Select Server, TotalMB, OldMaxValue, @{name="CurrentMaxValue";expression={$_.SqlMaxMB}}

}

Set-SqlMaxMemory $name 10000[/code]

Install Developer APPS

[code language=”powershell”]start-process F:\tools\7zipInstall.msi -ArgumentList "/q" -Wait

start-process F:\tools\ccleaner.exe -argumentlist "/S" -Wait

start-process F:\tools\fiddler4setup.exe -ArgumentList "/S" -Wait

start-process ‘F:\tools\Firefox Setup Stub 36.0.4.exe’ -ArgumentList "/S" -Wait

start-process F:\tools\iview438_setup.exe -ArgumentList "/silent" -Wait

start-process F:\tools\LINQPad4Setup.exe -ArgumentList "/silent" -Wait

start-process F:\tools\npp.6.7.5.Installer.exe -ArgumentList "/S" -Wait

start-process F:\tools\paint.net.4.0.5.install.exe -ArgumentList "/S" -Wait

start-process F:\tools\paint.net.4.0.5.install.exe -ArgumentList "/auto" -Wait

start-process F:\tools\PowerGUI.3.8.0.129.msi -ArgumentList "/q"[/code]

#start print spooler for cutepdf

[code language=”powershell”]net start spooler

sc query spooler[/code]

#fix hanging http://d4rkcell.com/archives/1217

[code language=”powershell”]start-process F:\tools\cutepdf-writer.exe -ArgumentList "/VERYSILENT /SUPPRESSMSGBOXES /NORESTART /SP-"

start-process F:\tools\VisualStudio2012\vs_premium.exe -ArgumentList "/adminfile AdminDeployment.xml /passive /norestart" -Wait -NoNewWindow -PassThru

$vsixInstallerPath = "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\VSIXInstaller.exe"

$extensionPath = "F:\tools\CKS.Dev11.vsix"

Start-Process -FilePath $vsixInstallerPath -ArgumentList "/q $extensionPath" -NoNewWindow -Wait

start-process F:\tools\SPDesigner\setup.exe -ArgumentList "/adminfile updates\adminfile.msp" -wait

start-process F:\tools\en_visual_studio_enterprise_2015_with_update_1_x86_x64_web_installer_8234346.exe -ArgumentList "/S /AdminFile F:\Tools\AdminDeployment.xml" -Wait[/code]

RDP to developer VM as svc_SPSetup and Launch AutoSPInstaller bat file

[code language=”powershell”]start-process F:\tools\AutoSPInstaller\SP\AutoSPInstaller\AutoSPInstallerLaunch.bat -wait[/code]

#Run autospinstaller

#Pre-reqs- 10 minutes w Restart

#Install binaries- 15 minutes

#Automation Fix- CU prompts for internet trusted file. 30 minutes (or right click all 3 CU files and go to Security and unblock, already done for June 2015 CU on AzureShare.).

#UPS Sync- we have to do this manually per install guide

#Add developer as full control of web applications

SharePoint- Configure User Profile AD Sync by hand

AD Connection:

Active Directory Company

Company.local

Company\svc_spups

passw0rd

(Sync All OUs)

Enable timerjob 1am daily:

Start full sync:

Manually add developer as full control of web applications.

Done!

SharePoint 2013, IIS7, NLB, SSL certificates and GoDaddy Renewal Steps

Overview:

SSL certificates with SharePoint 2013 web applications expire, and when that does, you have to generate a new SSL Certificate. In this post, I will go over how to renew you SharePoint 2013 SSL HTTPS website with GoDaddy, even including multi-server Web Front End (WFE’s) topologies. If you use wildcard certificates on you SharePoint websites, there are a few gotchas when renewing. The process is similar for most certificate types, but wildcards and SharePoint are this blog posts focus. These steps are also similar if you are adding a SSL certificate to your website for the first time (once your SharePoint farm, web applications, and site collections have been configured to use HTTPS, etc.).

Here is an overview of the steps involved with the certificate renew process:

Request a new certificate request from the machine running IIS/SharePoint (Pick a WFE)
Go to GoDaddy and rekey your certificate, entering your certificate request text from step 1
Complete the certificate request in IIS on WFE
Update WFE bindings to use SSL cert
Export certificate from WFE to WFE2 (PFX with personal information, create a password)
Import the PFX on WFE2 IIS
Update WFE2 bindings to use SSL cert

Common issues:

First, this is my experience. Comment below any corrections or other helpful information.

When adding the cert to IIS and refreshing, it disappears!
- Your certificate request is expired. Generate a new one and try again.
- You are following GoDaddys guide, which does not work. Follow my post below.
- The cert might already exist and need to be deleted in the Certificate Manager on the server.
CER, CRT, PFX- what is the difference? Why do I have to select *.* if I need a specific type? Who designed this stuff…
- CER is a request
- CRT is a certificate without private information
- PFX is a certificate package with private information (exported from CRT paired on the first server, the PFX is imported to the second server).
How do I complete a request on WFE2 if it was already completed from WFE1?
- Export the working cert from Server 1 as a PFX file with a password, then import it on server 2 in IIS. Do not use cert manager on server 2.

Steps to renew your Existing wildcard SSL Certificate:

Verify your certificate is expired by navigating to your SharePoint site. If you get an HTTPS trust warning, it’s expired or has issues that this blog post will address.
Go to WFE1 IIS 7 on your SharePoint box
1. Go to Server Certificates in IIS
2. Remove any old certificates that contain the URL for your SharePoint site that we are renewing
3. On the top right in IIS, go to “Create Certificate Request”
4. Enter your information. Common name is the wildcard URL. The rest, do not use abbreviations. See this post for more info: https://support.godaddy.com/help/article/4800/generating-iis-7-csrs-certificate-signing-requests
5. Select “4096” for the bit length
6. Select a location/filename for the text file that is about to be generated
7. We will be copying the contents of this file to GoDaddy to rekey our wildcard SSL certificate in the next step.
Now that we have our server “key” information waiting in the text file, we can now go to GoDaddy and pair this server information to that of our SSL certificate.
1. Go to Go Daddy Certificate Manager (Manage SSL Certificates > Manage Certificates)
2. Select “Re-Key” on the top navigation
3. Paste your text file contents from the IIS text file to this GoDaddy window:
4. Select “Re-Key”
5. Click “Manage Certificates” From the top navigation, then select “Certificates” folder on the left navigation.
6. Select the bottom SSL certificate (the most recent version)
7. Select “Download” icon from the navigation.
8. Select IIS7, the “Download”
9. Save this zip to your WFE server where you created the IIS certificate request.
10. Extract to C:\Temp and proceed carefully to the next steps in this post.
On WFE1 in IIS where you created the certificate request, open IIS 7 and follow these steps to use the certificate you downloaded from GoDaddy.
1. Remove any old expired wildcard certificates from the WFE1 servers “Certificate Manager”, check Personal > Certificates and the Intermediate > Certificates locations
2. COMMON GOTCHA: Do not install the cert, do it using IIS.
3. Go back to “Server Manager” in IIS 7, select “Complete Certificate Request” on the right navigation
4. Enter the information for the Certificate request as follows:
5. COMMON GOTCHA: Select *.* when browsing for the CRT file from the GoDaddy zip
6. Friendly name must be the wildcard URL of the domain.
7. Click OK.
8. Refresh the Server Manager to verify the certificate “stays”. If it disappears, you either have:
  1. A certificate in your Personal Certificate store with the same friendly name
  2. An expired or old Certificate Request you generated and downloaded, or you downloaded an older certificate from GoDaddy. Repeat these steps and it will work (it should).
Set the IIS binding of the new certificate to your SharePoint 443 SSL HTTPS website in IIS:
1. Go to IIS 7 > Sites > select the SharePoint site that uses the wildcard cert.
2. Select “Bindings” on the right with the website selected.
3. Select “Edit” and select the new SSL certificate
4. Select OK. On WFE2, you will get an error here trying to use an exported PFX file, follow the next steps to fix WFE2.
5. Verify the site loads on WFE1 if you can control your DNS/NLB routing.
If you have additional WFE servers, you need to export this new verified SSL certificate to IIS. Here is how.
1. From WFE1, Go to “Server Certificates”, right click the wildcard cert and select “Export”
2. Pick a location for the new PFX file, then enter a secure password.
3. Click OK
4. Copy the PFX file to WFE2 through Explorer or any other method.
5. On WFE2, go to IIS 7 > “Server Certificates” and select “Import”
6. Browse to the PFX file copied over from WFE1, enter your password and select OK.
7. Refresh “Server Certificates” to verify it is still available.
8. Repeat the import process in IIS on other WFE servers.
Now that the certificate is available on the other WFE’s in IIS, we need to update the bindings. Same process as the first WFE.
1. (Copied and pasted from WFE1 steps, but perform these on the WFE2 and additional servers once the certificate is imported)
2. Go to IIS 7 > Sites > select the SharePoint site that uses the wildcard cert.
3. Select “Bindings” on the right with the website selected.
4. Select “Edit” and select the new SSL certificate
5. Select OK.
6. Verify the site loads on WFE2 if you can control your DNS/NLB routing.

That’s it! I believe most of what’s above is best practices. I would also remove temporary certificate files, such as PFX, CSR files, etc. left around during the process for added security.

Feature with ID ‘87294c72-f260-42f3-a41b-981a2ffce37a’ is not installed in this farm, and cannot be added to this scope. Error creating SharePoint site collection, Powershell

I had an error today when I created my farm via PowerShell. I forgot to run the SharePoint Products and Configuration Wizard (PSConfig) after creating the farm. The result was an error when creating site collections via the UI, as well as subsites via the UI. I could only create the sites via PowerShell and I got quite a few errors in the UI and had to navigate to http://intranet/_layouts/settings.aspx to get the site to load without an error.

Sorry, something went wrong.

Feature with ID ‘87294c72-f260-42f3-a41b-981a2ffce37a’ is not installed in this farm, and cannot be added to this scope.

Technical Details

ULS shows this error creating a subsite under the root site collection via the UI: “Failed to apply template “STS#0” to web at URL “http://intranet.com/Test”, error Feature with ID ‘guid’ is not installed in this farm, and cannot be added to this scope.”

Solution: Run PSConfig, then try creating your site in the UI.

If you created the sites via PS and want to delete them, I had to do get-spsite | remove-spsite (THIS REMOVES ALL SITE COLLECTIONS). Then I ran PSConfig and recreated the Site Collections via PowerShell successfully.

You can run the following command:

Install-SPFeature –AllExistingFeatures

via powershell, but there are other commands as well that must be ran that PSConfig performs:

Install-SPHelpCollection -All

Initialize-SPResourceSecurity

Install-SPService

Install-SPFeature –AllExistingFeatures

New-SPCentralAdministration -Port 1234 -WindowsAuthProvider “NTLM”

Install-SPApplicationContent

SharePoint 2013 On-Premise App Store Configuration

The SharePoint 2013 March Public Update requires additional configuration steps to complete a SharePoint App Store deployment.
In this article, “Enable apps in AAM or host-header environments for SharePoint 2013”, http://technet.microsoft.com/en-us/library/dn144963.aspx The additonal steps are indicated.

New-SPWebApplicationAppDomain
$contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService $contentService.SupportMultipleAppDomains = $true $contentService.Update() Iisreset

SharePoint 2013 – Common Installation Issues

With SharePoint 2013, I have had a lot of installation issues. I will cover the latest issues I have ran into. This post does not enforce the Least Privlages security practice, but it can easily be adapted to for other environments. This post was for standing up a development environment. After a long battle of getting SharePoint 2013 to work without any issues, I wanted to share my experience in one place.

I have documented our company’s current development environment setup in this post. Your environment does not have to match mine 100%. Feel free to use Windows Server 2012, etc.

Installation Media Requirements (MSDN):

en_windows_server_2008_r2_standard_enterprise_datacenter_and_web_with_sp1_x64_dvd_617601.iso
en_office_professional_plus_2013_x64_dvd_1123674.iso
en_sharepoint_designer_2013_x64_1134649.exe
en_sharepoint_server_2013_x64_dvd_1121447.iso
en_sql_server_2012_enterprise_edition_with_sp1_x64_dvd_1227976.iso
en_visio_professional_2013_x64_1123802.exe
en_visual_studio_premium_2012_x86_dvd_920758.iso and update en_visual_studio_2012_x86_update_1_1203928.exe

One common issue I found is that I had to run Windows Update before installing SQL 2012. There were 96 Windows updates on a clean server install and it took 2 hours. At about an hour and a half Internet Explorer has a hidden install prompt in the background that you have to click “Next” on, etc. The patch process is as follows: Install, reboot, install security update, reboot one last time.

Before installing SQL Server 2012, I had to go to my Windows Server 2008 R2 Roles/Features and add the .NET Framework 3.5.1 Features.

Next I installed SQL Server 2012. I let the installer check for updates. If this installer fails, you probably did not finish the Windows Updates.

I installed the following features for SQL:

Database engine
Full text Search
SSAS
SSRS Integrated
SSRS addin tool
Management tools basic and complete (for SSRS)

You don’t need Full Text, but we are using it in our development environment for custom SQL applications outside of SharePoint.

Install Visual Studio 2012 and the update listed in the beginning of this post.

Then Install SharePoint 2013 Prerequisites. You will need to reboot and continue, then reboot again (I see a pattern here…)

For configuing SharePoint, MAKE SURE YOU ARE LOGGED IN AS A DOMAIN USER ACCOUNT – SPSetup for example! I tried using local admin and my service account created the SQL database, but got an error on the products and configuration wizard:

03/26/2013 23:07:06 6 ERR Failed to create the configuration database.

An exception of type System.InvalidOperationException was thrown. Additional exception information: An error occurred while getting information about the user SPFarm at server Domain.local: Access is denied

System.InvalidOperationException: An error occurred while getting information about the user SPFarm at server Domain.local: Access is denied

Configuration failed. One or more configuration settings failed. Failed to create the configuration database.

Basically SharePoint is trying to get information about this service account and access is denied. This is because you are running the Windows session and Products and Configuration Wizard as a local user that does not have access to the AD OU to verify the SPFarm domain account. Log out, Log in as SPSetup (or any domain user if you don’t have one) and re-launch the SharePoint Products and Configuration Wizard. Specify the SPFarm account to connect to the SQL Database. Dont forget, you might have to log in to SQL Management Studio and remove the partially created farm database SharePoint_Config or use a different name the second time.

Then Install SharePoint 2013. I use Complete instead of Stand-alone.

Specify your domain Database Access account (Domain\SPFarm). This account has to be DBOwner and Security Admin in the SQL instance (or sysadmin if you are lazy and frustrated).

Launch the Farm Configuration Wizard.

Specify a new managed account for the service applications (For Dev I ended up using the same SPFarm account).

I noticed the SharePoint 2013 Farm Configuration Wizard said “Working on it…” Sorry to keep you waiting. for over 6 hours. I read a blog post from Todd that once the W3WP.exe and OWSTimer.exe processes die down, you can kill the IE window and re-open Central Admin. Give it a good 15 minutes or so. More time the better.

Once you bring up Central Admin, then create the root site collection. My Sites is configured on the root web application under /my site collection. Go to About Me on your username above the ribbon to verify everything works.

Boom, SharePoint 2013 is up and running.

SharePoint 2010 Caching options

These are a few notes from Designing a Microsoft SharePoint 2010 Infrastructure PDF. (Page’s 3-30 and 3-31)

There are 3 different types of caching options in SharePoint 2010. Two of these require publishing features.

(page) Output Caching
1. Setting this to as small as 60 seconds can make a big difference on WFE servers load
2. Page requests are stored in memory on the WFE server.
3. Available with the Publishing feature enabled
4. Memory based on WFE servers
5. Cache Profiles
  1. Determine which users receive cached pages, etc. Set at the Site, Site collection, or web application level
Object cache
1. Store lists/ libraries/ or page layouts on WFE server. Size adjusted at the site collection or web application level
2. Available with the Publishing feature enabled
3. Memory based on WFE servers
BLOB cache
1. Better for media streaming (allows files to play before they are finished downloading). Possibly better for large files (depending on your SQL specs vs WFE).
2. Default is 10GB and disabled
3. Web application level
4. Disk based on WFE servers

Microsoft SharePoint Hyper-V virtual machine DNS fix

After downloading the MS SharePoint preconfigured VM (demo2010a and demo2010b), I started them up on my network and noticed they would not load the SharePoint sites.

Here is the VM I downloaded from Microsoft: 2010 Information Worker Demonstration and Evaluation Virtual Machine (RTM):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=751fa0d1-356c-4002-9c60-d539896c66ce&displaylang=en

After trying the site http://intranet.contoso.com in the Demo2010A machines internet browser and getting a Page cannot be displayed error, I checked my app pools, IIS web site, central admin (which worked on http://demo2010a:2010 btw), alternate access mappings, and DNS.

I noticed some funny settings with my vm’s DNS manager on Demo2010A machine.

The IP’s I was seeing were 192.168.150.0 and 192.168.150.1, which I am on the 10.6.0.x network, so this was alarming to me.

I called over our new IT guy and put him to work, explaining that I can access one of the applications by hostname (http://demo2010a:2010), but all of the alternate access mappings and DNS entries were not working.

After some troubleshooting and changing the DNS entry for demo2010a and demo2010b (SEE PICTURE ABOVE) to 10.6.0.164 and 10.6.0.169 (The new 10.6.0.x IP’s were the IPs machines were both assigned from TCIP set to automatic).

After I modified the DNS, I did a Command prompt ipconfig /flushdns

Following this,my coworker set the DNS under TCP/IP v4 to the IP of demo2010a machine (the dns server- 10.6.0.169).

The issue was that the DNS server was inheriting from my companys DNS on the same 10.6.0.x network, instead of the contoso DNS entries on demo2010a machine.

After setting Demo2010A and Demo2010B’s DNS to the local IP of demo2010a (10.6.0.169), this fixed the site. I can now access http://intranet.contoso.com from the demo2010a machine. I CANNOT access this on any computer who’s DNS is not set to 10.6.0.169, as the DNS automatically obtains my company’s DNS server and settings.