Application using SQL Server Database among a LAN

Question

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.

Answer 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

Answer 2

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; ...

Answer 3

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