OPENROWSET - To use or not to use.

Question

OPENROWSET - To use or not to use.

Simon G

Ten Centuries

Points: 1285
More actions
December 9, 2008 at 2:00 pm

#124934

Hi,
We are looking at the possibility of enabling 'OPENROWSET' functionality (Ad Hoc remote Queries under SQL Server Surface Area Configuration) so we can script the import of CSV files to our database on the database server itself.
In doing this, I realise that we are opening up a potential security hole via our websites if someone got hold of any particular databases log on information.
I just need to know if we can do this without giving the website itself access to run this SQL command?
All of our websites only give Database Reader and Writer access via their connections and our SQL servers should all be locked out via the firewall apart from a few IP addresses we have specified.
I already have a script set up on a local server and it imports CSV's like a dream...when I ran a test call of this code on our local office server from an ASP website:
select *
from OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=x:\my_file_path\;Extensions=CSV;',
'SELECT * FROM [my_csv_file.csv]')
I received the following error:
"Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server."
This suggests that by default, Windows denies access to this command which is what we want. I just want to make sure we are completley secure. As a further test, I tried to run the above SQL query directly in SQL Server using one of the user accounts for our website and it gave the same error which further suggests it will be safe to enable this on the server.
Am I right or am I potentially opening up our server for a whole world of pain.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

It's not the worst thing if you enable this, but it can be a security hole. If you know the CSV format, why not use a SSIS package? You can schedule this, and then even run it on demand with sp_start_job.

If it's ad hoc, I'd be wary of it. Especially since how do you know what to build the table structure as? If it's dynamic, then you could be asking for issues.

noeld SSC Guru Points: 96590 More actions · Answer 2

Linked servers do not require the use of "openrowset/openquery/opendatasource".

You may want to try that instead.

* Noel

Simon G Ten Centuries Points: 1285 More actions · Answer 3

Thanks for the response guys.

I've set up a table to accept the CSV and the file is uploaded on the site (in an admin section) using a set structure so not any old CSV can be imported. It's more the fact that you can do a number of things if you know the DB structure in any way....not that I imagine anyone would but still...

With regard to a linked server though, how would I set up a CSV import via this. I have no experience in this side of SQL Server....any cool links about?

PS - I'll look into the SSIS package too!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

If you really want to lock things down, use BULK INSERT and format files (if you need them). Take a peek in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

The big thing is do you know the format of the file ahead of time or not?

Simon G Ten Centuries Points: 1285 More actions · Answer 6

Yep, the format of the file is dictated to the client by me.

Basically, we are providing a bulk import of items into his database. At the moment, my script uses the OPENROWSET command to pull in each uploaded CSV file (which is structured how I've set out) into a kind of 'preview' table where they can review the items to make sure all is ok then if so, pull them over to the main database content.

so, there is more to it but the main import goes something like:

insert into [preview_table](field1, field2...)

select * from OPENROWSET('MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)};

DEFAULTDIR=x:\my_file_path\;Extensions=CSV;',

'SELECT * FROM [my_csv_file.csv]')

Simon G Ten Centuries Points: 1285 More actions · Answer 7

Bit of an update if anyone ever stumbles accross a similar problem.

I've looked into Linked Servers and set one up. It basically points to a folder in my site where the CSV files are uploaded. I can them reference each one directly via that in my script and I don't have to use OPENROWSET anymore.

I created the linked server with:

exec sp_addlinkedserver NW_TEXT, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'Z:\TEXTFILES', NULL, 'Text'

then I can access my csv file using the following:

select * from NW_TEXT...my_file#csv

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

Jeff Moden (12/9/2008)
If you really want to lock things down, use BULK INSERT and format files (if you need them).

It really depends on the format of the CSV file. If they are using quoted text in the CSV, then the format file needed for BULK INSERT gets very ugly very quickly. Also, a format file cannot handle a case such as a CSV generated from Excel or other program where only specific text with a comma in it has the quotes around it instead of all the values for that column in all the rows. In that case, using OPENROWSET or Linked server (especially with the Jet driver) is much more forgiving and easy to use.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

I concur... haphazard formatting is tough on anything that expects a consistant format. OPENROWSET and Text Linked Servers do a pretty job job there.

Of course, when you have haphazard formatting, it's time to get out the Wrist-Rocket and the pork chops and make an up close and personal visit with the data vendor. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)