May 3, 2012 at 9:32 am
We ant to Write all the records of a table to a text file on the local disk with in T-SQL. We do not want BCP as it needs user name and password. pl help, thanks in advance.
May 3, 2012 at 9:54 am
BCP doesn't need user name and password more than any other possible method (stored proc, sql batch script etc,) and it's good for what you asking for.
Use -T option for trusted connection.
May 3, 2012 at 10:58 am
is adding CLR assemblies an option?
I made a suite of CLR export functions as a proof of concept, and i can export to any text data type or HTMl as well; it'as actually very easy to do once you roll up your sleeves and start building CLR stuff;
exec CLR_ExportTableToCSV @TableName ='Products', --Also Accepts #Temp Tables in scope of the connection!
@FilePath ='C:\Data\',
@FileName = 'Products.csv'
@IncludeHeaders = 1
exec CLR_QueryTableToCSV @TableName ='SELECT * FROM Products INNER JOIN Invoices ON Products.ProductID = Invoices.ProductID',
@FilePath ='C:\Data\',
@FileName = 'Products.csv'
@IncludeHeaders = 1
Lowell
May 3, 2012 at 12:46 pm
thanks lowell and Eugene Elutin
BCP: still needs tursted connection, we have many 100s of customer with different authendication, the solution has to work in all situations.
CLR: it function already avail?, or I have to create?, ideally this is what we want. But if I have to build, It still depend on connection (name, pwd) infor? how the CLR will talk to DB?
May 3, 2012 at 1:02 pm
You need to grant permission only to SQL service account on folder containing text file for BCP
May 3, 2012 at 1:04 pm
that is coool, thanks lowell, I go for CLR.
May 3, 2012 at 3:37 pm
Lowell (5/3/2012)
is adding CLR assemblies an option?I made a suite of CLR export functions as a proof of concept, and i can export to any text data type or HTMl as well; it'as actually very easy to do once you roll up your sleeves and start building CLR stuff;
exec CLR_ExportTableToCSV @TableName ='Products', --Also Accepts #Temp Tables in scope of the connection!
@FilePath ='C:\Data\',
@FileName = 'Products.csv'
@IncludeHeaders = 1
exec CLR_QueryTableToCSV @TableName ='SELECT * FROM Products INNER JOIN Invoices ON Products.ProductID = Invoices.ProductID',
@FilePath ='C:\Data\',
@FileName = 'Products.csv'
@IncludeHeaders = 1
I am not a huge fan of SQLCLR objects that require EXTERNAL_ACCESS, but this question comes up a lot and what you have is a far better option IMO than using bcp with xp_CmdShell for those adamant about accessing the file system from within T-SQL. Have you thought about creating a new project on CodePlex?
The function you described would enhance the set of SQLCLR file system helper objects made available by these two contributions:
- FileSystemHelper SQL Server CLR by Jason Strate[/url]
- NCL SQLCLR File Functions by (I think) Elliott Whitlow, SSC Forum frequenter[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 3, 2012 at 7:55 pm
opc.three (5/3/2012)
Lowell (5/3/2012)
is adding CLR assemblies an option?I made a suite of CLR export functions as a proof of concept, and i can export to any text data type or HTMl as well; it'as actually very easy to do once you roll up your sleeves and start building CLR stuff;
exec CLR_ExportTableToCSV @TableName ='Products', --Also Accepts #Temp Tables in scope of the connection!
@FilePath ='C:\Data\',
@FileName = 'Products.csv'
@IncludeHeaders = 1
exec CLR_QueryTableToCSV @TableName ='SELECT * FROM Products INNER JOIN Invoices ON Products.ProductID = Invoices.ProductID',
@FilePath ='C:\Data\',
@FileName = 'Products.csv'
@IncludeHeaders = 1
I am not a huge fan of SQLCLR objects that require EXTERNAL_ACCESS, but this question comes up a lot and what you have is a far better option IMO than using bcp with xp_CmdShell for those adamant about accessing the file system from within T-SQL. Have you thought about creating a new project on CodePlex?
The function you described would enhance the set of SQLCLR file system helper objects made available by these two contributions:
- FileSystemHelper SQL Server CLR by Jason Strate[/url]
- NCL SQLCLR File Functions by (I think) Elliott Whitlow, SSC Forum frequenter[/url]
I notice in one of those links, the author of the CLR claims that he followed "best practices" for security. That brings up a question if someone would answer, please... I log into the SQL Server as me using Windows Authentication. When I run one of these "file delete" CLRs, who am I running within the CLR as and what privs do I actually have via the CLR?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2012 at 10:15 pm
Jeff Moden (5/3/2012)
opc.three (5/3/2012)
Lowell (5/3/2012)
is adding CLR assemblies an option?I made a suite of CLR export functions as a proof of concept, and i can export to any text data type or HTMl as well; it'as actually very easy to do once you roll up your sleeves and start building CLR stuff;
exec CLR_ExportTableToCSV @TableName ='Products', --Also Accepts #Temp Tables in scope of the connection!
@FilePath ='C:\Data\',
@FileName = 'Products.csv'
@IncludeHeaders = 1
exec CLR_QueryTableToCSV @TableName ='SELECT * FROM Products INNER JOIN Invoices ON Products.ProductID = Invoices.ProductID',
@FilePath ='C:\Data\',
@FileName = 'Products.csv'
@IncludeHeaders = 1
I am not a huge fan of SQLCLR objects that require EXTERNAL_ACCESS, but this question comes up a lot and what you have is a far better option IMO than using bcp with xp_CmdShell for those adamant about accessing the file system from within T-SQL. Have you thought about creating a new project on CodePlex?
The function you described would enhance the set of SQLCLR file system helper objects made available by these two contributions:
- FileSystemHelper SQL Server CLR by Jason Strate[/url]
- NCL SQLCLR File Functions by (I think) Elliott Whitlow, SSC Forum frequenter[/url]
I notice in one of those links, the author of the CLR claims that he followed "best practices" for security. That brings up a question if someone would answer, please... I log into the SQL Server as me using Windows Authentication. When I run one of these "file delete" CLRs, who am I running within the CLR as and what privs do I actually have via the CLR?
Had to hit the books, I try not develop anything but SAFE assemblies so it's been a while since I looked into it...after reading up on it again though and trying to boil it down...for objects in an EXTERNAL_ACCESS assembly:
It will depend on two things: the type of login executing the SQLCLR object (SQL or Windows) and whether the object implements impersonation.
- Your case first...If executed by someone in the instance via a Windows Login then the SQLCLR object can access the file system in the context of the associated Windows account if the developer implemented implicit* impersonation within the object (a trivial task), meaning if Windows Login DOMAIN\PersonName executed my SQLCLR function it would access the file system as DOMAIN\PersonName. If not using impersonation it will access the file system using the SQL Server service account (the default). Using the executing Windows account via impersonation while granting that Windows account least privilege to do necessary work in the file system would be my first preference.
- If run by a user in the instance under a SQL Login then the only option would be to have the function access the file system using the SQL Server Service account...not ideal IMO.
- In order to get explicit impersonation, i.e. where a function executed by a person in the instance under any type of login would access the file system under a designated Windows account not belonging to them, the assembly would have to be marked UNSAFE.
References:
Impersonation and CLR Integration Security (msdn)
Using CLR Impersonation to Access Resources Outside of SQL Server by Jonathan Kehayias
* "implicit impersonation" concept attributed to JKehayias
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 3, 2012 at 10:19 pm
A couple more articles I meant to mention by JKehayias
Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents[/url]
Trading in xp_cmdshell for SQLCLR (Part 2) - Export Data to Flat Files
[/url]
Edit: fix URL
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 4, 2012 at 3:49 am
BCP: still needs tursted connection, we have many 100s of customer with different authendication, the solution has to work in all situations.
And, you will want that file be created on the client PC, isn't it?
If so, it will be much easier to have application which will call stored proc (or execute query) to get data over client pc and then write it to text file.
If you use BCP or CLR, the service account which your SQLServer is running under, will need to have access to the machine where you want the file to be placed. I don't think it's a viable option...
May 4, 2012 at 1:21 pm
well i took opc.three's advice and slapped together a project on CodePlex.
There is no doubt in my mind this will help the SQL Community.
If any of you guys have some time, please give it a test drive on one of your Sandbox databases and give me some feedback.
SQLCLRExport.Codeplex.com[/url]
I think it's well documented, so you can copy paste an example from teh docuemtnation and modify it to match your queries and everything.
the HTML exports are really nice examples (patting my own back) with 35 different color styles.
Lowell
May 4, 2012 at 2:03 pm
opc.three (5/3/2012)
- Your case first...If executed by someone in the instance via a Windows Login then the SQLCLR object can access the file system in the context of the associated Windows account if the developer implemented implicit* impersonation within the object (a trivial task), meaning if Windows Login DOMAIN\PersonName executed my SQLCLR function it would access the file system as DOMAIN\PersonName. If not using impersonation it will access the file system using the SQL Server service account (the default). Using the executing Windows account via impersonation while granting that Windows account least privilege to do necessary work in the file system would be my first preference.
Man, thanks for the extra work you went through for this answer. I appreciate it, Orlando.
Correct me if I'm wrong, please, but it sounds like any of the above would allow a pissed off user to delete files that either (s)he had access to or the system had access to...
...someone tell me how that's safer than running xp_CmdShell to do the same thing (delete files).
The real key here is that users should not be allowed to do things like delete files. ONLY the system should be allowed to do that. Users should ONLY have PUBLIC privs and the ability to execute certain stored procedures. Certainly, they should never be given privs to be able to name the file they want to delete whether it be through xp_CmdShell or some SQLCLR utility.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2012 at 3:20 pm
An excellent point on security Jeff, I'm still mulling it over.
I was thinking that a CLR added a layer of flexibility in there. You don't have to give the end users access to the core CLR procedures/functiosn that get created; you could wrap calls to them with your own proc that does stuff like hardcodes the directory, and even creates the file names based on, say user/getdate so they never overwrite an existing file.
the core proc would give you a bit of flexibility, but i suppose you could create your own proc wrapped around xp_cmdshell that does the smae limitation logic, right?
something like this that i jjust tested?
Create Procedure CLR_TighterSecurity @sQueryCommand nvarchar(4000),
@sIncludeHeaders int,
@sTitle nvarchar(4000),
@sSummary nvarchar(4000),
@sHTMLStyle int
AS
BEGIN
SET NOCOUNT ON
DECLARE @sFilePath nvarchar(4000),
@sFileName nvarchar(4000)
SET @sFilePath = 'C:\Data\'
SET @sFileName = REPLACE(SUSER_NAME(),'\','_')
+ '_'
+ CONVERT(VARCHAR,GETDATE(),112)
+ '-'
+ REPLACE(CONVERT(VARCHAR,GETDATE(),114),':','')
+ '.html'
--Above returns a fileName like 'MyDomain_lizaguirre_20120504-170747007.html
EXECUTE CLR_ExportQueryToHTML @QueryCommand = @sQueryCommand,
@FilePath = @sFilePath,
@FileName = @sFileName,
@IncludeHeaders = @sIncludeHeaders,
@Title = @sTitle,
@Summary = @sSummary,
@HTMLStyle = @sHTMLStyle
END --PROC
Lowell
May 4, 2012 at 9:47 pm
All good points and thanks for your time, Lowell. You've actually emphasized the very point that I'm trying to get across.
That point is that SQLCLR is NOT some magical beast that's automatically safer than using xp_CmdShell. Like anything else, "It Depends". You have to work at making things secure using wrapper code like Lowell suggested and then limiting the user to only being able to execute the wrapper code and not the CLRs themselves.
I certainly could be wrong but, as of right now and without the properly secured wrapper code, I don't see how SQLCLR is any safer than xp_CmdShell. Both require extra work to be used safely in the areas of such things as file handling.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply