The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.
Sql Server Setup, Linking to application
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.
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.
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
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
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Sorry, I haven't abandoned the question just haven't had a chance to try anything of the suggestions yet.
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.
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.
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.
The person that installed SQL Server on the office server is unfortunately no longer available.
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.
mlcktmguy, if your issue as been solved please elect one or more comments provided by the experts as the best and assisted solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Access 2007 Int… 207 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.