SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network We have a SQL Server 2008 database that we are attempting to link with an Access 2007 DB (the maintainer works down the hall and refuses to migrate to SQL Server). We have the password to the database (assume the database password = dbpassword, and if it was only this level of protection I think we would already have linked to the Access database with minimal problems.

However, the Access database is on a network drive, and within a directory on that network drive that is protected. We know the username and password (let's assume the username = cat and the password = pancakes). 

I'm looking into this sp_addlinkedsrvlogin, and I believe it's what we need to do. Isn't it?

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ] 
     [ , [ @locallogin = ] 'locallogin' ] 
     [ , [ @rmtuser = ] 'rmtuser' ] 
     [ , [ @rmtpassword = ] 'rmtpassword' ] 


The problem is, I don't know what to put in these parameters.

I'm assuming rmtuser would be 'cat' and rmtpassword would be 'pancakes'...

Here is the full path to the Access database:
\volume9\v9\projects\cats\cooking\pancakedb\back-end

volume9 appears to correspond with the K: drive...

So would rmtsrvname = '\volume9\v9\projects\cats\cooking\pancakedb\back-end'?

Also,  useself = 'FALSE' right? And I don't know about locallogin.

Am I going about this the right way? This is the first time I've ever set up a sp_linkedserver. Any help is greatly appreciated!

EDIT:

Forgot! Our linked server is called PANCAKECONFIGDB. I don't know if that's useful

EDIT 2: 

These are the settings for SQL Server on my local machine. I'll put the information about the server's one's in a moment, as well as the full sp_linkedserver text. Is this where I should be changing things?



EDIT 3:

Here is the server's Log On As thing:



Also the script for the linkedserver...

****** Object:  LinkedServer [PANCAKECONFIGDB]    Script Date: 06/20/2012 14:14:28 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PANCAKECONFIGDB', @srvproduct=N'Access 2007', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'\\volume9\v9\projects\cats\cooking\PANCAKECONFIGDB\back-end\PANCAKECONFIGDB.accdb', @provstr=N';PWD= dbpassword'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PANCAKECONFIGDB',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
I'll just summarize all my comments here as an answer.

You should read this:

http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.105%29.aspx#Use_startup_accounts

The SQL Server Service is the SQL Server engine and runs under an account specified for the service and linked servers which use a file share will necessarily use those permissions, since there is no setting to have the Server Service impersonate another account when it connects through the filesystem.

The SQL Server Agent is a separate process which runs scheduled jobs.  If you use this and it connects to network shares on its own (not just telling SQL Server engine to run some statement against a linked server which would be under the engine permissions above), it would need to run under an appropriate domain account.  In your case, that doesn't appear to be running, so I wouldn't bother fooling with it.

Running the SQL Server Express has certain database size and functionality limitations, and I wouldn't consider it for general server-class production use except for high numbers of lightweight servers - for instance a deployment of an small inhouse application to several worksites - where you wouldn't want to pay for licensing and the requirements fit the product.

The built in network account is "NT AUTHORITY\NETWORK SERVICE" and you can just put that in and blank out the passwords to restore it.

If you switch the SQL Server Service to run as a domain account, you will need to give it sufficient permissions to access the OS on the database server - i.e. folder access to where the database files are if the account doesn't have access.