July 29, 2014 at 7:24 am
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
July 30, 2014 at 1:10 pm
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!
July 31, 2014 at 3:20 am
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".
July 31, 2014 at 4:12 am
This is because Excel uses MSQuery which needs to access to several system objects.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply