Linking Access FE to SQL Server BE hosted on different machine (1 Viewer)

[tmyers]

I have created the various login's within SQL Server itself and have created a DSN file stored in a shared folder on a server but when I try to link the front end on another computer, I get this error:

I have created the login's on the server using windows auth. and have set the database to the one I want to connect to (not master).

 

[Minty]

That ODBC message string looks wrong to me can't say I've seen [DBNETLIB] in the error messages before.

What driver is specified in the file, and what driver is loaded on the machine in question.

 

[sonic8]

The error message (which looks fine to me BTW, @Minty) very likely indicates a problem with the connection on network level.

Is TCP/IP enabled as communication protocol for SQL Server on the sever computer? - Run SQL Server Configuration Manager on the server and check the "SQL Server Network Configuration" section.

Are there any other potential obstacles in network configuration, like firewalls, between client and server?
A common cause would be a Windows Firewall on the server not beeing configured to allow incoming connections to SQL Server.

 

[Minty]

@sonic8 - Fair enough, it must be a different driver to any of the ones I have used recently.

@tmyers If none of @sonic8 advice above works can I suggest simply trying to create a new ODBC connection to the server outside of Access, from that PC and see what errors you get, they are often more accurately descriptive.

 

[Isaac]

might be interesting to re-create that File DSN and when you do specify the IP address, not the server name. once that's done see if you have the same problem

 

[tmyers]

I used a guide that walked me through configuring the server to permit remote connections. I added those in an made a rule for the firewall to allow it through.

 

[tmyers]

might be interesting to re-create that File DSN and when you do specify the IP address, not the server name. once that's done see if you have the same problem

Ill give that a try.

 

[tmyers]

Progress! My test was able reach the server, but was denied. I have to go through the permissions more I guess.

Changing the name to the host computers IP did seem to do the trick though.

Edit:
Fixed the permission and they were able to connect, but they only seen system tables. Now I am at a total loss.

 

[Velocity]

It looks like you need to grant the correct privileges to the user so that they can view the tables and data. You can use SQL commands or the GUI in your database management system to assign the correct privileges to the user.

 

[sonic8]

Edit:
Fixed the permission and they were able to connect, but they only seen system tables. Now I am at a total loss.

I missed this edit.
Please clarify what "they only seen system tables" means exactly. - User should not "see" any table at all. They should see the data from the tables being displayed in the application.
So, what happens if your application executes an SQL Statement on one of its tables?

 

[GPGeorge]

I missed this edit.
Please clarify what "they only seen system tables" means exactly. - User should not "see" any table at all. They should see the data from the tables being displayed in the application.
So, what happens if your application executes an SQL Statement on one of its tables?

I have seen something similar when I connected to a newly set up SQL Server instance with a login that didn't have a user defined in any of the databases on it.