I have spent most of the past 5 to 6 years upgrading SQL Servers.

Upgrading from SQL Server 2000 (and prior), 2005, 2008 and 2008R2 to SQL Server 2012, 2014, and 2016.

From physical to virtual.

From in-house to private and public clouds.

From old data centres to new data centres.

Or a combination of some or all of these.

During these upgrades I have come across some very peculiar issues.

The following are two issues that were resolved by the same workaround. In reality we don't know the root cause, we just fixed it and moved on.

Firstly you need to understand a little about User Name Formats.

The User Principal Name (username@domainname.com) and the Down-Level Logon Name (DOMAIN\username).

https://msdn.microsoft.com/en-us/library/windows/desktop/aa380525(v=vs.85).aspx

In the first case we had a linked server that would not work with the remote user and password. The remote user (@rmtuser) was in the UPN format. When it was changed to the Down-Level Logon Name format it worked.

The second case was the SSIS service not starting. Once again this was in the UPN format, as you would expect for a service account, but once again it only worked when it was changed to the Down-Level Logon Name format.

All of this may have been caused by having old domains and AD's but we didn't have time to investigate further, it was onto the next upgrade.