SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OPENROWSET - To use or not to use.


OPENROWSET - To use or not to use.

Author
Message
Simon G
Simon G
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 145
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151658 Visits: 19455
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
noeld
noeld
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23590 Visits: 2048
Linked servers do not require the use of "openrowset/openquery/opendatasource".
You may want to try that instead.


* Noel
Simon G
Simon G
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 145
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
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223759 Visits: 42006
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151658 Visits: 19455
The big thing is do you know the format of the file ahead of time or not?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Simon G
Simon G
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 145
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
Simon G
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 145
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
Chris Harshman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11226 Visits: 4714
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
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223759 Visits: 42006
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. Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search