title: Unable to access CSV file using linked server using any login other than 'sa'
body: <p>I've set up a linked server to read a set of CSV files located locally on the SQL Server machine, as follows:</p>
  
  <pre><code>USE master
  go
  
  IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLink') 
      EXEC sp_dropserver 'MyLink', 'droplogins'; 
  
  EXEC sp_AddLinkedServer @server     = 'MyLink', 
                          @srvproduct = '',
                          @provider   = 'Microsoft.ACE.OLEDB.12.0', 
                          @datasrc    = 'C:\Data\Feeds', 
                          @provstr    = 'Text'
  
  EXEC sp_AddLinkedSrvLogin MyLink, FALSE
  go
  </code></pre>
  
  <p>The following code then works perfectly, provided I'm logged in as 'sa' (either syntax works):</p>
  
  <pre><code>SELECT * FROM MyLink...myFile#csv
  SELECT * FROM OPENQUERY(MyLink,'select * from myFile.csv')
  </code></pre>
  
  <p>If I log in as any other user, however, I get the following error:</p>
  
  <pre><code>Msg 7416, Level 16, State 2, Line 1
  Access to the remote server is denied because no login-mapping exists.
  </code></pre>
  
  <p>Trying to change the remote login, either via script, or by modifying the linked server properties in SSMS to add a mapping, not only doesn't work but also breaks the ability of 'sa' to run the select statements.</p>
  
  <p>BTW, I'm trying to work around a <a href="http://connect.microsoft.com/SQLServer/feedback/details/127114/flat-file-source-incorrectly-parsing-data-when-using-comma-as-the-column-delimiter" rel="nofollow">known bug</a> in SSIS where it cannot handle CSV with commas inside double-quote-delimited fields.  I stumbled across <a href="http://www.sqlservercentral.com/Forums/Topic559811-149-1.aspx#bm559856" rel="nofollow">this post</a> which describes using a linked server to get around the problem without having to code a custom parsing routine.  Works great, except for the whole security thing; I don't want my application connecting as 'sa'.  I'm using SQL 2005 SP3.</p>
  
  <p>Any ideas on getting this to work with a run-of-the-mill login?</p>
  
  <p>TIA,
  Jim</p>
  
  <p>@JustinStolle When I set it up as you describe, I get a different error for the non-sa user (sa still seems to work):</p>
  
  <p>OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MyLink" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
  Msg 7399, Level 16, State 1, Line 1
  The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MyLink" reported an error. Authentication failed.
  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 "MyLink".</p>
  
  <p>I can't import the file; SSIS can't handle it.</p>