How to windows authenticate Windows 8.1 Microsoft Account With Sql Server 2012

Question

I have set up a little network with windows workgroup. My pc (windows 8) connected with server (sql server 2012) using windows authentication. Of course I had a local account and not the microsoft account and a pure mapping of the accounts did the job.

With the upgrade to windows 8.1 and in order to use skydrive I must use a Microsoft account. How can I connect to sql server using windows authentication and using this Microsoft account?

Frieder

Does logging in with a Microsoft account actually create a Windows user locally on the machine? If so, you need to give permissions to that user, if not, then I don't think it will work. – Desulphurize

I think so. I have a local user that is different from the Microsoft account (actually it is the first name)! I have tried to create the user with the same name on my sql server but it didn't work (something about note registered domain). – Frieder

Answer 1

If you are able to control the method that the client connects to the SQL Server, and have control of the SQL Server, then it might be best to switch the server to SQL Server and Windows Authentication mode instead of just Windows.

However, if you require the clients Windows login in order to differentiate the users accessing the SQL Server, then you may be out of luck. If the SQL Server is configured to only allow Windows Authentication, then it will only accept connections from the Windows logins defined on the Windows server - if the server is in workgroup mode and not domain mode.

The workaround for this has always been to ensure that a Windows account exists on the client and server with the same name and password, but if you are now required to use a Microsoft Account (formerly known as a Live Account) as your Windows login on your desktop I don't see how you would give that account access rights on the SQL Server.

Insignificant

This means that if some dev want to use windows 8.1 he should use local account and kiss skydrive goodbye and the rest of the cloud integration of the os. Thank you microsoft for that. I just thought that using this new account would create some hidden local account that i could use for authentication with sql server. Thanks anyways – Frieder

Answer 2

I have figured out a work around for this issue.

First create a local account that will work with your sql server. Then switch to your Windows Live account (a seperate user) and modify the shortcut's target.

C:\Windows\System32\runas.exe /user:domainName\localAccountName /savecred “pathToExecutable”

This should work for any program that needs to use Windows Authentication.

Putrescent

Answer 3

Actually there is a way you can add credentials to the "Microsoft account" the same as adding credentials to accounts in previous versions.

Not sure if this will work for you but it works for me with file share access over a network.

Under Control Panel Open the User Accounts and Family Safety section, then go to User Accounts. Your Microsoft account will be listed. From the left side click Manage your Credentials.

You can add Windows Credentials in there as well as a lot of other credentials.

Hope this helps

Stonewort

hi, can you help me what exactly to type in for internet or network address, username? – Frieder

in ControlPanel-> Credentials, in the username you will have MicrosoftAccount/<username>. I tried changing it to LocalAccount/<username> but this did not work either. I too thought we could enjoy the MS Account eco system, not so, we have to use local account with DropBox or similar for sharing files. I doubt Server 2016 will include Microsoft Accounts but lets hope they some how get the two to talk and authenticate. – Matejka

Answer 4

When using a Microsoft Account to logon in Windows, a local user is created. The name of the local user is your first name that is specified on the Microsoft account.

So if your first name is Eric, try connecting to SQL Server with Eric and your Microsoft Account's password.

Allanson

windows and sql server are on a different machine so this does not work. local user name is not the first name, at least not in my installation. it is the first letter of my first name and the 4 first letters from my last name. – Frieder