How do I connect to a SQL Server on an XP peer-to-peer network?

  • I am trying to develop an Access 2005 "Project" (ADP) application as a "front-end" to a SQL Server 2005 Express database.

    My SQL Server database is stored on my "development" laptop where I am also creating my Access application. No problems on that laptop.

    When I launch a copy of my Access on a "remote" PC using a "run-time" version of Access, my "splash" screen appears OK, but the application fails as soon as it needs to read any data from the database on the "development" laptop.

    Both these computers are running Windows XP Professional and can "see" each other in the usual "peer-to-peer" way. I am logging into each computer using the same login name and password and the user is an "administrator" on each machine.

    My "remote" PC user just can't seem to get "authenticated" by the SQL Server. Does SQL Server work over this Windows XP "workgroup" sort of arrangement, or must is be a "domain" type of setup?

    Although my eventual target location is a "domain" on a Windows 2003 Server network and I have been able to install the database on the server and have a user connect to it OK, I would like to test the multi-user performance on my laptop and PC at home.

    My knowledge of "Logins", "Users", "Schemas" etc is very sketchy but if anyone can help or point me in the direction of a "readable" article I would be very grateful.

    Thanks. Colin.

  • There could be a couple of issues, how are you connecting to the SQL server ? if you are using ODBC then there is a possibility that you do not have the correct setup on your Access Machine.

    Also what sort of login are you using? SQL login or windows authentication?

    As for reference here are a lot of articles on BOL on this subject with some begginners walk-throughs to get you started,.

  • By default SQL Server does not accept remote connections. On the SQL Server box you need to go to the SQL Server Configuration Manager and make sure TCP and/or Named Pipes are enabled.

  • Thanks for the replies.

    I'm not sure whether or not I'm using ODBC to connect from my remote PC; how would I tell?

    I have been into a couple of the SQL Server Configuration Tools programs (Configuration Manager, Surface Area Configuration, Management Studio Express etc) and am sure I have allowed remote connections, have started the SQL Browser Server, am using Windows Authentication etc., but nowhere do I seem to be able to "see" or "enable" my remote PC user. (When I do it on the Server 2003 setup, finding the users and "mapping" them etc., seems fairly straightforward and appears to work).

  • How is the Access application connected to the SQL server?

    Have you linked the tables into Access ? or are you using a connection string in the code?

    or another way?

  • If you have no Domain, then running using Windows Authentication is not going to work. How would SQL Server authenticate the user? You should use a SQL Login.

  • Thanks Jack, I just needed someone to tell me (I hadn't managed to read it anywhere) that Windows Authentication would not work without a Domain. What I had read was that Windows Authentication was the preferred option and I thought (naively and wrongly) that somehow I would be able to "see" the users on another XP machine and allow them to become a "Login" to the SQL Server.

    Once I created a new "SQL Server Authenticated" "Login" to the Server and changed my Access Project's "Data Link Properties" to connect via that "SQL Server Login" rather than "Windows NT Integrated Security" I was able to connect to the Server with my "run-time" application on my "remote" XP machine.

    Thanks for making that clear. Now all I need to do is cope my regular bugs.

    Cheers. Colin.

  • Jack Corbett (1/16/2009)How would SQL Server authenticate the user?

    How about this

  • Vadim Rapp (1/19/2009)


    Jack Corbett (1/16/2009)How would SQL Server authenticate the user?

    How about this

    So it can be done, but don't miss this line:

    Both methods are not recommended.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply