Post reply

Openrowset with Mapped Drive using SQL Auth

  • October 23, 2007 at 7:50 am

    #179524

    I have a stored procedure that uses openrowset and a mapped drive to read data from a csv like so.

    Select * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=S:\','SELECT * FROM SomeFile.csv')

    I mapped the S:\ drive on the server that SQL Server is running to the share where the file is located. When logged into Management Studio using Windows auth the proc runs fine. When logged on via SQL Auth I get the error message.

    OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'S:\' is not a valid path

    The proc is going to be called through an asp.net 2.0 app that is using sql Auth to connect to the db. Is there anyway to get this to work with SQL Auth?

  • October 23, 2007 at 8:03 am

    #744550

    check out this link and try to implement the methodology.

    http://msdn2.microsoft.com/en-us/library/ms175608.aspx

  • October 23, 2007 at 8:38 am

    #744568

    I was able to get this working by first setting trustworthy bit for the db to on

    ALTER DATABASE MyDB SET TRUSTWORTHY ON;

    and then while logged in to Windows as the Windows user that was able to successfully execute the proc initially and connected to SQL Man Studio using Windows auth I altered the proc to include the

    WITH EXECUTE AS self

    clause.

    Thanks for the help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply