SQL server windows authentication through network

Question

I am using sql server 2008 r2.There are about 50 pc in my office connected in network by using LAN.In a sql server 2008 r2 express(installed in windows 8) i have created all users windows account with same name as their name in their PC and then created windows authentication user in sql server. Now all the users are able to connect to sql server using windows authentication through SSMS.

But now i am trying implement same for the another SQL server 2008 r2 express which is installed in windows xp sp3. So i created one windows authentication user of network PC. But it is not working when i try to connect to sql server using windows authentication from network pc message comes like "Login failed for user 'PC91\Guest'. " It is not recognizing the windows account of my pc. why it happens? Please tell me a solution.

UPDATE:

SQL server and browser is running and sql server is already configured to use remote connections.Also i can ping the PC.

My doubt is is it allowed to connect sql server in another pc by creating same account as in sql server installed PC?

Let me explain what i did below:

In sql server installed pc(windows XP) i created windows login 'test'.Then i created sql windows authentication login using 'test' windows account.

In another PC i created windows user account 'test'(same as in pc having sql server,with same password) so that i have same user account in both PC. Now from this PC ,i connect to sql server of another PC by using windows authentication('test'). But it worked properly for sql server which is installed in windows 8 and windows 7.But for the sql server installed in XP it didn't work(I tried in two instance in two different windows XP machine). So i doubt if this feature is allowed in windows XP or i am going wrong in anyway(may be any settings missing)?

• user507: Can you connect using Windows Authentication locally on the XP machine (same one SQL Express is loaded on)?
• IT researcher: Yes.In that(local pc) i can connect using windows authentication.But from network pc its not working.
• user507: Can you provide a bit more information in your question on your environment? Are you in a domain? What type of application are you using from the "network pc"? If it is a web application can you provide the connection string?
• IT researcher: @ShawnMelton I am not using domain. I am just using network connection by using LAN. All users use the sql server by using SSMS, so no question of connection string.
• Mark Storey-Smith: Is PC91\Guest an account you created or the built-in guest account? If the later, I don't think what you're trying to do is possible. Create a standard user account instead.

Answer 1

Do the username and password match between the network pc and the sql machine? I also found this link regarding, xp home. I am not sure what version of XP you are using.

https://serverfault.com/questions/54761/pass-through-authentication-does-not-work-with-a-specific-account-in-windows-xp

• IT researcher: username and password is proper.I have checked it.
• Mark Storey-Smith: @ITresearcher What about Adam's other point regarding XP Home? Is the problem machine Home or Professional edition?

Answer 2

If you have 50 PCs trying to connect, you might be hitting the limit for connections into a XP machine. The limit is 10 connections. http://support.microsoft.com/kb/314882

Also, for your machines to authenticate to the XP machine, they need to connect via the network before they connect to SQL server. SQL Server usually re-uses existing credentials instead of initiating an authentication handshake. Try this: From the XP box, try creating a share (blank). Then map a drive to the XP box. This will make the machines authenticate at a network level and ensure that the login/pw is correct (permissions, etc).

• IT researcher: I think i need to update question. Now for this sql server i created only one windows authentication connection from network pc.But it is not connecting.My plan was to connect number of pc by using windows authentication as i did the same for sql server which is installed in windows 8.