SQL Srv 2016: Login failed for user 'MicrosoftAccount\...'

Question

I have Windows 10 Pro machine with SQL Server 2016 Dev installed. There are two local user accounts on the machine: [WORK\admin] and [WORK\erop]. [WORK\erop] account was created at first without connecting to Microsoft. But later I connect it to my MS account. [WORK\erop] account was added to sysadmin role when installing SQL Srv.

Now I try to connect to SQL Srv instance with SSMS as [WORK\erop] but receive:

Login failed for user 'MicrosoftAccount\<my_MS_account>'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] and Error: 18456, Severity: 14, State: 5. which means "Invalid userid".

As I guess SSMS is launched under MS account and since MS account has no login on SQL Srv instance it refuses connection.

I executed

CREATE LOGIN [MicrosoftAccount\<MS_account>] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [MicrosoftAccount\MS_account]
GO

and everything is OK. But I wonder are there any smarter way to map MS_account to local user that has a login on SQL Srv instance?

Answer 1

I wouldn't call any solution smarter than yours, depends on your situation and what you want. But alternatively you could have reverted to a local user, assuming you don't care about the OneDrive and such functions.

Start > Avatar button > Change account settings > Your email and accounts > Sign in with local account instead.

But adding the login again is the smartest and easiest solution.

Answer 2

For those who tried the solution above but are still encountering this issue: change your connectionstring within your appsettings by replacing the Server=localhost value with the server name you see when logging onto MSSQL Management Studio.