locked
PROBLEM CONNECTING TO SQL 2008 EXPRESS WITH CLASSIC ASP ON SMALL PRIVATE NETWORK RRS feed

  • Question

  • I created a private wireless network (no internet connection) of four workstations, each running an installation of SQL 2008 Express.  All four laptops are running Windows 7 Professional.  I created a HomeGroup for this private network.  While I don't care about file sharing between the four workstations, I do need three of them to be able to connect to the SQL 2008 Express database on the fourth laptop via connection strings in classic ASP programs.  Each of the four laptops can see the others in the network.  I can ping the ip addresses from each to the other with no problems.  When I try running the ASP program on the three laptops that attempt to connect to the SQL database on the fourth, I get the Server does not exist or access denied error '80004005'.  The same program can also run on the fourth laptop and connects to the SQL database with no problems.  I've tried using SQLOLEDB and SQLNCLI10 as the provider in the connection string on the other three.  That fails.  I've tried adding the IP Address of the laptop where the SQL Database resides as the Data Source in the connection string on the other three and that still fails.  TCP/IP and Named Pipes are enabled.  Each laptop can connect to the instance of SQLEXPRESS running on each, but I need the three to be able to connect to the instance of SQL Express running on the fourth.  Any ideas what I may be doing wrong?  This connection string works fine in the ASP program to connect to the local database on each laptop:   

    Connection.Open "PROVIDER=SQLOLEDB;Server=ComputerName\Sqlexpress;UID=MyUser;PWD=MyUserPassword;DATABASE=MyDatabase"

    Nothing seems to work for the Connection String that the other three laptops need to use to connect to this database on the fourth laptop.

    Sunday, April 19, 2015 12:32 AM

Answers

  • Actually, what I had to do is go in to SQL Configuration Manager on the computer that I wanted the instance of SQL 2008 Express to be accessible from the other workstations on the network, click SQL Server Services, then double click the SQL Server entry and change the BUILT-IN account from Local to Network Service.  Then, I was able to see that instance of SQL Server from SQL Management Studio on the other workstations as a network server.  After that, the ASP connection string worked perfectly.  Thanks for all of your help with this.  I would never have guessed to even look there, but with all of your other suggestions, I stumbled across it and gave it a shot.  I'm a DJ and host karaoke sessions.  I set up this private network so that participants can use the computer workstations to search my karaoke library in the SQL Express instance on the console computer and automatically send requests to that console computer from the other workstations.  That gets rid of the manual paper request cards.  It also prevents me from having to backup the database and install the backups on all the workstations every time I update the database, which is quite frequently.  Works like a charm now.  Thanks again.   
    • Marked as answer by Stan210Editor Monday, April 20, 2015 5:39 PM
    Monday, April 20, 2015 4:53 PM

All replies

  • i never tired this on wireless networks... but it appears that none of computers can see each other....

    they all need to see each other - for this set up to work... is any particular error message you were getting

    I would first validate the connection using sql server management stuido.

    what happens when you launch sql management studio on computer1 and connect to computer4, can you connect? if not, what is error message.. see this link, if it is similar error 

    http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

    make sure you disabled the firewall on all computer(for testing, later you can add exception for the port sql is running and re-enable firewall)

    run sql browser service on all computer4 - configuration manager- sql browser

    enable remote connections.

    enable tcp/ip connections.

    make sure sql authentication is enabled on computer4  sql server - after making the change - restart the box.

    check now, if you connect from ssms, if you can, you should be able to do that from the connection string as well.


    Hope it Helps!!

    Sunday, April 19, 2015 1:24 AM
    Answerer
  • Actually, what I had to do is go in to SQL Configuration Manager on the computer that I wanted the instance of SQL 2008 Express to be accessible from the other workstations on the network, click SQL Server Services, then double click the SQL Server entry and change the BUILT-IN account from Local to Network Service.  Then, I was able to see that instance of SQL Server from SQL Management Studio on the other workstations as a network server.  After that, the ASP connection string worked perfectly.  Thanks for all of your help with this.  I would never have guessed to even look there, but with all of your other suggestions, I stumbled across it and gave it a shot.  I'm a DJ and host karaoke sessions.  I set up this private network so that participants can use the computer workstations to search my karaoke library in the SQL Express instance on the console computer and automatically send requests to that console computer from the other workstations.  That gets rid of the manual paper request cards.  It also prevents me from having to backup the database and install the backups on all the workstations every time I update the database, which is quite frequently.  Works like a charm now.  Thanks again.   
    • Marked as answer by Stan210Editor Monday, April 20, 2015 5:39 PM
    Monday, April 20, 2015 4:53 PM