Microsoft

SQL Server Configuration Manager Aliases

I don’t do too much with SQL, but this one got me for a while, so here’s my story on SQL Server Configuration Manager Aliases.

I had a particular server that couldn’t connect to a specific instance on SQL on another box. Other servers appeared to be fine, but each time SQL Server Management Studio was run and attempted to connect to servername\instancename, it would instead connect to the default instance.

It didn’t matter who logged onto the server either. It would never connect to that secondary instance and I couldn’t work out why.

After much digging and testing, I resorted to reading through forum threads on Google searches, hoping for an idea. What I eventually found was the existence of SQL Client Aliases. These are like hosts file records – hard coded results for connecting to a specific server:

In Sql Server Configuration Manager, you can define an alias for what you’re connecting to. Servername\instancename could map to serverb\instancename or servername\instance2 – this is great when doing testing and wanting to point a server at a different SQL database or instance without changing a bunch of settings.

However, the other catch is the port specified. In the above example, the default SQL port 1433 is used. Makes sense, but each instance uses it’s own port, or uses a dynamic port. I soon discovered that if you try to connect to a SQL instance and have a port defined, the SQL instance you actually connect to is whatever is listening on that defined port.

An easy thing to find if you know where to look for it, and now I do. Hopefully this helps others who come across a similar scenario!

Conditional Access Stuck on “Loading…”

There’s currently an issue with configuring Conditional Access via Azure Active Directory. There’s an open ticket with Microsoft Support, with no ETA at the time of writing.

The issue:  When trying to configure a new policy for Conditional Access against an Azure Active Directory application, the ‘New’ page gets stuck loading. I’ve tested this on multiple browsers, tenants, internet connections, computers, and had Microsoft support confirm.

The path to doing this is from the Azure portal – Azure Active Directory > Enterprise Applications > choose your application > Conditional Access > New policy:

The Workaround: Thankfully it’s not a showstopper, as there’s another way to get to Conditional Access and it works fine. Instead of going via a specific app first, you can just go via Azure Active Directory > Conditional Access > New policy. Also Azure Active Directory > Enterprise Applications > Conditional Access > New policy works, it’s just an extra click to the same screen.

Points to take note of – if something’s broken, try accessing the same function from a different route of click-through links and it might work another way. Also, log these issues with Microsoft Support as overall the support is pretty good and often the issue won’t be anything to do with you. Test different scenarios wherever possible too, and also asking the question on Twitter can get some extra attention!

Automate Backup Of Word AutoRecover Files For More Recovery Options

“I’ve lost a document and can’t find it!” is a common phrase that nobody likes to hear. Most people are working in Microsoft Word for their documents, and although it has a bunch of nice features for autorecovering lost work, it doesn’t cover all scenarios.

There’s even a new feature which autosaves your work as you go; as long as the document is in SharePoint Online or OneDrive for Business.

However, it’s still easy for someone to accidentally close a document and say ‘no’ to saving changes, or other scenarios where documents get overwritten with the wrong information. A document management system (DMS) with versioning (such as SharePoint) can help, but I’ve yet to hear of a company that has 100% of their documents at all times in their DMS!

Anyway, after seeing many scenarios of lost work, I thought there might be another method I can implement to help capture lost data. Microsoft Word’s Autorecover function does work quite well, in keeping an ASD file updated at regular intervals (10 minutes by default) which are saved in C:\Users\username\AppData\Roaming\Microsoft\Word\ (by default). I changed this to 5 minutes rather than 10:

Microsoft Word Options > Save screen

Autorecover will update an ASD file in this folder for each document you have open, on the frequency configured above. That file can get closed or lost depending what the user clicks (again, closing and not saving a document is a scenario that will lose the ASD).

My idea was to back up these ASD files also on a 5 minute interval, giving another avenue to restore lost documents. Because the AutoRecover starts at a random time, a script running every 5 minutes would also start at a random time, and together there’d be a 5 to 10 minute window on copying out the backup files, which isn’t a huge amount of work to lose if someone had been working for hours.

Here’s the PowerShell script I wrote.  It first sets a few variables that can be configured, then does a cleanup of previous backups. If they’re > 2 days old, backup folders are purged or we’d have an ever growing amount of backups. The 2 day value in (Get-Date).AddDays(-2) can be changed.

Then, it runs a filecheck to make sure there’s ASF files to back up. If not, the script breaks. If files exist though, it then creates the Backup folder, creates a sub folder based on the date/time and then copies the ASD files into that folder.

The format of the folders is set at the very start of the script, and again can be changed to a different format if you prefer.

#get-date format https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-date?view=powershell-6
 $date = get-date -uformat %Y%m%d%H%M

$SourceDir= "C:\Users\$env:username\AppData\Roaming\Microsoft\Word\"
 $backupdir = "C:\Users\$env:username\AppData\Roaming\Microsoft\Word\Backup\"
 $targetDir = "C:\Users\$env:username\AppData\Roaming\Microsoft\Word\Backup\$date"

Get-ChildItem $backupdir -Recurse | Where CreationTime -lt (Get-Date).AddDays(-2) | Remove-Item -Force -recurse

$Filecheck = get-childitem $sourcedir -filter *.asd

If ($Filecheck -eq $null) {break}

md $targetDir

set-location -path $sourceDir

$files = Get-Childitem -File -filter *.asd

foreach ($file in $files)
 {
 $strippedFileName = $file.BaseName;
 $extension = $file.Extension;
 $sourceFilePath = $file.DirectoryName;
 $DestinationFile = $targetDir + $sourcefilepath.TrimStart($sourceDir) + "\" + $date + $strippedFileName + $extension;
 Copy-Item $file.FullName -Destination $DestinationFile -Recurse -force
 }

(note that the File copy section was taken from here). Save the above as a .PS1 script and you’re good to go.

That worked well after a lot of testing, but the next problem was getting it to run on everyone’s computer. Using a Scheduled Task means we can configure it to run however often we like and whenever we like, as well as being able to push out the task via Group Policy. However, you can’t run PowerShell scripts silently just by running a PS1 file when triggered from Scheduled Tasks.

Scheduled Task pushed out via Group Policy

There is a great workaround here which uses a VBS file to then trigger the above PS1 script. the VBS component itself runs silently which in turn runs the PS1 script silently. Here’s a copy of the script in case the link goes dead, but please read the original link for more details:

Set objShell = CreateObject("Wscript.Shell") 
Set args = Wscript.Arguments 
For Each arg In args 
 Dim PSRun
 PSRun = "powershell.exe -WindowStyle hidden -ExecutionPolicy bypass -NonInteractive -File " & arg
 objShell.Run(PSRun),0
Next

The final catch is then opening an ASD file when you want to recover something. To open a recovered file, in Word go to File > Info > Manage Document > Recover Unsaved Document (if the Info link is greyed out, open a new blank document first). If you had to navigate away from the default location it shows to open the ASD file, you will probably see this error:


Microsoft Word cannot open this file because it is an unsupported file type

As pointed out here, for some reason Word doesn’t like opening the file unless it’s in the special ‘UnsavedFiles’ location. Luckily you can just copy the ASD file into this folder (which by default is C:\Users\%username%\AppData\Local\Microsoft\Office\UnsavedFiles” ) and then open it as per the above method.

Keep in mind, both the PS1 and VBS files also need to be available to the user, which you may want to also push out by Group Policy. Just make sure the file called by the Scheduled Task exists, or the users will see an error saying the file can’t be found, every single time the script runs.

Update 16th July 2018

I’ve adjusted the script slightly for use in a terminal server environment (RDS or Citrix). The scripts are seperate – yes a master one could be created, detect if it’s a client or server, then run the appropriate parts, but I haven’t done that :)

The only real change is getting the list of logged on users from a broker. I could filter these out to only grab the users on the local box but the script runs within a second anyway and shouldn’t find or do anything if the user profile doesn’t have any backup files.

#Terminal Server Version
#get-date format https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-date?view=powershell-6
$date = get-date -uformat %Y%m%d%H%M

Import-Module RemoteDesktop
$users = Get-RDUserSession -connectionbroker #brokerservername

foreach ($user in $users){

$login = $user.UserName

$SourceDir= "C:\Users\$login\AppData\Roaming\Microsoft\Word\"
$backupdir = "C:\Users\$login\AppData\Roaming\Microsoft\Word\Backup\"
$targetDir = "C:\Users\$login\AppData\Roaming\Microsoft\Word\Backup\$date"

$SourceDirExcel= "C:\Users\$login\AppData\Roaming\Microsoft\Excel\"
$backupdirExcel = "C:\Users\$login\AppData\Roaming\Microsoft\Excel\Backup\"
$targetDirExcel = "C:\Users\$login\AppData\Roaming\Microsoft\Excel\Backup\$date"


Get-ChildItem $backupdir -Recurse | Where CreationTime -lt (Get-Date).AddDays(-2) | Remove-Item -Force -recurse
Get-ChildItem $backupdirExcel -Recurse | Where CreationTime -lt (Get-Date).AddDays(-2) | Remove-Item -Force -recurse

md $backupdir
md $backupdirExcel


$Filecheck = get-childitem $sourcedir -filter *.asd
$FilecheckExcel = get-childitem $sourcedirExcel -filter *.xar -Hidden

If ($Filecheck -ne $null) {

md $targetDir

set-location -path $sourceDir

$files = Get-Childitem -File "C:\Users" -recurse -filter *.asd

foreach ($file in $files)
{
$strippedFileName = $file.BaseName;
$extension = $file.Extension;
$sourceFilePath = $file.DirectoryName;
$DestinationFile = $targetDir + $sourcefilepath.TrimStart($sourceDir) + "\" + $date + $strippedFileName + $extension;
Copy-Item $file.FullName -Destination $DestinationFile -Recurse -force
}
}

If ($FilecheckExcel -ne $null) {

md $targetDirExcel

set-location -path $sourceDirExcel

$files = Get-Childitem -File -filter *.xar -hidden

foreach ($file in $files)
{
$strippedFileName = $file.BaseName;
$extension = $file.Extension;
$sourceFilePath = $file.DirectoryName;
$DestinationFile = $targetDirExcel + $sourcefilepath.TrimStart($sourceDirExcel) + "\" + $date + $strippedFileName + $extension;
Copy-Item $file.FullName -Destination $DestinationFile -Recurse -force
}
}
}

Exchange Online Service Account Options

Going from Exchange On-Premises to Exchange Online can be a bit of a learning curve. One of the changes is having to worry about licensing a lot more; on-prem you can have as many service accounts as you need (e.g. a ticketing application may need access to a mailbox to send and receive emails, or to create IT Helpdesk jobs from emails) and you’re good to go.

With Exchange Online however, every single account needs to be licensed. As per Microsoft’s FAQ on the topic:


Exchange Online is licensed via a subscription model in which each user needs a User Subscription License (USL). Three types of subscriptions are available: Exchange Online Kiosk, Exchange Online Plan 1, and Exchange Online Plan 2. These subscriptions can be purchased on their own or as part of an Office 365 plan that includes SharePoint Online, Skype for Business Online, and Office ProPlus.


Here’s a breakdown of all the license options for Exchange Online, and what features each license has.

Your normal users are probably going to have some sort of business package applied to each user – one of the most common is Office 365 Enterprise E3, but generally not value for money for a single purpose service account.

The Exchange Online Kiosk plan is the cheapest, but limited.  Also note that there’s the Office 365 F1 plan which includes Exchange Online Kiosk, but again is a more expensive package with features you probably don’t need. Although this license can also be used to access another mailbox, there are many limitations such as “Exchange Online Kiosk does not provide access rights for utilization with on-premises servers.” and the ability to access the mailbox using Microsoft Outlook. It also can’t use Exchange Web Services (EWS) which is one of the more modern ways that a developer will read or manipulate emails.

Exchange Online Kiosk has the brief description of “Basic messaging and calendaring plan with Web email and POP access.” If you purely want to send emails via SMTP using Office 365’s SMTP connector, then this is what you need.

For most other functions, you’ll need at least Exchange Online Plan 1. This is the next cheapest option, and gives you a standard fully working Exchange Online account with a fully functional mailbox.

There is another option around all this; if you’re happy to run Exchange Hybrid but have all your mailboxes in Exchange Online, you’re entitled to a free Exchange Server license. With that in place, you can use SMTP relays to allow your on-premises accounts to use that connecter without a license, and have that relay back to Office 365. It’s also possible to do ignoring  Exchange Hybrid if you build your own IIS server and SMTP Relay. Both of these options are great for devices like printers that may be sending emails anonymously, or to avoid changing configuration of all your devices with the new Office 365 SMTP server smtp.office365.com .

As you’ll need to do license management and probably be looking at month to month charges, it’s important to understand licensing and allocate in the most cost effective way. Of course, all this may change so please check official Microsoft documentation to ensure you’re getting what you need.

Update Microsoft’s Documentation Yourself

This might be a strange concept to many people out there. Microsoft is letting you help correct/update/add to their online documentation at https://docs.microsoft.com

I’m typing this from Microsoft’s Headquarters as part of the MVP Summit, and the session is one of the few not under NDA which is a good reason to blog it :) Here’s my summary of the presentation:

Docs.Microsoft.Com is the new platform for Microsoft’s technical documentation across their entire product line for IT Professionals and Developers.

Why contribute?

To share your knowledge, help others and for Microsoft MVPs it adds to your contributions to keep the badge next year. This isn’t to do Microsoft’s documentation for them.

Where to start?

Start small – clarifications, examples (e.g. SDK/PowerShell), guidance tips and translations. If you see something wrong, fix it.

How to do it

You’ll need a GitHub account – https://github.com/join (don’t worry, you won’t need a client – this is all browser based).

Once you’re signed up, you find the article you want to change and choose the ‘Edit’ link on the top right below ‘Feedback’:

Then, you’ll need to click the pen icon (highlighted in yellow) to edit the actual text:

Now you’re able to change the raw text. The documents themselves are in Markdown. This means you’ll need to use characters to modify your text. For example **test** will come out as test. There’s a great cheatsheet here on lots of examples, but for starters follow what you can already see in the documentation rather than trying to create new styles.

You can use the ‘Preview’ tab to see the document with your new changes too. Once you’re happy, at the bottom of the page give a brief description of the change, and click ‘Propose File Change’

After that, you’ll see the final page which shows your change, and the button to ‘Create Pull Request’

You’re done! (For the most part). Your change gets sent off to the document owner for review. You’ll get some emails back advising of the progress, any questions/clarification and in the end, the change approved and your request closed.

It’s a very simple process while making sure the documentation is still Microsoft controlled. Get updating today!