One of the things I find myself doing every day is teaching apps developers how to do stuff with these principles in mind:
- Least privilege
- Smallest attack surface
Unfortunately, most apps developers in my workplace:
- Are administrators on both their workstations (I'm OK with this) and their development servers (not so OK with this), which means they:
- Install heaps of shit they don't need, particularly on the dev server
- Configure that shit to run under their own administrative user account (!!!!)
- Forget they've installed that shit
- After a period of time, don't remember if that shit is necessary for their code to run
- Install all the server-side components of their client/server app onto one single dev server. This blurs the lines. Is it the web part of the app that performs task X, or is it the SQL part? Who knows. The apps developers don't.
So when the time comes to provision their production environment, I get requests like this:
- Install Excel on the SQL box and the web server box, and actually any other box that our app uses, because we can't remember which box actually needs it
- Enable xp_cmdshell on the SQL box. We're not really sure why, but we need it.
- And while you're at it, we need you to create scheduled tasks on all the boxes (because we can't remember what box does what), and that task needs admin rights because otherwise it doesn't work. But we don't really know why it doesn't work.
I wish I was making this shit up. But this is what I've spent the last week extracting from a particular group of people. Of course, when I say NO to this sort of crap, it becomes my problem to come up with an alternative that uses least privilege, whilst installing the smallest possible amount of stuff to make it work.
I'll write a separate post about the xp_cmdshell thing, but for now I want to deal with the Excel thing. I don't believe that any SQL server should have anything installed on it aside from SQL. Excel is a user app, not a server app. And I don't believe for a second that SQL "needs" it to import Excel data.
So. Getting onto the heart of this post. How do we get that Excel data into a SQL server database?
It would be nice if something like this would just work. It doesn't:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\downloads\ImportTest-XLS.xls;Extended Properties=Excel 8.0')...[Sheet1$]
This code was snaffled from a MS article, BTW, http://support.microsoft.com/kb/321686. The article also makes reference to linked servers, but for my purposes I just want to run ad hoc queries on transient spreadsheets.
We get this error message:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Hmm. WTF does that mean? Believe it or not, it's not because you need to do something about apartment mode, whatever the hell that is. It's actually SQL's obscure way of complaining about the data source driver. For 2007 and 2010 versions of Excel files, you need to install a provider that knows how to deal with these files. By default, SQL 2008 R2 doesn't have one:
Get a hold of the Microsoft Access Database Engine 2010 Redistributable (don't worry about the "Access" in the title, just download it). Make sure you get the 64-bit version if you're running SQL 2008 R2.
Install it. If you get this message, it's because you have a 32-bit installation of Office or one of its components. Naughty! Uninstall it.
Uninstalling the Office components might require a reboot. My experience with the Access driver is that you'll need to reboot your server again (or possibly just restart the SQL services – I haven't tried this in my test environment yet). So if you're doing this on a production box, be sure to schedule an outage.
You'll see a new provider in the list:
Let's try running that TSQL code again:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\downloads\ImportTest-XLS.xls;Extended Properties=Excel 8.0')...[Sheet1$]
We get the same error message again because now we are using the wrong provider. Let's change that:
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0;Database=c:\downloads\ImportTest-XLS.xls;HDR=YES;IMEX=1', 'SELECT * FROM [sheet1$]');
Now we get another error message, though thankfully less obscure:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
OK. Let's enable that, shall we?
sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO
Let's try again:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Oh for Christ's sake. What now?
There are some things that need to be enabled on the provider itself:
USE [master] 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 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1 GO
Most articles I've seen recommend just the first two settings, but in all my experiments I just couldn't get it to work. I found an article that mentioned the last two settings and had success with that. Your mileage may vary.
Let's try it again!
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0;Database=c:\downloads\ImportTest-XLS.xls;HDR=YES;IMEX=1', 'SELECT * FROM [sheet1$]');
Holy snapping duckshit! It WORKS!
| First name | Last Name |
|---|---|
| Firstname1 | Lastname1 |
| Firstname1 | Lastname2 |
| Firstname3 | Lastname3 |
You'll also be able to read .XLSX files:
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0;Database=c:\downloads\ImportTest-XLSX.xlsx;HDR=YES;IMEX=1', 'SELECT * FROM [sheet1$]');
And CSV files, though I suspect you'd be able to do this without the driver:
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Text;Database=C:\Downloads\;HDR=Yes;',
'SELECT * FROM [ImportTest-CSV.csv]');
Note the syntax for a CSV is slightly different. Also note that in all of them, the last parameter makes reference to the sheet name. So make sure you've got that last part right.
So, in summary:
- Remove any existing Office components and reboot.
- Install the Microsoft Access Database Engine 2010 Redistributable and reboot
- Run the following TSQL commands:
USE [master] GO sp_configure 'show advanced options', 1; RECONFIGURE; GO 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 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1 GO
And you are good to go!!!!!!!!!!!!!!!! 
Now that you can actually read the damn files, you can import them into a table:
SELECT * INTO SomeTable FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0;Database=c:\downloads\ImportTest-XLS.xls;HDR=YES;IMEX=1', 'SELECT * FROM [sheet1$]');
Note that the only change in the query is the "select into" part. The rest is the same, and this applies to the XLSX or CSV import.
The SELECT INTO will overwrite your table, so if the plan is to add rows to it, use this sort of query:
INSERT INTO SomeTable SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0;Database=c:\downloads\ImportTest-XLSX.xlsx;HDR=YES;IMEX=1', 'SELECT * FROM [sheet1$]');
You are a genius
You’re biased. But thanks 🙂
Thank You so much, the issue is resolved
Remove any existing 32-bit Office components is unnecessary. Only need to do is:
AccessDatabaseEngine_x64.exe /passive
By the way: great post!
Thank you for the tip, and also for the compliment!
I’ve read this article a number of times over, and with hindsight, I think there are probably better ways to do the job (eg SSIS). But it’s still a cool thing to be able to do. 🙂
Thanks for the AccessDatabaseEngine_x64.exe /passive tip.
Love the article but am hoping you have some more information.
I have two identical servers Prod and Test. The steps above work great on Test, but not on Production.
I am receiving the 7399 and 7350 errors and am crossing my fingers that someone has seen this and found a solution.
Thanks!!
Muchas gracias sos un capo 🙂
Holy snappin’ duckshit??? BWAAAA-HAAAA-HAAAA!!!! I blew the porkchop bone I was nibbling on right out my nose when I saw that!!! 😉
Great article and thanks for the laugh!
–Jeff Moden
I’m glad you enjoyed it! That article (and a few other SQL articles) was borne out of frustration with SQL. I’m a SQL noob, but have somehow become my company’s DBA person. As a result, my life is two-parts frustration and one part yippee when I finally solve a problem (that would take someone experienced, say, a SQL MVP, no time at all to fix).
Speaking of SQL MVPs…. can I just say how thrilled I am that you liked my article? That really made my day! 😀
Apologies for the very late reply. I lost track of this wonderful thread (book-marked in about 14 places now, though).
Like you, I started out as an “accidental DBA” and the name of my Assistant DBA is “Google”. 😉 Neither Google nor Yahoo had yet been invented when I started (now long in tooth and gray hair in places where younger folks don’t have hair 😉 ) so I have a real live appreciation for what you went through to get all this working for you.
Keep up the great work and don’t ever lose that great sense of humor. Like Red-Green says, “We’re all in this together and I’m pullin’ for ya!”
–Jeff Moden
Is there any way you can upload an excel data located in a different computer that the one with the SQL 2008 R2 server without using SSIS ?
Thanks in advance
Assuming the file is located on a network share somewhere, maybe you could just use the code above, but point it to the UNC path \\servername\sharename\filename.xlsx instead of c:\path\filename.xlsx.
I haven’t played much with Excel, but I think there is a built-in feature that allows Excel to push data to SQL (or vice versa) from the client. So if this is something you don’t need to do on an automated basis, maybe that could work for you.
Hi maxxxie
I found this page very useful to use Microsoft.ACE.OLEDB.12.0 to import CSVs that a third party gave me.
You may wonder why CSVs need Microsoft.ACE.OLEDB.12.0. The pain was that a few columns were enclosed in double-quotes on a few rows and very same columns were without double-quotes in few other rows. And data had commas when enclosed in double-quotes.
I used the Microsoft.ACE.OLEDB.12.0 as the quick way out. Without your help “quick” would not have been “quick” enuf. SSIS is not an option for me.
Thank you for writing this out.
Your style leaves things clear with no scope of any confusion.
I also used the AccessDatabaseEngine_x64.exe /passive tip.
However when I changed to SQL Server from 2008(uninstalled) to 2012. I got this error
OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”
I followed the above steps again but it didnt help this time.
I have tried several tips in last 4 hours in an unorganised manner and i dont know what worked but something worked. Probably it was full rights to all to temp folder. I am not sure.
Apologies for ending the thanks with a vague problem-solution.
Regards
Sheela
I’m very glad my article was able to help you! I have to say, as a non-DBA thrust into a DBA role, learning this sort of stuff about SQL has been a challenge. So when I come across a problem like this, I figure maybe I can help others by publishing my solution.
Also, don’t worry about your “vague” solution. Sometimes even the tiniest bit of information can help point people in the right direction 🙂
Max
Words are not enough to express my frustration with Excel imports…… then my elation at reading your blog. All I can say is THANKS!
You are most welcome!
Well, I followed every step, I saw every error message and every image with impressive accuracy…till I reached the “holy snapping duckshit” part. Unfortunately, I got a new unexpected error:
(“OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Error no especificado”.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”)
If you have any suggestions, I’m all ears. Despite it didn’t work to me, I think this was a great post, btw.
Regards!
Hi Salvador! I haven’t come across that error before, but some Google hits suggest an issue actually accessing the Excel file itself. Take a look at this link. It suggests a permissions issue. Check if SQL’s service account has NTFS permissions to the file you’re trying to access. If the file is on a remote server (UNC path), then the SQL Server service account will need to be a domain account that has access to the target share as well as the files on it.
Also, double-check that you’ve run all of these:
USE [master]
GO
sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO
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
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’NestedQueries’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’NonTransactedUpdates’, 1
GO
I hope this helps! And thank you for the compliment 🙂
Also, this article is worth looking at. There’s mention of permissions on the local Temp directory. It’s probably worth investigating.
Hi Maxxie,
First of all, thanks a lot for your prompt response. You were right, The Temp subfolder, either at local and at Network folders, must be granted with full permissions. After this, the error dissappeared (msg 7303), but the previous error message prompted again:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.
Since the Microsoft.ACE.OLEDB.12.0 was already included at my sql providers (SQL 2008 R2) I just followed the rest of the steps again. I also re-ran the commands you asked me to double check having the expected results:
Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ad Hoc Distributed Queries’ changed from 1 to 1. Run the RECONFIGURE statement to install.
But it didn’t work, I just got the same error msg again (msg 7399/ msg 7350)
I checked the provider’s options finding all of them were checked but these two:
-Index as access path
-Disallow adhoc access
I have to say i played disabling and enabling back the other options (Dynamic parameter, Nested queries, Level zero only, Allow inprocess, Non transacted updates, Supports ‘Like’ operator) and closing and opening back SQL everytime, but I got exactly the same error again and again when running the query.
If you have any idea of what could it be, or if you noticed something wrong in my procedures, I would greatly appreciate it.
Regards!
Hi Salvador!
I did a quick google, and lots of hits suggest an issue with the OLEDB providers. Can you try removing the Access driver altogether, reboot, install it again, reboot, and see what happens?
Also, what query are you using to try to read the excel file?
Max
Hi Max!
Guess what? It finally worked out!!! Now, let me share how was it, in case someone else has the same issue:
After uninstalling and installing again the Access driver (AccesDatabaseEngine_x64.exe) and running all the commands I got the following error:
(“OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” returned message “Error no especificado”.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”)
So I granted full permissions to both Temp subfolders (the one at NetworkServices and the one at LocalServices), and then I got the previous error again:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.
But then, I thought it could be a good idea to reboot the server. So I did. The result was that I had again the first error msg:
(“OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” returned message “Error no especificado”.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”)
And here comes the big trick: I went to START/RUN/SERVICES.MSC, then I looked for SQL SERVER (MSSQLSERVER) service, right clicked, went to LOG ON tab and wrote the usual account I use to log on to the server everyday, with the same password and all. Then I saved the configuration, and stopped and started the service.
Finally, I typed the query again:
select * from openrowset
(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0; IMEX=1; HDR=YES; Database=C:\Prueba\Nombres.xlsx;’,
‘SELECT * FROM [Hoja1$]’);
…and voilà!!!! Working smooth as silk!!
Thanks a lot for the post and for your help!!!
Great news that you’ve resolved it! 😀 Your fix tells me that it is definitely an issue with the permissions granted to the account your SQL Server service uses (which we already kind of knew from your previous experiments).
My guess is that you user account is a member of the local Administrators group. It stands to reason that using this account as your SQL Server service account would resolve your problem. But, it’s not best practice. Ideally, the SQL Server service account is an unprivileged domain account. If you’re interested in seeing how badly things can get messed up if it uses a privileged account, have a read of a few articles I wrote about it here, here and here.
You might be interested to know that Microsoft recommend using the SQL Server Configuration Manager MMC to change the SQL Server service account, rather than using the Services MMC to achieve the same goal. This is because the SQL Server Configuration Manager MMC also changes a bunch of permissions behind the scenes, to let SQL do its job. The Services MMC doesn’t do that (literally all it does is change the logon account). There’s a Microsoft article here that describes the registry keys, NTFS permissions and user rights that the SQL Server Configuration Manager tool touches (and here is a MUCH more in-depth article). And there’s an article here that gives a step-by-step on how to use SQL Server Configuration Manager to change the SQL Server service account.
I mention this because it is likely one of those things (mentioned in the MS articles) that’s messed up in your environment. I know it’s tempting to leave things as they are right now! It works, leave it alone! But if it was my environment, I would want to find out what permissions aren’t right. And I would also want to make sure my SQL Server service account is unprivileged.
If you have time to spend troubleshooting it further, and you’re curious like me 🙂 , I would really like to know what happens if you create a service account (just a domain user account, nothing special) and use the SQL Server Configuration Manager MMC to reconfigure SQL Server’s service account. Then restart the SQL Server service. Then, making sure you’ve granted NTFS permissions to the Excel file in question, see if your query runs properly.
Max
Hi Max!
Thanks a lot for the info. Truth be said, I was tempted to let it work as it was… but an inner voice whispered me to dig about your comments and posts and see how deep was the hole, my curiosity won and so I am here.
I read the three posts about the evil xp_cmdshell and the triangle of death (SQL server service linked to an account member of the domain admin group, SQL proxy configured with such an account and the xp_cmdshell enabled), which was pretty enough to terrorize me about the future.
So after suffering a while to link an account following the article you kindly provided me (I had to create a new account and set a password, but then when I was linking the SQL server service to it the password didn’t work and finally found out I wrote the first password wrong so all my attempts were failing…) I finally managed to make the SQL to work. Just for the record, I don’t really know why, but I had to stop and then to start the SQL service (a reboot didn’t work, it was showing me the error “The remote procedure call failed [0x800706be]”)
Thanks god I didn’t have the program at production yet, so the security of the server was not compromised.
I still have to make some more tests but looks to work fine, it took the commands properly and I haven’t seen any more problems till now.
Again, thanks a lot for all your help. If I find something weird or interesting to comment I will let you know.
Regards!
Hey Salvador!
That’s great news! I’m really glad my article was able to help you, and that you’re feeling better about your system’s security, too. Best of luck with putting it all into production 🙂
Max
I know I’m only a couple of years late in responding but wanted to respond about xp_CmdShell. It’s NOT a security nightmare. If you’ve done it right, only those with “sysadmin” privs can enable it and on those with “sysadmin” privs can use it. You should never ever allow someone outside the DBA group to use it directly through a proxy. You CAN very safely allow them to use it through a stored procedure by setting up a proxy but, although folks can run a stored procedure that uses xp_cmdshell, they cannot use xp_cmdshell directly if you have it setup properly and that’s an easy thing to do.
A bigger problem is that people think that xp_CmdShell is a security problem. That deprives DBAs of an incredibly powerful and valuable tool and having it disabled doesn’t do anything for security except to deceive people into thinking they’ve enhanced security. It may even lead them into a false sense of being secure and nothing could be further from the truth because anyone that breaks into a server is going to try to do it as someone that has “sysadmin” privs and their attack software takes less than 3ms to figure out if xp_CmdShell is enabled or not and then enable it if it isn’t.
Also, even if xp_CmdShell didn’t exist, there are EXEC tasks and Powershell available and those are just as dangerous in the hands of an attacker.
Stop depriving yourself of a useful tool and start paying attention to real security problems like password complexity, rotating passwords, constant passwords, sharing passwords, and using passwords of security accounts. Worry about and solve all SQL Injection vectors and limit what SQL Server can actually see. And never build implicit trust between machines.
Jeff, welcome back! You’re right, it’s been a couple years. And here I was, thinking nobody reads my blog 🙂
I wrote a few posts about xp_cmdshell, and they were all around how xp_cmdshell *can* be a security nightmare if it’s done wrong. Have you had a look through those posts? I’d be interested to hear what you think.
http://www.maxxxie.net/2012/07/06/xp_cmdshell-baby-did-a-bad-bad-thing/
http://www.maxxxie.net/2012/07/08/xp_cmdshell-some-more-evil/
http://www.maxxxie.net/2012/07/08/xp_cmdshell-elevated-accounts-a-new-kind-of-evil/
My objective in those posts was to configure xp_cmdshell poorly, specifically to demonstrate how it can be exploited if it’s not properly secured.
I take your point about xp_cmdshell being useful if done right. I don’t disagree.
Anyway, have a look at those other posts and let me know what you think. And welcome back 🙂
Max
Man, I wish there was some way to get post notifications from all this. Nothing like dropping in every 18 months or so just to see. 😉
Read your posts (very well done, as usual) and, yeah, if someone is dumb enough to create the proxy with a user that has domain privs and then gives some unqualified user privs to run xp_CmdShell directly, you’re screwed. Definitely not my idea of using it probperly (but you also say that).
Ah… just noticed when I went to post this… there is a way to get notified for new comments. Since it’s so obvious, I know I must have checked the box before. I wonder why I wasn’t notified. Maybe it got caught in my SPAM locker.
Welcome back, Jeff, and happy new year 🙂
Hi Max
I needed to setup an old code reading XL files on a new laptop. Microsoft.ACE.OLEDB.12.0 was already listed in Linked Servers Providers. But I ran into all the usual problems while using OpenRowSet to read the XL.
I did not install AccessDatabaseEngine_x64.exe /passive as I was afraid I may complicate the problems.
What worked for me this time was – I used Folder>Properties>Security to give full control to NT service\MSSQLSERVER on the folder that contained the XL file used in the code.
I have now book marked this page as a reference for SQL Server – XL file issues.
Thank you Max
Warm regards
Sheela
That’s good feedback. It’s been a *long* time since I wrote that blog entry, and I honestly can’t remember if I looked at file permissions as a culprit. It’s a handy tip. Thanks for posting!