[RESOLVED] connect to SQL Server from home fails (working fine at work)
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: [RESOLVED] connect to SQL Server from home fails (working fine at work)

  1. #1
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    [RESOLVED] connect to SQL Server from home fails (working fine at work)

    Hi guys,

    I'm facing the following problem. I created a winfor app which is in use for a year now. Since last month, some employee's are migrating from a desktop pc to a notebook (which they also take home).

    When they are at the office, the app works just fine. But when they are at home, the connection to the SQL server can't be established
    IBatisNet.DataMapper.Exceptions.DataMapperException: Unable to open connection to "Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0".
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
    The Sql server is configured to accept remote connections (otherwise it would not work at the office as well).

    Any ideas what is causing this?

    (SQL Server 2005 Express Edition )

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,473

    Re: connect to SQL Server from home fails (working fine at work)

    The IP address for the SQL server at home is different than when they're in the office. Or if it isn't, see if they can ping the sql server while at home.

  3. #3
    Join Date
    Jun 2001
    Location
    Melbourne/Aus (C# .Net 4.0)
    Posts
    686

    Re: connect to SQL Server from home fails (working fine at work)

    When you are at home on your PC, are you able to access the SQL Database (at work) using the SQL Server Management Studio?
    Rob
    -
    Ohhhhh.... Old McDonald was dyslexic, E O I O EEEEEEEEEE.......

  4. #4
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: connect to SQL Server from home fails (working fine at work)

    Quote Originally Posted by Arjay View Post
    The IP address for the SQL server at home is different than when they're in the office. Or if it isn't, see if they can ping the sql server while at home.
    The IP address is the same in both locations. In the connectionstring, as datasource I'm using mydomain.com//SQLEXPRESS. Pinging mydomain.com results at both places to the same IP.

    Quote Originally Posted by rliq
    When you are at home on your PC, are you able to access the SQL Database (at work) using the SQL Server Management Studio?
    I haven't tried that yet. Will try it when I'm at home tonight.

  5. #5
    Join Date
    May 2007
    Location
    Denmark
    Posts
    623

    Re: connect to SQL Server from home fails (working fine at work)

    Unless there is some Port Forwarding or Network Address Translation set up, I can't see how the SQL Server would be accessible from outside the company network (really, it shouldn't be, unless something is configured!)
    So if your users aren't using a VPN client or have a hardware VPN-tunnel configured then it should be no surprise that the database isn't accessible to them when they are not at work.
    It's not a bug, it's a feature!

  6. #6
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: connect to SQL Server from home fails (working fine at work)

    Quote Originally Posted by rliq
    When you are at home on your PC, are you able to access the SQL Database (at work) using the SQL Server Management Studio?
    It results in exactly the same error.

    Quote Originally Posted by foamy View Post
    Unless there is some Port Forwarding or Network Address Translation set up, I can't see how the SQL Server would be accessible from outside the company network (really, it shouldn't be, unless something is configured!)
    So if your users aren't using a VPN client or have a hardware VPN-tunnel configured then it should be no surprise that the database isn't accessible to them when they are not at work.
    Why is Port Forwarding necessary? The IP of the domain leads directly to the server.

    Network Address Translation... this is new for me...

    The company doesn't want to setup a VPN network.


    What should I do to make it accessible from home..?

  7. #7
    Join Date
    May 2007
    Location
    Denmark
    Posts
    623

    Re: connect to SQL Server from home fails (working fine at work)

    Quote Originally Posted by dannystommen View Post
    What should I do to make it accessible from home..?
    You would need to configure the company network to forward traffic on a specific port (1433 by default) to your SQL Server. If nothing is configured, it will not be accessible from outside the network. I'm not an expert in this sort of configuration (since I'm a developer :-P), but it is necessary if the company does not have some kind of VPN or similar Virtual Connection setup.
    It's not a bug, it's a feature!

  8. #8
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,473

    Re: connect to SQL Server from home fails (working fine at work)

    Generally, due to security concerns, it's not considered best practices to expose SQL server directly to the outside world.

    Instead, you generally expose a web service (or WCF service) that accesses the db and expose the endpoint externally.

  9. #9
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: connect to SQL Server from home fails (working fine at work)

    Quote Originally Posted by foamy View Post
    You would need to configure the company network to forward traffic on a specific port (1433 by default) to your SQL Server. If nothing is configured, it will not be accessible from outside the network. I'm not an expert in this sort of configuration (since I'm a developer :-P), but it is necessary if the company does not have some kind of VPN or similar Virtual Connection setup.
    Our network administrator says that technically it doesn't make any difference if the application is inside or outside the companynetwork. But seems that something is still blocking calls from outside the network. He though t it had something to do with the dynamic port SQL server was using, so I disabled that en set it to the default 1433. Still doesn't work.

    Quote Originally Posted by arjay
    Generally, due to security concerns, it's not considered best practices to expose SQL server directly to the outside world.

    Instead, you generally expose a web service (or WCF service) that accesses the db and expose the endpoint externally.
    I agree, but it's not an option at this time. It's quite a large application, some migrating to webservices would take a while.


    Any other suggestions?

  10. #10
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,473

    Re: connect to SQL Server from home fails (working fine at work)

    Quote Originally Posted by dannystommen View Post
    Any other suggestions?
    Were you able to connect at home using Sql Svr Management Studio?

  11. #11
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: connect to SQL Server from home fails (working fine at work)

    No, exact the same error!

  12. #12
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,473

    Re: connect to SQL Server from home fails (working fine at work)

    Are you using integrated security to connect or sql security?

    If using integrated sec, try using Sql security (specify a un and pw in sql).

  13. #13
    Join Date
    May 2007
    Location
    Denmark
    Posts
    623

    Re: connect to SQL Server from home fails (working fine at work)

    Quote Originally Posted by dannystommen View Post
    Our network administrator says that technically it doesn't make any difference if the application is inside or outside the companynetwork.
    That's wrong. There's a router or firewall you have to get through when you're outside the company network and there isn't one when you're inside. That's the difference, and if this router/firewall isn't configured to allow the SQL connection then you won't be able to connect.
    It's not a bug, it's a feature!

  14. #14
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: connect to SQL Server from home fails (working fine at work)

    Quote Originally Posted by Arjay View Post
    Are you using integrated security to connect or sql security?

    If using integrated sec, try using Sql security (specify a un and pw in sql).
    The connectionstring I'm using:
    Code:
    Data Source="mydomain.com\SQLEXPRESS";Initial Catalog="some_DB";User ID="user";Password="pass";
    So I assume that I'm not using integrated security.

    Quote Originally Posted by foamy
    That's wrong. There's a router or firewall you have to get through when you're outside the company network and there isn't one when you're inside. That's the difference, and if this router/firewall isn't configured to allow the SQL connection then you won't be able to connect.
    The firewall of the server is disabled. But I think you could be right. When using http://www.yougetsignal.com/tools/open-ports/ (a simple port checker site), and I try to connect to port 1433 on my server, it says that the port is closed. I'll contact the network administrator after the weekend.

  15. #15
    Join Date
    May 2007
    Location
    Denmark
    Posts
    623

    Re: connect to SQL Server from home fails (working fine at work)

    Just to be clear: I'm not talking about a software firewall installed on the server, but a hardware unit that needs to be configured to forward traffic on a specific port to the IP address of the server.
    It's not a bug, it's a feature!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center