title: Using SQL Credential to Open a file with OpenRowSet
body: <p>I am attempting to run the below statement:</p>
  
  <pre><code>INSERT INTO table SELECT * FROM 
  OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;
  Database=//server/folder/file.xls;
  HDR=YES;',
  'SELECT * FROM [Sheet1$]')
  </code></pre>
  
  <p>However, I am receiving the below error:</p>
  
  <pre><code>OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\server\folder\file.xls'. It is already opened exclusively by another user, or you need permission to view and write its data.".
  
  Msg 7303, Level 16, State 1, Line 1
  Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
  </code></pre>
  
  <p>I am running SQL Server 2005 on a 32 bit machine. The code being executed will eventually be from c# code behind on a IIS6 web server. However, currently I'm just trying to get it to run on the SQL Server. I'm logged into the SQL Server using SQL Auth, but the file must be accessed using Windows Auth specific to the share drive (an AD account on our network). Ad-Hoc priveleges have been given to the SQL Auth account in order to allow <code>OPENROWSET</code>.</p>
  
  <p>I have tried adding <code>UID=user;PASS=pswd</code> into the <code>OPENROWSET</code> code as below:</p>
  
  <pre><code>INSERT INTO table SELECT * FROM 
      OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;
      Database=//server/folder/file.xls;
      HDR=YES;
  UID=user;
  PASS=pswd',
      'SELECT * FROM [Sheet1$]')
  </code></pre>
  
  <p>This yielded the same error. I also set up an SQL <code>CREDENTIAL</code> with the user id and pass of the windows auth Active Directory account with access to that share drive, but the error remained.</p>
  
  <p>I considered using a proxy, but this isn't an sql server agent job. It's an adhoc call.</p>
  
  <p>I have also logged into the SQL Server using Windows Auth with a user that has rights to the share, and get the same error.</p>
  
  <p>I've researched this a lot, but can't seem to find an answer that works. Through my searches, it seems to be a problem many people have. Any help will be greatly appreciated. I'm stumped.</p>