2

I need to create a stored procedure to access Excel files in SQL Server. I have created the below stored procedure. Since I am a sysadmin, the stored procedure is working fine for me.

But for the users who do not have SA privileges they are getting an error.

Could some one assist me on this, how the users can execute the stored procedure with my credentials (SYSADMIN)?

create procedure [dbo].[readexcel] 
    (@filepath varchar(max))
as
    declare @Statement varchar(max)
    --set @Statement = 
    EXECUTE('select * FROM OPENROWSET(' +
        '''Microsoft.ACE.OLEDB.12.0'',' +
        '''Excel 12.0;Database=' + @filepath +''',' +
        '''SELECT * FROM [Sheet1$]'')'); 

Error message:

Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server

  • even i tied to execute the SP with Execute as caluse , getting different error message EXECUTE AS user = 'username' Msg 916, Level 14, State 1, Line 2 The server principal "username" is not able to access the database "dbname" under the current security context. – Yogi Jan 20 '16 at 16:32
1

The good news is it CAN be done. The bad news is you are opening a security hole.

  1. Create a new database for this SP.
  2. Make the owner of the database a sysadmin.
  3. Set the database to trustworthy
  4. Create your SP in the database using EXECUTE AS OWNER
  5. For the people you want to be able to run this grant ONLY the following permissions.
    1. CONNECT (create them a user)
    2. EXECUTE on the stored procedure.

The reason for moving it off to it's own database & dramatically restricting permissions there I hope is fairly obvious. By setting things up this way anyone who can run the stored procedure is in effect a sysadmin. Now it's restricted to the code in the SP. But what happens if someone gets db_owner access? They can now write SPs that can grant them sysadmin. Note: db_owner is a simple example. There are lower permissions that could cause problems also.

  • Hi Kenneth, created new database with execute as owner and on the trustworthy on the new database. unfortunately getting below error message: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". 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)". – Yogi Jan 20 '16 at 17:21
  • Try making the owner of the DB as SA – Kenneth Fisher Jan 20 '16 at 17:34
  • now users are getting below error message: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server. – Yogi Jan 20 '16 at 18:25
  • I also found this social.msdn.microsoft.com/Forums/sqlserver/en-US/… where they suggested granting read/write access to C:\Users\<SQL Server Service Account>\AppData\Local\Temp – Kenneth Fisher Jan 20 '16 at 18:25
  • how to grant access for sql users ?. for windows logins i have granted it is working fine. Thank you so much for helping on this – Yogi Jan 20 '16 at 18:30
0

To specifically address your question and allow OPENROWSET to read the Excel file do the following:

I believe the SQL Service account is attempting to access the file. For trouble-shooting purposes on a system for which you can test and develop on, change the SQL Service account to a Windows account you know has access to the Excel file you're trying to open (in fact, open it with that user just to be sure). Use SQL Server Configuration Manager and restart the SQL Service.

enter image description here

For other related errors you may need to run the following to enable ad-hoc distributed queries and enable the ACE.OLEDB.12 provider:

exec sp_configure 'Show Advanced Options', 1;

RECONFIGURE;

GO

exec sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 

GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

GO 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.