2

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?

enter image description here

EDIT 3:

Here is the server's Log On As thing:

enter image description here

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
Improve this question

2 Answers 2

Reset to default
5

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.

Improve this answer
3
  • Gah! I change them from Network Service to the same account that I use to get into the folder where the Access DB is... and it still doesn't work! However, if I go into the directory (by navigating through the network) and enter the same credentials and then test the connection, it works! Blargh!
    – Silver
    Jun 20, 2012 at 20:56
  • @Silver Did you re-start the service?
    – Cade Roux
    Jun 20, 2012 at 21:55
  • Indeed :(... I thought for sure that was going to work man, dang. I'll keep working on it tomorrow when I get to work. I'll give a play by play (screen shots) of what I do and why I think it should work.
    – Silver
    Jun 21, 2012 at 1:52
2

you can make your sql server service run under the username that can access the MsDB file, and take a look at other similar questions on SO1 & SO2

Improve this answer
9
  • Thanks for the response! I'm assuming you mean go into services.msc and change the "Log on as" value to say, Network Service, correct? The problem is, my supervisor doesn't want to ask our administrator to change this value because he's concerned that it will break everything. Therefore he wants to directly imbed the credentials in this addlinkedsrvlogin thing...
    – Silver
    Jun 20, 2012 at 17:49
  • 3
    @Silver There is no way to embed the network filessystem credentials in a linked server. Any yes, changing your account can break everything, which is why I generally recommend (if the SQL Server Service is going to participate in network activities) setting the SQL Server to run under a Domain account in the first place - it can then be added to and removed from groups without fiddling with the server.
    – Cade Roux
    Jun 20, 2012 at 18:17
  • @Cade Roux: So, which SQL things do I need to change to run under a Domain account? Would that Domain account maybe be 'cat'? (not a real account name, it's really in the form of sysam01/catEngineer). EDIT: It appears to recognize 'cat'... so I'm going to try testing it out here in a couple hours when we are given access to a test system. I'll report back here then.
    – Silver
    Jun 20, 2012 at 18:47
  • 1
    @Silver The account would be of the form domain\user (as opposed to a local account on the server) where domain is an active directory domain which is trusted by both the SQL Server's underlying OS and the file server's OS and where domain\user has been given suitable rights to both the share on the file server and the directory permissions themselves (note that a share can sometimes have different permissions than the underlying filesystem) so that the Access engine can be used to open the file. This would be similar for any cross-network file access from the SQL Server Service.
    – Cade Roux
    Jun 20, 2012 at 18:59
  • 1
    @Silver Note that the SQL Server service account is just one account which is used by the engine (mainly through T-SQL things). The Agent has it's own account, so if the Agent runs something where it needs to open a file (import a file off a network share), then that's a separate issue.
    – Cade Roux
    Jun 20, 2012 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.