Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

Server: Win Server 2003 R2 standard x64
MSSQL: Server 2005 Standard x64

Using SSMS on the database server itself I can execute a simple select on a linked server with OPENQUERY and I can also execute an OPENROWSET with the equivalent UNC path. The file in question is an Access database on a remote server.

However, when I attempt to use the same queries from a remote server or through a remotely executed stored procedure I receive the following error:

"The Microsoft Access database engine cannot open or write to the file '\\[xxx.xxx.xxx.xxx\path\blah\AccessData.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".

It seems like it's a permissions issue, but the same credentials that work when executing from the database server do not work from another server. The file definitely isn't open by anyone else.

I'm at my wits end and any help would be greatly appreciated.

share|improve this question
Are you connecting to the SQL Server with an AD login or a SQL login? If you are using AD, are you using kerberos? – darin strait Jun 14 '12 at 20:57
AD login. Works perfectly with windows auth on the host machine, but gives me that error from any remote server. – robusto Jun 15 '12 at 14:24
Is there an SPN set up for SQL running on this server? Are you connecting via kerberos or ntlm? – darin strait Jun 17 '12 at 11:13
SQL is running on the default instance MSSQLSERVER. I don't believe I specified any SPN. Authentication looks like NTLM from what I can see in the security logs. Should I be using Kerberos? – robusto Jun 18 '12 at 15:19
I don't think I've ever had success using NTLM when trying to connect from a SQL Server to another SQL Server or a legacy file format like Access or Excel. The workaround would be to use a SQL account, but I would recommend configuring an SPN if you have the time. I think that the simplest thing is to get your AD admin to allow the SQL Server's domain account to create an SPN when SQL Starts up. There are guides on the internet for that and for setting one up manually. I don't want to provide too much detail on this here becuase I think that it would be outside the scope of this question. – darin strait Jun 19 '12 at 17:18
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.