SQL Server Logins and Windows Authentication

  • I have a stored procedure to export data from SQL tables to an Excel file. I can run it in QA by using the windows authentication login. But I can not run it under another login of the database, even I tried to grant every permissions of the database to the login. Then the problem is that how can I fire off the stored procedure in a .Net application, since you can not use Window Authentication as the connection to the database! Any suggestions?

    Thanks!

  • what account is your SQL server service running as? is it loalsystem?

    it sounds like the problem may relate to the windows permissions of the SQL server service. - can the file be created by that account?

    what is the error message being generated?

    MVDBA

  • I tried to run my SQL Server Service under LocalSystem. It stil does not work.

    The error message is:

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.

    [OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]

    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].

    Thanks!

  • from SQL to Excel?

    this looks a lot like msaccess

    MVDBA

  • Anyhow, that's what my user wanted?

  • Jet can be used to make an OLEDB connection to both Excel and text files. Not a common thing, but it's part of the functionality of Jet.

    K. Brian Kelley
    @kbriankelley

  • I am guessing that you are using a linked server for the Excel XLS, right?

    Since you stated that the data flow is from SQL Server to Excel, then the XLS file has to exist before the linked server connection can be made. You can see my method around this issue here:

    http://www.sqlservercentral.com/scripts/contributions/763.asp

    Also for Jet connections you should use Admin for the user name with no password.

    Andy

  • When I tried to used OPENROWSET to handle the INSERT instead of a LINKED SERVER, everything worked just fine.

    Thanks everybody!

Viewing 8 posts - 1 through 7 (of 7 total)

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