sql server 2008 export to mdb issue

Question

Hi

i'm trying to import records from a mdb file to sql server 2008. i have a separate db server and application server.

wrote a stored proc to import data from mdb. But it fails as the mdb is on a network. It works if it's on the db server itself.

i'm using sql server account.

if i login using windows account and try the same sp it works.

How to make it work for sql server account?

-Vivek

Answer 1

Is the SQL Server service running under a domain user account? If not, then it has no means to authenticate to the network share. To correct, you should change the SQL Server service to run under a domain account that has access to the network share. When you login via Windows Authentication, the stored procedure runs under your credentials which have access to the network share.

ADDITION Changing the SQL Server service account.

1. Run the SQL Configuration Manager
2. Click on SQL Services
3. Right-click on the SQL Server serice and choose "Properties..."
4. Change the account name to something like <Domain>\SQLUser

I would recommend having a dedicated account on the domain for SQL Server with a long password that is kept safe somewhere. The configuration manager should put the user in the appropriate Local User group which will be named something like "SQLServerMSSQLUser$$MSSQLServer" which will give it the necessary rights to run SQL Server as a service and access the files and folders it needs.

For more information:

Setting Up Windows Service Accounts