Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENROWSET - To use or not to use. Expand / Collapse
Author
Message
Posted Tuesday, December 9, 2008 2:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 2:03 AM
Points: 39, 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.
Post #616505
Posted Tuesday, December 9, 2008 2:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #616528
Posted Tuesday, December 9, 2008 2:59 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Linked servers do not require the use of "openrowset/openquery/opendatasource".
You may want to try that instead.



* Noel
Post #616546
Posted Tuesday, December 9, 2008 3:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 2:03 AM
Points: 39, 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!
Post #616565
Posted Tuesday, December 9, 2008 7:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #616615
Posted Tuesday, December 9, 2008 9:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #616654
Posted Wednesday, December 10, 2008 1:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 2:03 AM
Points: 39, 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]')

Post #616743
Posted Wednesday, December 10, 2008 7:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 2:03 AM
Points: 39, 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

Post #616978
Posted Wednesday, December 10, 2008 9:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:30 AM
Points: 1,950, Visits: 2,122
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.
Post #617172
Posted Wednesday, December 10, 2008 5:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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. :P


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #617497
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse