• Adam Thurgar

Linked Servers, SSIS and user name formats


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.


Recent Posts

See All

Hypothetical indexes

We had a query recently about a database that had a lot of objects starting with the name _dta_. We explained that these where hypothetical indexes and statistics that had been created by the Database

Docker update - Linux good

After touching base with a few contacts at Microsoft, I tried a number of suggestions to get the Windows container working with SQL Server 2016 Developer Edition. No success, same error as before. So