Sql Server Setup, Linking to application

mlcktmguy
mlcktmguy used Ask the Experts™
on
My firm is doing our first SQL Sever project so we are definitely NUB's.  

MS Access 2013 is the Front End and we are using SQL Server Developer 2014 for the backend DB.

Our first step was to install SQL Server Developer 2014 on each of the workstations involved in the project.  After that I created a 'File' DSN on the local machine and the developers were able to link to the SQL DB and do some preliminary testing/conversion work.  The only DB's the local machines could use, was the one on their machine.

This setup has been working well to get the staff familiar with using SQL Server.

The next step is to install SQL on our office server.  This step has been completed, so the DB is sitting on the server but none of the local machines can see it.

Now we need the individual workstations to be able to see/link to the SQL Server DB on our office server.  At this point I'm unsure of the next step, which I believe is setting up DNS(s) to link to SQL on our office Server.

Do each of the individual machines set up there own DSN to link to the instance on the server, or is the DSN set up on the server and referenced by the local machines?

I see three types of DSN's, 'User', 'System' and 'File'.  As I mentioned above, up to this point we have created 'File' DSN's on each of the local machines which connect to the SQL DB on the local machine.  What type of DSN will be used to connect to the SQL DB on our office Server?

Any help with the next step would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Before Setting up a DSN, I'd make sure the SQL server is even accessible.
To start, make sure that the SQL Server Browser service is up and running (You can check all services in services.msc)

Also, I'd make sure that the server is listening to remote connections over port 1433, you'll need to check the configuration manager.

Once you've done that and once you are certain there are no firewall blocks over port 1433, go to any workstation and create a simple text file on the desktop, then change the extension to UDL which allows you to test connections to an SQL server.
Commented:
1st try to ping the "main" sql server box from user(s) PC
2nd check if sql server service on the "server" is running ( check windows services)
2.1 check if sql server network protocols are set\enabled  (e.g. TCP\IP)
3rd check with person who installed this sql server -- the sql server name to be able to connect it
 and sql login  with DB permissions was set :AD group based  or sql login...
4rd  try to connect the new sql server from developer's PC by using SSMS (click SQL Server Management Studio)
 check
Lesson 1: Connecting to the Database Engine
https://msdn.microsoft.com/en-us/library/ms345332(v=sql.120).aspx

Lesson 2: Connecting from Another Computer
https://msdn.microsoft.com/en-US/library/ms345343(v=sql.120).aspx


5th set MS Access link to sql server

see video how-to set it

Linking MS Access to MS SQL Server 2008 R2
https://www.youtube.com/watch?v=4bLRsYgtL-Q

more
https://support.office.com/en-us/article/Link-to-SQL-Server-data-0474c16d-a473-4458-9cf7-f369b78d3db8

Author

Commented:
Sorry, I haven't abandoned the question just haven't had a chance to try anything of the suggestions yet.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
SQL Server is installed remotely so to access it from a remote workstation and since everybody has SQL Server Management Studio (SSMS) installed you just need to open SSMS and when requesting you to give the server name just replace the local server name with the remote server name.
For this work you'll need to have SQL Server Browser service running in the server side and if the server is behind a firewall allow the port TCP 1433 and UDP 1434 in the firewall.

Author

Commented:
Thanks for all the suggestions although I don't really know how to implement most of the them.  I'm trying to locate someone in the area that can assist me with this.

The person that installed SQL Server on the office server is unfortunately no longer available.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Maybe this tutorial can help you.

Author

Commented:
I am getting abandoned notices on tis question.  I have not abandoned it but have not been able to implement any of the above suggestions yet.

Author

Commented:
Thank you all for your assistance
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
mlcktmguy, if your issue as been solved please elect one or more comments provided by the experts as the best and assisted solution.

Author

Commented:
I thought I had done that last night.  I will try again this morning.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial