August 13, 2012 at 12:11 pm
I have stored procedure which includes bulk insert, activating and deactivating the access to xp_cmdshell and executing xp_cmdshell. I am trying to get this procedure to run without the user being in the sysadmin fixed server role. I got bogged down in fixed server roles and proxy users for xp_cmdshell and then saw a reference to creating the procedure using the with execute as option. I have re-created the procedure using the with execute as <use in sysadmin role> and granted the non-sysadmin user the rights to execute the procedure. When I try to run it logged in as the non-sysadmin user it errors out on the bulk insert statement as follows:
You do not have permission to use the bulk load statement.
Everything I've seen about using the execute as option when creating the stored procedure indicates that the procedure will be run with the permissions of the specified execute as user. The specified user is in the sysadmin role and can run the procedure if I log in as him. I would appreciate any suggestions as to what I am doing wrong.
Thanks
Chuck
August 14, 2012 at 10:28 am
From my perspective you should be looking at turning this process inside-out and using PowerShell to execute whatever you're doing with xp_cmdshell, bcp.exe to load your file and sqlcmd or the ADO.NET classes to execute T-SQL. A new SSIS package would also be a great candidate to replace the T-SQL process. If you want to hear more about either of those options that let me know.
If you want to continue with everything in T-SQL:
Database Users are not the same as Server Logins. Database Users are not included in the sysadmin Fixed Server Role, Server Logins are. BULK INSERT requires server-level permissions.
In order to accomplish this task I would recommend you look into signing your procedure with a certificate based on a user in the master database with the server-level permissions necessary to call BULK INSERT as well as exec xp_cmdshell.
Signing Stored Procedures with a Certificate (SQL Server 2005)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 15, 2012 at 7:37 am
My background is a good bit of SQL scripting mostly self taught and enough vb scripting to get me by. I am willing to learn anything that will help processes run better. I'm reviewing and testing the link you provided but would love to hear about options. Thanks for your help
Chuck
August 15, 2012 at 9:58 am
Chuck 17099 (8/15/2012)
My background is a good bit of SQL scripting mostly self taught and enough vb scripting to get me by. I am willing to learn anything that will help processes run better. I'm reviewing and testing the link you provided but would love to hear about options. Thanks for your helpChuck
Sorry but I must start with a disclaimer: we're talking about security and division of responsibility, i.e. architecture, so one is not necessarily better or worse. Further to that, these are general suggestions. These solutions may not be conducive in your environment, depending on logistics, security scheme, and many other variables I am not privy to.
BULK INSERT and xp_cmdshell are powerful tools but they arrive with lots of baggage in the area of security as you have learned. Many people simply blast through the roadblocks and start adding people to the sysadmin Role or setting up the proxy and granting exec on xp_cmdshell without understanding the ramifications of doing something like that, so kudos to you for doing better.
Alternative 1
Create a PowerShell script that:
- leverages the .NET SqlClient library to execute stored procedures as needed
- in place of BULK INSERT use bcp to load flat files into database tables
- in place of xp_cmdshell since we're in a PowerShell context you can simply execute what is needed using PowerShell native commands or the same commands/executables you were calling using xp_cmdshell
Excerpt from the Permissions section in bcp Utility (SQL Server 2005):
A bcp in operation minimally requires SELECT/INSERT permissions on the target table.
Your users can then execute the PowerShell script.
Alternative 2
Create an SSIS package that:
- uses the Execute SQL Task to execute stored procedures as needed
- in place of BULK INSERT use a Data Flow Task to load flat files into database tables
- in place of xp_cmdshell use the Execute Process Task to execute cmd-shell commands or other executables
Your users can then execute the SSIS package directly using dtexec.exe.
Alternative 3
Setup an asynchronous Service Broker (SB) queue that your users can insert a 'request to execute said proc' into. The worker process that services that queue would then asynchronously execute your proc in a different security context, one with the permissions necessary to run BULK INSERT and xp_cmdshell.
This option is not interactive, i.e. the caller would receive confirmation when inserting into the SB queue that a message was added to the queue but they would not necessarily know the outcome of the SB worker process unless they checked back later.
--
In closing, signing a proc that uses BULK INSERT and xp_cmdshell so an interactive user can successfully exec the proc while still maintaining a very limited set of database permissions has merits and demerits. The same applies to the options I show above.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 15, 2012 at 10:11 am
Thank you. I will experiment with each of the options you have suggested.
Chuck
August 15, 2012 at 10:17 am
My pleasure. If you have any issues or doubts after you start experimenting with any of the options feel free to post back and I'll do my best to answer your questions.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 15, 2012 at 12:03 pm
Just to chime in, I'd go with SSIS. It's designed to do this kind of work. It's easy to get started in it (at least, I remember it being so when I started on it), and it does a good job.
- 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
August 15, 2012 at 1:42 pm
I have experimented with certificates and took a quick look at Power Shell and SSIS and I think SSIS is probably my best options. I found an artical on using SSIS with examples. I wants be to launch Business Intelligence Development Studio [which is basically Visual Studio] and create a new project and select Business Intelligence Projects project type. That type is not one that I have on my list. I tried installing Business Intelligence Development Studio from my SQL 2008 R2 DVD and it is still not on the list. Is this something I can install seperately as a plugin?
Never mind. I figured it out
August 16, 2012 at 6:12 am
What edition of SQL Server are you using?
- 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
August 16, 2012 at 11:03 am
I'm using 2008 r2 but I figured it out. The path I was given wasn't quit right. Sorry for bothering you
August 16, 2012 at 11:12 am
No problem.
By "edition", I meant "developer", "enterprise", "standard", "express", and so on. Express doesn't come with some of the BIDS options, if I remember correctly. That's why I was asking.
Glad you figured it out.
- 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
August 17, 2012 at 1:46 pm
I have decided to use a SQL solution as it does not require any software to be installed on user workstations. I have setup two SQL users. User1 is in the bulk load server role, is in the datareader role in all databases and has execute permission on my test procedure. User2 has execute permissions on my test procedure and can impersonate User1. The test procedure is as follows:
if OBJECT_ID('tempdb..#t') is not null drop table #t
create table #t (f1 varchar(50),f2 varchar(50),f3 varchar(50))
execute as login='User1'
select USER_NAME()
select * from TWO..GL00100
bulk insert #t from 'E:\test.txt';select * from #t
select USER_NAME()
revert
select USER_NAME()
If I rimmout the bulk insert command user2 can execute the procedure to include selecting data from a database he doew not have access to without error. If I leave the bulk insert comment in he gets the following error:
You do not have permission to use the bulk load statement.
The impersonate command was done at the master database using the following command:
use master;grant IMPERSONATE ON LOGIN::User1 TO User2
Does the impersonate command not include server roles or have I missed something?
Thanks
Chuck
August 17, 2012 at 1:52 pm
Chuck 17099 (8/17/2012)
I have decided to use a SQL solution as it does not require any software to be installed on user workstations.
Are you sure? For the record:
If they have SSMS 2005 installed they already have the means to execute an SSIS package (dtexec.exe).
If they are on Windows 7 or above they have PowerShell 2.0.
Moving on...
I have setup two SQL users. User1 is in the bulk load server role, is in the datareader role in all databases and has execute permission on my test procedure. User2 has execute permissions on my test procedure and can impersonate User1.
Users (i.e. Database Users) do not get BULK INSERT permissions. Server Logins do.
The test procedure is as follows:
if OBJECT_ID('tempdb..#t') is not null drop table #t
create table #t (f1 varchar(50),f2 varchar(50),f3 varchar(50))
execute as login='User1'
select USER_NAME()
select * from TWO..GL00100
bulk insert #t from 'E:\test.txt';select * from #t
select USER_NAME()
revert
select USER_NAME()
If I rimmout the bulk insert command user2 can execute the procedure to include selecting data from a database he doew not have access to without error. If I leave the bulk insert comment in he gets the following error:
You do not have permission to use the bulk load statement.
The impersonate command was done at the master database using the following command:
use master;grant IMPERSONATE ON LOGIN::User1 TO User2
Does the impersonate command not include server roles or have I missed something?
Thanks
Chuck
I have not accomplished this using EXECUTE AS, although it may be possible. Certificate signing works for sure.
Erland Sommarskog has good info in this area: http://www.sommarskog.se/grantperm.html#EXECUTE_AS
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 17, 2012 at 2:08 pm
Embedding the Execute As Login in an Exec command worked. I am not sure what the difference is but in that it is not longer broke I am not going to fix it. Thanks for your help. Couldn't have done it without you.
Chuck
August 17, 2012 at 2:52 pm
Thanks for posting back. Could you post the code that worked for you? Personally I am curious to see what finally worked but maybe others can benefit too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply