0

So we've set up a sp_linkedserver on SQL Server to connect to a remote Access database.

Assume the path to this database is W:/breakfast/pancakes/secretPancakes/pancakeDB.accdb

We know the username and password to the actual database

Password: allY0urPancakeAreBelongToUs!

The problem is, there is also authentication to get into the "secretPancakes" directory.

This authentication is

Username: SuperChef

Password: ilovesf00d

Here is the code for making the sp_linkedserver...

/****** Object: LinkedServer [PancakeDB] Script Date: 06/13/2012 10:08:21 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PancakeDB', @srvproduct=N'Access 2007', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'W:/breakfast/pancakes/secretPancakes/pancakeDB.accdb
', @provstr=N';PWD=allY0urPancakeAreBelongToUs'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PancakeDB',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

The rmtuser and rmtpassword are for the database though correct? Thing is, if I go on my actual computer and open the protected directory (secretPancakes) and ask Windows to remember my credentials, it'll work fine on my computer, but not on others'.

I'm trying to fix this so we don't have to do the objNetwork.MapNetworkDrive(DriveLetter, RemotePath, False, UserID, UserPWD) thing, which is a pain. (objNetwork = Microsoft.VisualBasic.CreateObject("WScript.Network"))

So my question is: how do I pass along the credentials for folder access within the linkedserver?

UPDATE: This screen shot may help...? enter image description here

Also, is this where I would change these credentials to the credentials used to get into that folder right? enter image description here

1 Answers1

0

The SQL Server service account needs to have access to the folder where the Access file is, then it should work. As you said, the user name and password for the Access DB have nothing to do with whether or not someone has permissions on the file system.

Ideally, you should not use drive letters here and simply use a UNC path instead, which will avoid your drive mapping issue. But the only way to allow SQL Server to read from a file is to make sure that it has the appropriate share and NTFS permissions to do so.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • Right, so can you look at my added screen shots and see if this is where I should change things? I don't know the password of the Network Service (and it won't let me copy it), so I'm slightly concerned about changing it. I mean I could just reinstall SQL Server after testing it on a local machine.... right? –  Jun 14 '12 at 13:06
  • 1
    This topic is [documented in great detail](http://msdn.microsoft.com/en-us/library/ms143504.aspx) so you should review all the information. Long story short, you can change the service account to a domain account, but make sure you follow the process described in the documentation to do so, otherwise it's possible that SQL Server won't start because the new account has no permission to open any database files. – Pondlife Jun 14 '12 at 14:52
  • Excellent resource! Thank you very much! –  Jun 14 '12 at 23:46