SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked server with MS access mdb database - Error 7416?


Linked server with MS access mdb database - Error 7416?

Author
Message
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6969 Visits: 3501
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
rf44
rf44
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1093
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!
terry999
terry999
SSC Eights!
SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)

Group: General Forum Members
Points: 981 Visits: 677
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".
rf44
rf44
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1093
This is because Excel uses MSQuery which needs to access to several system objects.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search