November 4, 2010 at 12:56 pm
I have a vendor system with a SQL2008 back end. We put their database on a large server that houses several other databases because of the large disk array. Now the vendor wants one of their SPs to create a .txt on a fax server to initiate a fax. A share was created on the fax server and a domain account created with rights to the share. The domain account was made an xp_cmdshell proxy, and the vendor's SQL Server login was granted exec to xp_cmdshell. It works as expected and since the proxy account ONLY has access to the fax share on our domain, they can't use xp_cmdshell to write files anywhere else. That's how it appears anyway. But is this the only way? With all I read about xp_cmdshell being a risk, what other option is there? I have a working spOACreate script that will create a file, but SQL2008 BOL says you must be a sysadmin to run sp_OA procedures. So what can one do within an SP, other than xp_cmdshell, to create a .txt without being a sysadmin?
Also, I opened query analyzer with the vendor's sql server account and tried to create files on various servers and shares on our domain and got Access Denied as I expected. Anything else I should be checking to verify this account is truely restricted?
Thanks all.
November 4, 2010 at 1:20 pm
Since VB.NET can create text files pretty easily, a CLR proc would do the job, and be more secure than xp_cmdshell.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 1:26 pm
Another option would be to create an SSIS package and run it from within your sproc.
November 4, 2010 at 1:32 pm
Wouldn't an SSIS package require xp_cmdshell to run dtexec?
November 4, 2010 at 1:55 pm
GSquared (11/4/2010)
Since VB.NET can create text files pretty easily, a CLR proc would do the job, and be more secure than xp_cmdshell.
how would you get the CLR to write the results? say something simple as an example, like select * from sys.tables?
Would you have to pass the query to the CLR, where it would be executed and then written with a datareader? can you pass a CLR a table variable? dump the results to a temp table and have the CLR read that?
on the Visual Studio side of the CLR, once the data is in a reader or datatable, I know what to do with it, that's not really my question, but it's getting the results to the CLR I'm wondering about...is there a shortcut getting the results of an existing query to the CLR?
Lowell
November 4, 2010 at 2:12 pm
A CLR proc called by an outer proc has access to temp tables created in the calling proc, just like any other subproc does.
Or run the query in the CLR proc using a data reader or whatever. You'll need one of those for the temp table anyway, if you use that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 2:17 pm
You might want to consider database mail as a way of sending faxes. You have a lot of flexibility, even passing a query to the senddbmail method. It is very easy to control access to msdb and the senddbmail procedure.
The assumption here is that your mailserver can send the fax or hand the message off to another processes to be faxed. No access to the file system is required.
The probability of survival is inversely proportional to the angle of arrival.
November 4, 2010 at 2:19 pm
sturner (11/4/2010)
You might want to consider database mail as a way of sending faxes. You have a lot of flexibility, even passing a query to the senddbmail method. It is very easy to control access to msdb and the senddbmail procedure.The assumption here is that your mailserver can send the fax or hand the message off to another processes to be faxed. No access to the file system is required.
That's possibly a very good idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 2:33 pm
There are several products (GFI Faxmaker is one) that work with Exchange or any other SMTP server to provide fax capability. This technology is reliable and has all of the good features like logging and retry that you would want to have and can be used by any email client in your network, not just the SQL server.
The probability of survival is inversely proportional to the angle of arrival.
November 4, 2010 at 3:00 pm
You might want to consider database mail as a way of sending faxes. You have a lot of flexibility, even passing a query to the senddbmail method. It is very easy to control access to msdb and the senddbmail procedure.
The assumption here is that your mailserver can send the fax or hand the message off to another processes to be faxed. No access to the file system is required.
Yes, that is a good idea. I use database mail quite often. But in this case it's not appropriate. We are a health care system and have a handfull of ancillary systems that send faxes to physician's offices and such. So we fax a lot and have a fax server & backup fax server that are monitored 24/7 by our operations staff. I'm sure our SysAdmins told this vendor to "put a file here formatted like so and it will get faxed".
The faxes are going to go. My question is more about having to use the file system and how best to do it. I use xp_cmdshell all over the place, but I'm the DBA and I'm a sysadmin and I'm trustworthy. When a vendor's development folks tell me they need xp_cmdshell I'm taken aback a little. But really, their options are limited. To make a flat file from Tsql you always see "use BCP, OSQL, SSIS or OLE/COM". But to call BCP, OSQL or SSIS from a proc requires using xp_cmdshell and you have to be a sysadmin to use OLE Automation sps. Next you read xp_cmdshell is a security risk. So what's one to do.
November 4, 2010 at 3:02 pm
Randy Doub (11/4/2010)
You might want to consider database mail as a way of sending faxes. You have a lot of flexibility, even passing a query to the senddbmail method. It is very easy to control access to msdb and the senddbmail procedure.
The assumption here is that your mailserver can send the fax or hand the message off to another processes to be faxed. No access to the file system is required.
Yes, that is a good idea. I use database mail quite often. But in this case it's not appropriate. We are a health care system and have a handfull of ancillary systems that send faxes to physician's offices and such. So we fax a lot and have a fax server & backup fax server that are monitored 24/7 by our operations staff. I'm sure our SysAdmins told this vendor to "put a file here formatted like so and it will get faxed".
The faxes are going to go. My question is more about having to use the file system and how best to do it. I use xp_cmdshell all over the place, but I'm the DBA and I'm a sysadmin and I'm trustworthy. When a vendor's development folks tell me they need xp_cmdshell I'm taken aback a little. But really, their options are limited. To make a flat file from Tsql you always see "use BCP, OSQL, SSIS or OLE/COM". But to call BCP, OSQL or SSIS from a proc requires using xp_cmdshell and you have to be a sysadmin to use OLE Automation sps. Next you read xp_cmdshell is a security risk. So what's one to do.
That's what CLR in SQL 2005 was implemented to handle. That exact set of problems.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 3:18 pm
That's what CLR in SQL 2005 was implemented to handle. That exact set of problems.
- GSquared
Yea, I guess that's right.
I was hoping I could retire before having to learn CLR and .NET 🙂
November 4, 2010 at 3:19 pm
Would it be an option to create a sproc executed as a different user (impersonation) who's allowed to run xp_cmdshell with the package source path as input variable and assign execute permission to the 3rd party folks? That way they'd be unable to misuse xp_cmdshell...
Another option would be to create a job that will run the SSIS package and assigne the permission to start and stop that job, but not to view or alter it.
November 4, 2010 at 6:06 pm
How about one of the following, each of which have some degree of separation from the application that enables you to run the code that writes the fax file using a different security context....
1. The application writes relevant info to a table. The existing (I assume) sproc is executed periodically by SQL Server Agent. The SQL Server Agent account is given enought privilege to run xm_cmdshell. The account used by the users to run the application does not need to run xp_cmdshell. Schedule the job to run as frequently as needed.
2. Similar to above except the SQL Agent job executes a SSIS package to do the work. No need to xp_cmdshell at all.
3. Write to a queue (using Service Broker). You can implement whatever you need to write the file (could be the existing sproc, a SSIS package, .Net application etc.
November 4, 2010 at 7:06 pm
Lowell (11/4/2010)
GSquared (11/4/2010)
Since VB.NET can create text files pretty easily, a CLR proc would do the job, and be more secure than xp_cmdshell.how would you get the CLR to write the results? say something simple as an example, like select * from sys.tables?
Would you have to pass the query to the CLR, where it would be executed and then written with a datareader? can you pass a CLR a table variable? dump the results to a temp table and have the CLR read that?
on the Visual Studio side of the CLR, once the data is in a reader or datatable, I know what to do with it, that's not really my question, but it's getting the results to the CLR I'm wondering about...is there a shortcut getting the results of an existing query to the CLR?
use a streamwriter object. Just instantiate it with the appropriate file name, etc... after that - writing to it is simple.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply