2

I have a WinForms program I am creating for a friend of mine that uses a SQL Server Express database. Locally, I can connect to my SQL Server Express fine and when I deploy the app to his computer, it works also. I'm having difficulty connecting to his SQL Server Express instance from my machine though (I'm trying to run the program in debug mode in vs2012 but connected to his database). The program uses Entity Framework in case that matters (I don't think it does).

We've setup his firewall to allow my IP address to access his computer and his SQL Server... so I can log in via remote desktop and I can also connect using SSMS from my pc and see all the databases.... but why can't I connect using vs2012? I'm thinking it has something to do with the connection string but haven't found a working solution yet.

Here's what I have tried:

Got these from ConnectionStrings.com:

Server=100.100.100.100\SQLExpress;Database=TestDB;User Id=UserID;Password=myPassword;

DataSource=100.100.100.100\SQLExpress;Database=TestDB;User Id=UserID;Password=myPassword;

Obviously the IP address has changed for the purposes of this post.

Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Robert
  • 1,696
  • 3
  • 36
  • 70
  • How do you mean you set up firewall? You connect to his computer on same network or over NAT device (router)? – Aleksandar Toplek Jul 31 '13 at 00:33
  • Oh and what are these connection strings? EF creates custom string for you when creating tsql script, thay are in app.config file of project – Aleksandar Toplek Jul 31 '13 at 00:35
  • He 'setup' the firewall so that I can connect to his computer, both on different networks (NAT device). The 1st connection string is what EF generated (but I generated it locally, not using his IP address), so I thought I could just change the IP address and be ok, but that's not the case unfortunately. – Robert Jul 31 '13 at 00:42
  • 1
    What does "having difficulty" mean? Do you get an error message? If so, what is it? Did you try `Data Source` instead of `DataSource`? Did you verify that his SQL Browser service is running? Is SQL Express set to allow remote connections? – Aaron Bertrand Jul 31 '13 at 00:53
  • @AaronBertrand Yes, the error message is "The underlying provider failed to open" (this is an EF error). Yes his instance of SQL is running and is allowing remote connections, as I verified that by connecting to it via SSMS as mentioned originally. I have not tried `Data Source` and will give that a go. Thx. – Robert Jul 31 '13 at 00:59
  • If you are trying to connect to a pc on another network, you need to >>setup<< port forwarding on client router. Take a look at which ports are used by server and forward them to any ports you want (same ports would do...) then change connection string as following 192.168.1.101:123 123 is port you set and ip address should be public ip address that provider gave to the client. oh i almost forgot... are you setting cs in code or via app.config? – Aleksandar Toplek Jul 31 '13 at 01:01
  • try [link](http://blogs.msdn.com/b/dataaccesstechnologies/archive/2012/08/09/error-quot-the-underlying-provider-failed-on-open-quot-in-entity-framework-application.aspx) for the error you mentioned. – Nilesh Jul 31 '13 at 02:31
  • Duplicate of [Enable remote connections for SQL Server Express 2012](http://stackoverflow.com/questions/11278114/enable-remote-connections-for-sql-server-express-2012) - SQL Server **Express** doesn't allow remote connections "out-of-the-box" - you need to specifically enable this capability - which is shown in the answer to this other SO question – marc_s Jul 31 '13 at 05:33
  • 1
    @AaronBertrand - Thank you for your suggestion as it was the problem. I was using `DataSource` not `Data Source` and that cleared up the problem. If you post it as the answer, I can mark it so you get credit. – Robert Jul 31 '13 at 17:23
  • @marc_s - clearly this is not a duplication of that question. In my question, I state that this is a WinForms app that's having issues connecting, not anything else. I stated that I could make a connection to the SQL Express Server from my machine using Remote Desktop AND SSMS....so it wasn't that I couldn't connect to this database at all, it was that I couldn't via a WinForms program I created using C# and vs2012. – Robert Jul 31 '13 at 17:26

2 Answers2

1

Here is a list of things you need to check on the other computer:

  • Is TCP/IP protocol enabled? Go to SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for {instance}
  • What IP addresses are enabled for listening in configuration manager? Go to TCP/IP properties -> IP Addresses tab
  • SQL Server browser started
  • Firewall set properly – you want to enable TCP and UDP traffic on port 1433
  • Server allows remote connections? In SSMS open properties for that instance and check Connections tab.
Dwoolk
  • 1,491
  • 13
  • 8
1

You've used the connection string attribute:

DataSource

There is no such thing, and I suspect it was just a typo (it pays to use cut and paste instead of transcribing). There is actually a space in that attribute, so it should be:

Data Source
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490