0

This has been one annoying piece of needle in a haystack.

The C# application is practically complete and I need to test the cooperation of two or more different machines on the same network, to see if they display the same data from the database.

When I run SQL Server + Visual Studio together during development, there's no problem, I use this connection string and everything works OK:

'@"Data Source=PCNAME\SQLEXPRESS;Initial Catalog=POS;";'

I have now published the app and installed it on another machine that happens to be on the same network. Obviously the connection string will not work on that machine because its name is not PCNAME, it's 2NDPC.

But I doubt the answer is to open the solution, edit the cnx string and re-publish for each machine. Even then, how will they be using the same database?

TL;DR **What I need ** I want to use the machine I developed the application on initially to be hosting the database, to which the local machines can connect.

I've tried forwarding 1434 port and making rules, I've tried starting tcp/ip process in Server Configuration and making sure SQL Browser Agent Service or what not is running.

But no, I either get and error#25 or nothing happens.

Please help and tell me in explicit steps how I can achieve this goal.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Barry D.
  • 549
  • 2
  • 6
  • 21
  • dupe. This has been answered many times before. https://msdn.microsoft.com/en-us/library/ms345343.aspx http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx http://stackoverflow.com/questions/2486610/how-to-connect-to-sql-server-from-another-computer – Jeremy Aug 17 '15 at 11:18
  • I said in my post I've tried, and I've seen those posts, but I'm not getting the same response. My entire is inquiry is to get help with knowing what I'm possibly doing wrong. – Barry D. Aug 17 '15 at 11:30
  • you mention port 1434. is that a typo, or the real port? the default port is 1433. aslo, make sure you've done EVERYTHING from the msdn article, including enabling TCP connections. One thing to test is to install SSMS (SQL Server MAnagement Studio) on PC2, and connect to your instance on PC1 from that. Also what security are you useing? built in windows, or username and password? if using built in windows, are your computers on the same domain, and does the user exist for both computers? – Jeremy Aug 17 '15 at 11:35
  • 1433 sir, I've enabled TCP connections. I do indeed have SSMS on both machines and it's how I test between them. I'm not too sure about security as of yet. All I know is that I Integrated Security=True on my connection string. Yes, the computers are on the same domain, but no, the user does not exist for both computers. That's why sometimes I get an error saying PCNAME/guest cannot login. – Barry D. Aug 17 '15 at 11:41
  • That is your problem then. I would setup a SQL Server login, and use that login to connect in your connection string. from http://www.connectionstrings.com/sqlconnection/ use a connection string like Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword; – Jeremy Aug 17 '15 at 11:44

3 Answers3

1

1) Enable TCP/IP in the SQL Server Configuration Manager 2) Make sure SQL Server Browser is running, if not, start it, if you can't, right click > properties > service tab > Start Mode = "Automatic"

This is what (excluding people recommending firewall solutions, different programs/sites and downloading stuff to achieve this) I was missing, that nobody managed to tell me.

So .. depending on what you've been doing recently, you may or may not have changed some default values here and there. I'm not too sure if this is default, or if it happened with my win10 upgrade, but either way this worked.

3) In SQL Server Configuration Manager, right click on your TCP/IP item, hit properties, swtich to the ip addresses tab and put in 1433 in the tcp port field under IP1 and IPALL

Barry D.
  • 549
  • 2
  • 6
  • 21
  • just for the record, the first item in the first link I sent you in my first comment tells you the steps to do this, which is why I asked if you read it carefully. https://msdn.microsoft.com/en-us/library/ms345343.aspx read again for your reference. – Jeremy Aug 17 '15 at 15:34
  • I appreciate your help, man. Take it easy. :) Who's to say I didn't learn the above from your links? I just meant nobody told me this directly to emphasize that it may be tricky to find this answer, hence why I reposted it. – Barry D. Aug 17 '15 at 15:43
  • I'm glad you got it working. My only issue is that you specifically stated that you enabled TCP connections, when you clearly hadn't. It is very difficult to help someone when you are not accurate with statements. – Jeremy Aug 17 '15 at 16:00
  • +Jeremy I had enabled it to the limit of my knowledge, which was to forward the necessary ports on firewall. I had no clue about the right clicking part. Most answers online about the issue didn't cover that method, just firewall. Apologies if you felt miscredited or anything else. & Thanks again for all your help. Take care, bye. :) – Barry D. Aug 17 '15 at 16:04
0

Instead of pcname, try using TCP/IP address. PCNAME is Netbios dependant. You also need to make firewall adjustments, make sure SQL server is allowed for remote connections and listening on correct port (although 1433 is the default port, you are not guaranteed to have the correct instance on that port). I have created and published a four part video series on this subject. Although it is about installing our application, 3 out of 4 parts deal with installing SQL server on the host, configuring it for remote access and accessing from clients. Check if you wish, here is the link to the 1st one:

AccuSQL Installation part 1

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • I tried forwarding ports & using IP like this @"Data Source=\\192.168.1.2;Initial Catalog=POS; Integrated Security=True"; But then it throws an Error 25 saying my connection string is invalid. – Barry D. Aug 17 '15 at 11:28
  • It doesn't look like a correct connectionstring to me, It is more like a shared folder mapping. It should look like: server=192.168.1.2\InstanceNameIfAny;Database=pos;uid=userName;pwd=password To use integrated security you need to create logins for remote connections too. – Cetin Basoz Aug 17 '15 at 11:31
  • "Server=192.168.1.2;Database=POS;uid=Developer/IT;"; Returns "Login failed for user: Developer/IT" – Barry D. Aug 17 '15 at 11:43
  • Yes, if you can connect with trusted_connection then remove uid and pwd attributes: server=192.168.1.2\InstanceName;Trusted_Connection=yes;Database=pos – Cetin Basoz Aug 17 '15 at 11:44
  • K trying trusted connection – Barry D. Aug 17 '15 at 11:46
  • Trusted_Connection=true + No credentials returns: Error 40 - "Could not open a connection to SQL Server" – Barry D. Aug 17 '15 at 11:48
  • Did you watch the videos that I gave link to? The methods shown there work every time. – Cetin Basoz Aug 17 '15 at 11:50
  • I figured it out, posting something now. – Barry D. Aug 17 '15 at 14:24
0

You need to load your connection string from a configuration file and on publish run a first run wizard or something similar to set the connection string the first time.

To get your 2NDPC to connect, all you'd need to use is the DNS name of the PC and it should work. You may also need to set SQL Server to allow connections on TCP/IP in SQL Configuration Manager.

Also FYI seeing your comment about using IP addresses, you're doing it right, but you don't use \\ in front of the IP. Just replace your PC name with the IP address. EG: 

Data Source=192.168.0.1;Initial Catalog=MyDB; ...
ldam
  • 4,412
  • 6
  • 45
  • 76