February 14, 2011 at 10:21 am
I am trying to create a Linked Server to an MS Access database on a computer other than where SQL Server resides. I can create the linked server if the path is local (same computer as where SQL Server resides) but when I try to create a linked server to a copy of the same MS Access database but on another computer using a UNC path it won't work, I'm told it is not a valid file name. The MS Access database is not secured.
Is the problem really with permissions? I logged in the SQL Server computer using the same account that the service starts with. I set the TMP and TEMP user environment variables to C:\Temp. I made sure the account had security access to the C:\Temp folder and the permissions included modify and write. I have restarted the SQL Server services multiple times after making various changes. Within the Security tab of the Linked Server Properties I have tried many combinations and either get error 7416, 7303, or 7399. Is it necessary to specify a Local Login and Remote User in the 'Local server login to remote server login mappings' section? Is the Security context relative to the MS Access database, username: Admin; password: <blank>?
Any help is appreciated.
February 14, 2011 at 11:12 am
I think the account that is running SQL Server needs access to the UNC path. If you're using a domain account, then grant this user read/write permissions on the UNC path/file. If it's running as Local System then there is a computer account in AD that you must use. This account has the same name as the server (for example SRVSQL01)
February 14, 2011 at 1:04 pm
Thanks for your reply. I reviewed permissions and found that SQL Server login account did not have access to the full path, I had applied proper access to the final folder and file and missed a higher level folder. In the end modify/write access had to start at the top of the path and within the Security tab of the Linked Server Properties I set the 'Local server login to remote server login mappings' selection to use the same account that the SQL Server service started with for the Local Login and the Remote User was set to Admin with a blank password (because the MS Access database was not secure). The selection at the bottom was 'Be made without using a security context'. This last thing doesn't make sense but none of the other selections work, can you explain that?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply