Find the location of MS SQL MDF and LDF files

Run the following query against the Master DB to return the location of User DB file locations. You can then select all in the results pane and paste into Excel.

select name,physical_name from sys.master_files where database_id > 4

 

IIS FTP User Isolation with local user accounts has a very manual step that will fuck you up

As it is too manual to remember how to configure FTP on IIS in Windows Server 2012 R2 and above. I decided to document the main catch for me.

Big catch, using local users you turn on FTP User Isolation and everything stops working, you turn off isolation and the remote connections work again.

Make sure your FTP site has a virtual directory named 'LocalUser' in the root folder. Your user virtual directories will go in there.

The folder and the virtual folder MUST be named 'LocalUser' you are not allowed to call it anything else or connections will fail.

This needs to be reflected in the file-system. For example. C:\inetpub\FTPSite\LocalUser\Username1

Convert Waymarked Trails to Garmin Courses

Who could have guessed this would be so difficult.

I bought a Garmin Edge 520 Plus so I can navigate the excellent cycle routes on Waymarked Trails.
Here is london for example. https://cycling.waymarkedtrails.org/#?map=12!51.5217!-0.1022

So the journey of learning new terminology begins, so do the thoughts of sending it back...

In the end (after 2 hours) I found a way.

What we are looking for is a Training Course (Garmin seems to think we are all athletes).

Go to Waymarked Trails and pick a trail, we will use London's CS3 as an example. Click the route to highlight it and you will see a panel on the right as below.




Click on the GPX button in the panel on the right to download the GPX file.
You will need to convert this with the correct options for it to be acceptable and complete.

Now go to https://www.alltrails.com/converter (you might need to login, you can use http://bugmenot.com/view/alltrails.com to find a login if you don't want to sign up).

Upload your GPX file on Alltrails and pick the following options.
Convert to: Track / Route
Download as: GPX Track
Route Options: Combine multiple routes into one route



Click Convert file! (there is a short delay then the file will download)

You are now almost there

Go to https://connect.garmin.com/
Click Training
Click Courses
Click Import


Browse for your newly converted GPX file (in our case cycle-superhighway-3 (1).gpx)
Click Get Started
Select a Course Type (Road will do)
You will see the Route on the map!
Edit the name with the pencil icon and click Save New Course.
You can now use the Send to Device option to transfer this to your Garmin.

So why the AllTrails bit in the middle, without this the file will not import, without the combine option you will not see the full route. Why is this so difficult on a device that is suposedly the best?

Turn off hibernation in Windows

Run in Admin mode CMD: powercfg /h off

McAfee Agent cannot be removed while it is in managed mode

Run the following in CMD running as Administrator

"C:\Program Files\McAfee\Agent\maconfig.exe" -provision -unmanaged

DMARC described in one sentence

The sender's DNS stipulates that messages should contain a cryptographic proof of a valid sending server, when DMARC fails the messages did not contain this.

Remove 365 licenses from PC to resolve license issues

1.       Find the office install directory.  Should be here C:\Program Files (x86)\Microsoft Office\Office16.

-          There should be a VBS Script in the directory called ospp.vbs

      

2.       Sign out of Office – Make sure you sign out and remove anything office related in the Credential manager. Then close all office app including skype for business, Onedrive if running.

3.       Run CMD as Administrator and change to this directory C:\Program Files (x86)\Microsoft Office\Office16

The command below gets the product key or keys:

4.       Run the script – “cscript ospp.vbs /dstatus”

Run this command to remove the product key or keys if multiple keys is present. – Make sure to sign out of office first if not you will soon find out why.

5.       Remove the license - “cscript ospp.vbs /unpkey:8HRTP”

6.       Run this again “cscript ospp.vbs /dstatus” to double check the license is removed.

7.       Open word or excel and sign in with the account you want to license Office with.

Create user in Azure hosted SQL

Login to SQL Management Studio as the Azure defined SQL Admin

Change the items in red to those that you require.

In the Master Database
CREATE LOGIN username WITH password='password';

CREATE USER username FROM LOGIN username;

In the User Database.
CREATE USER username FROM LOGIN username;
EXEC sp_addrolemember N'db_owner', N'username'

For Read-only

CREATE USER username FROM LOGIN username;

EXEC sp_addrolemember N'db_datareader', N'username'

Open File, Print and RDP, enable RDP on next boot on a workstation via CMD - Domain profile only

netsh advfirewall firewall set rule name="File and Printer Sharing (SMB-In)" dir=in profile=domain new enable=Yes

netsh advfirewall firewall set rule name="Remote Desktop - User Mode (TCP-In)" dir=in profile=domain new enable=Yes

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server" /v fDenyTSConnections /t REG_DWORD /d 0 /f

Creating a SQL user on a SQL server set up for Secondary log shipping

Create the SQL user on the Primary SQL server and assign User Mapping, allow log shipping to occur.

On the Primary SQL server run the following, it will return a SID...
select name, sid from master..syslogins where name in ('SQLUser')

On the Secondary SQL server run the following using the SID generated on the Primary SQL server.
sp_addlogin 'SQLUser', @passwd='SQLUserPassword', @sid=0x01234567890123456789012345678901

You will now see the same user with the same User Mapping on both Primary and Secondary SQL servers, the user on the secondary will match to the permissions of the Primary server user.

powerShell 365

Connect to Office 365 PowerShell
$LiveCred = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic -AllowRedirection
Import-PSSession $Session


Connect to Azure PowerShell
$LiveCred = Get-Credential
Connect-MsolService -Credential $LiveCred

Import-Module Azure


Set the calendar permissions of another user: 
set-MailboxFolderPermission -Identity alias:\calendar -User user@example.com -AccessRights Owner


Set the Default user on every mailbox calendar to Reviewer
$users = Get-Mailbox | Select -ExpandProperty Alias
Foreach ($user in $users)
{
Write-Host $user
set-MailboxFolderPermission $user":\Calendar" -user default -accessrights reviewer
get-MailboxFolderPermission $user":\Calendar" -user default
write-host ""
}

Find where a mailbox alias is in use
Get-Mailbox -Identity * | Where-Object {$_.EmailAddresses -like 'smtp:alias@example.com'} | Format-List Identity

Get details of all folders in all mailboxes
Get-Mailbox | Select-Object alias                                            `
| foreach-object {                                                           `
    Get-MailboxFolderStatistics -Identity $_.alias                           `
    | select-object Identity, ItemsInFolder, FolderSize, FolderAndSubfolderSize }

Remove forwarding on a mailbox
Set-Mailbox bob -ForwardingAddress $Null
Set-Mailbox bob -DeliverToMailboxAndForward $False

Recover all recently deleted items from retention
Restore-RecoverableItems -Identity alias

Create a CSV of AD users and their managers

Run the following in PowerShell changing the path at the end to suit your needs, the output can then be sorted by the Manager column in Excel.

get-aduser -Filter * -Properties Manager | Select-Object Name,sAMAccountName,Manager | export-csv C:\Scripts\UsersWithManagers.csv -notypeinformation