Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server with MS access mdb database - Error 7416? Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 7:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008

Hi

MS ACCESS mdb database located to another server.

SQL 2K8R2 - database another server.

I have created & working fine linked at SQL 2K8R2 with MS ACCESS by using windows administator user in SQL box.

when try to connect and access SQL user does not working linked server and throwing error 7416.

It seems like remote server login access denied in SQL box, so how can assign remote sever login and password at SQL box. because mdb file don't have user and login.

Thanks
ananda
Post #1597282
Posted Wednesday, July 30, 2014 1:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:23 AM
Points: 84, Visits: 1,028
1. User must have connection rights (login) to the SQL Server or be member of an A.D. group that has connection rights to the SQL Server.
2. User must have the necessary access rights to the concerned database or be member of an A.D. group that has access rights to the concerned database.
3. As the Access database is a Front-End (i.e. it does not contain the data tables), it should be copied on each client machine (never share a F.E.).
4. If a connection string is used (i.e. no attached tables or dynamic attached tables), include "Trusted_Connection=Yes" in the connection string (e.g.: "ODBC;DRIVER={SQL Server};SERVER=Iskender;DATABASE=PictureManagerSQL;TRUSTED_CONNECTION;Yes;").
5. If the F.E. uses attached tables through a DNS (ODBC defined connection), use an account that fullfill 1 and 2 here above when defining this ODBC connection (it can be a system-defined DNS connection or a user-defined DNS connection).

Have a nice day!
Post #1597920
Posted Thursday, July 31, 2014 3:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 116, Visits: 439
Hi

Never used access, but had problem with Excel file on another machine. I had to set the user in the linked server properties to connect to the XLS as "admin".

Post #1598148
Posted Thursday, July 31, 2014 4:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:23 AM
Points: 84, Visits: 1,028
This is because Excel uses MSQuery which needs to access to several system objects.
Post #1598163
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse