dts or xp_cmdshell permissions

How do i give a user permissions to execute XP_CMDSHELL or a DTS package?
Without compromising the systems security.
Basically what im trying to do is use an asp page on another domain to
access mssql and create a database. Everything works fine except that i
cannot create a folder on the server.
I need to first create a folder by passing the domain name to xp_cmdshell on
the local drive. Then creating the database.
The account accessing the mssql server is dbm.something.com which is in the
master database. This sends arguments to a stores procedure that creates the
database. I added a line to the stored procedure to run xp_cmdshell and
create a database. Once it gets to this line it gives me an error
xpsql.c:Error 1314 from CreateProcessAsUser on line: 432.
Please help me
Thank you
The error 1314 you are getting is:
A required privilege is not held by the client.
Could be that your SQL service account doesn't have the
correct rights to allow it to change security context to the
proxy account. The SQL service account needs Act as part of
OS, Increase Quotas, replace process level token and Login
as batch job.
-Sue
On Wed, 21 Sep 2005 16:15:04 -0400, "robert" <rob@.ms.com>
wrote:

>How do i give a user permissions to execute XP_CMDSHELL or a DTS package?
>Without compromising the systems security.
>Basically what im trying to do is use an asp page on another domain to
>access mssql and create a database. Everything works fine except that i
>cannot create a folder on the server.
>I need to first create a folder by passing the domain name to xp_cmdshell on
>the local drive. Then creating the database.
>The account accessing the mssql server is dbm.something.com which is in the
>master database. This sends arguments to a stores procedure that creates the
>database. I added a line to the stored procedure to run xp_cmdshell and
>create a database. Once it gets to this line it gives me an error
>xpsql.c:Error 1314 from CreateProcessAsUser on line: 432.
>Please help me
>Thank you
>
|||Yeah i read that somewhere, but i dont know how to give it the permissions.
Where do i do that ? act as part of OS and logon as batch are windows
permissions. I dont have a proxy account for SQL under the local users or
domain users. What am i missing here?
Thank for helping
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:feijj1dvrbpvrjepce3jrvqbkj2pdi6l6e@.4ax.com...
> The error 1314 you are getting is:
> A required privilege is not held by the client.
> Could be that your SQL service account doesn't have the
> correct rights to allow it to change security context to the
> proxy account. The SQL service account needs Act as part of
> OS, Increase Quotas, replace process level token and Login
> as batch job.
> -Sue
> On Wed, 21 Sep 2005 16:15:04 -0400, "robert" <rob@.ms.com>
> wrote:
>
|||First, your missing the rights for the service account. The
permissions are outlined in the following article but if you
set the service accounts through Enterprise Manager, the
rights and permissions are handled for you:
HOW TO: Change the SQL Server or SQL Server Agent Service
Account Without Using SQL Enterprise Manager in SQL Server
2000
http://support.microsoft.com/?id=283811
You can use the Local Security Policy snap in to view the
permissions. From the run command from the start button,
type in secpol.msc
If accounts other than sysadmin accounts or the service
accounts are going to be used to execute xp_cmdshell, you
need to setup a proxy account. You can use
xp_sqlagent_proxy_account
You can find more information in books online under
xp_sqlagent_proxy_account and xp_cmdshell.
-Sue
On Wed, 28 Sep 2005 12:18:24 -0400, <rvegas@.rogers.com>
wrote:

>Yeah i read that somewhere, but i dont know how to give it the permissions.
>Where do i do that ? act as part of OS and logon as batch are windows
>permissions. I dont have a proxy account for SQL under the local users or
>domain users. What am i missing here?
>Thank for helping
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:feijj1dvrbpvrjepce3jrvqbkj2pdi6l6e@.4ax.com.. .
>
|||Thanx for your help Sue, still a bit confused will have to read up on this.
Under enterprise manager >>management>sqlserver agent>job system tab.
I have "only sysadmins can run active scripting jobs" checked. I dont want
to change this. Is there another way?
Sorry for all the stupid questions. just dont want to mess anything up.
Thanx
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:55jlj1p4m2s30gcolq6i9ok7gqcs16vjjj@.4ax.com...
> First, your missing the rights for the service account. The
> permissions are outlined in the following article but if you
> set the service accounts through Enterprise Manager, the
> rights and permissions are handled for you:
> HOW TO: Change the SQL Server or SQL Server Agent Service
> Account Without Using SQL Enterprise Manager in SQL Server
> 2000
> http://support.microsoft.com/?id=283811
> You can use the Local Security Policy snap in to view the
> permissions. From the run command from the start button,
> type in secpol.msc
> If accounts other than sysadmin accounts or the service
> accounts are going to be used to execute xp_cmdshell, you
> need to setup a proxy account. You can use
> xp_sqlagent_proxy_account
> You can find more information in books online under
> xp_sqlagent_proxy_account and xp_cmdshell.
> -Sue
> On Wed, 28 Sep 2005 12:18:24 -0400, <rvegas@.rogers.com>
> wrote:
>
|||No problem. You could have the users execute the package
using the DTS object model. There are a few different
options with that and you could use another application such
as ASP. The following have some examples:
Execute a package with OLE Automation -
http://www.databasejournal.com/featu...le.php/1459181
Execute a package from ASP -
http://www.sqldts.com/default.aspx?207
Execute a package from VB -
http://www.sqldts.com/default.aspx?208
-Sue
On Fri, 7 Oct 2005 16:56:33 -0400, <rvegas@.rogers.com>
wrote:

>Thanx for your help Sue, still a bit confused will have to read up on this.
>Under enterprise manager >>management>sqlserver agent>job system tab.
>I have "only sysadmins can run active scripting jobs" checked. I dont want
>to change this. Is there another way?
>Sorry for all the stupid questions. just dont want to mess anything up.
>Thanx
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:55jlj1p4m2s30gcolq6i9ok7gqcs16vjjj@.4ax.com.. .
>

DTS Migration from SQL Server 2000

I’m currently evaluating SQL Server 2005 to what advantages there would be to upgrading from SQL Server 2000. Our existing systems make much use of DTS so it critical for us to be able to run SQL Server 2000 DTS packages in SQL Server 2005.

Having installed the Enterprise SQL Server 2005 CTP successfully on a new Windows 2003 server and following the advice to install Legacy Components I find the Migration Wizard (under Legacy> Data Transformation Services) fails with the following error after entering source and target dialogs:

This wizard will close because it encountered the following error:

Additional Information:

Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index (mscorlib)

Also when trying to open a structured storage DTS file I incur another error:

Unable to load DTS 2000 Runtime. This component may have not been installed

(Microsoft.SqlServer.DtsObjectExplorerUI)

This error seems to imply that the DTS 2000 Runtime needs installing. Can this component be downloaded and installed separately?

John,
The first error sounds like it *could* be a bug. Are you able to open a bug at betaplace?

Regarding your second error....you cannot open a DTS2000 package for editing using SQL Server 2005 tools so don't even bother trying. You need a SQL2000 instance in order to edit your DTS2000 packages.

Happily SQL2000 and SQL2005 can run alongside each other with one (or both) of them as a named instance.

-Jamie|||

Thanks Jamie,

I am not a member of betaplace. Could someone set me up with guest id to log the 1st problem as a possible bug? I’ll install an instance of SQL2000 to overcome the 2nd problem.

|||

Several forum users have reported the following error message when attempting to migrate a DTS package:

TITLE: Microsoft SQL Server
This wizard will close because it encountered the following error:
...
ADDITIONAL INFORMATION:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)
...

One cause of this error, which can easily be reproduced, is a space before or (more likely) after the name of the package.

Unfortunately this problem is not currently detected by Upgrade Advisor when it scans package names for invalid characters.

-Doug

|||

I have the same problem after uppgrading from Sql 2000 Personal to Sql 2005 Standard. The server has been uppgrade from win2000 standard server SP4 to win 2003 standard server SP1

In Mannagement > Legacy folder i'v have altso problems deleting old Mainenance plans. When I delete it it seems do disapair, but when i'm refresh it it comes back.

I have no space in the name of the DTS package

- Rolf

DTS Migration from SQL Server 2000

I’m currently evaluating SQL Server 2005 to what advantages there would be to upgrading from SQL Server 2000. Our existing systems make much use of DTS so it critical for us to be able to run SQL Server 2000 DTS packages in SQL Server 2005.

Having installed the Enterprise SQL Server 2005 CTP successfully on a new Windows 2003 server and following the advice to install Legacy Components I find the Migration Wizard (under Legacy> Data Transformation Services) fails with the following error after entering source and target dialogs:

This wizard will close because it encountered the following error:

Additional Information:

Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index (mscorlib)

Also when trying to open a structured storage DTS file I incur another error:

Unable to load DTS 2000 Runtime. This component may have not been installed

(Microsoft.SqlServer.DtsObjectExplorerUI)

This error seems to imply that the DTS 2000 Runtime needs installing. Can this component be downloaded and installed separately?

John,
The first error sounds like it *could* be a bug. Are you able to open a bug at betaplace?

Regarding your second error....you cannot open a DTS2000 package for editing using SQL Server 2005 tools so don't even bother trying. You need a SQL2000 instance in order to edit your DTS2000 packages.

Happily SQL2000 and SQL2005 can run alongside each other with one (or both) of them as a named instance.

-Jamie|||

Thanks Jamie,

I am not a member of betaplace. Could someone set me up with guest id to log the 1st problem as a possible bug? I’ll install an instance of SQL2000 to overcome the 2nd problem.

|||

Several forum users have reported the following error message when attempting to migrate a DTS package:

TITLE: Microsoft SQL Server
This wizard will close because it encountered the following error:
...
ADDITIONAL INFORMATION:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)
...

One cause of this error, which can easily be reproduced, is a space before or (more likely) after the name of the package.

Unfortunately this problem is not currently detected by Upgrade Advisor when it scans package names for invalid characters.

-Doug

|||

I have the same problem after uppgrading from Sql 2000 Personal to Sql 2005 Standard. The server has been uppgrade from win2000 standard server SP4 to win 2003 standard server SP1

In Mannagement > Legacy folder i'v have altso problems deleting old Mainenance plans. When I delete it it seems do disapair, but when i'm refresh it it comes back.

I have no space in the name of the DTS package

- Rolf

DTS Migration from SQL Server 2000

I’m currently evaluating SQL Server 2005 to what advantages there would be to upgrading from SQL Server 2000. Our existing systems make much use of DTS so it critical for us to be able to run SQL Server 2000 DTS packages in SQL Server 2005.

Having installed the Enterprise SQL Server 2005 CTP successfully on a new Windows 2003 server and following the advice to install Legacy Components I find the Migration Wizard (under Legacy> Data Transformation Services) fails with the following error after entering source and target dialogs:

This wizard will close because it encountered the following error:

Additional Information:

Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index (mscorlib)

Also when trying to open a structured storage DTS file I incur another error:

Unable to load DTS 2000 Runtime. This component may have not been installed

(Microsoft.SqlServer.DtsObjectExplorerUI)

This error seems to imply that the DTS 2000 Runtime needs installing. Can this component be downloaded and installed separately?

John,
The first error sounds like it *could* be a bug. Are you able to open a bug at betaplace?

Regarding your second error....you cannot open a DTS2000 package for editing using SQL Server 2005 tools so don't even bother trying. You need a SQL2000 instance in order to edit your DTS2000 packages.

Happily SQL2000 and SQL2005 can run alongside each other with one (or both) of them as a named instance.

-Jamie|||

Thanks Jamie,

I am not a member of betaplace. Could someone set me up with guest id to log the 1st problem as a possible bug? I’ll install an instance of SQL2000 to overcome the 2nd problem.

|||

Several forum users have reported the following error message when attempting to migrate a DTS package:

TITLE: Microsoft SQL Server
This wizard will close because it encountered the following error:
...
ADDITIONAL INFORMATION:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)
...

One cause of this error, which can easily be reproduced, is a space before or (more likely) after the name of the package.

Unfortunately this problem is not currently detected by Upgrade Advisor when it scans package names for invalid characters.

-Doug

|||

I have the same problem after uppgrading from Sql 2000 Personal to Sql 2005 Standard. The server has been uppgrade from win2000 standard server SP4 to win 2003 standard server SP1

In Mannagement > Legacy folder i'v have altso problems deleting old Mainenance plans. When I delete it it seems do disapair, but when i'm refresh it it comes back.

I have no space in the name of the DTS package

- Rolf

DTS in SQL Server 2005

Hi,
I'm in the process of migrating several systems from SQL Server 2000 to SQL
Server 2005.
On the existing SQL Server 2000 server, I have several DTS packages, one of
which uses the Copy Database feature to synchronise data on the live box
with my development box. This is an ad-hoc function e.g. a user reports an
error, so I use the DTS package to copy the live data from the particular
database in question down to my development machine so that I can compare
"like for like", if you see what I mean. Often, errors like this are
data-specific, so I need to see exactly what the user is seeing.
This DTS package contained as many Copy Database steps as there are
databases on the server(s). This means that I could open the package and
execute one or more of them as required.
I migrated this (and the other) DTS packages from SQL Server 2000 into SQL
Server 2005 without error. In SQL Server 2005 Management Studio when I
connect to Integration Services and open up Stored Packages\MSDB\DEV, all of
the packages are there. However, when I open the one in question, I don't
appear to see the individual Copy Database components so that I can run them
individually.
I tried to use the SQL Server 2005 Copy Database Wizard, but was unable to
because the live server is actually hosted by my ISP. This means that the
Copy Database Wizard errors when it tries to iterate through the various
databases on my ISP's SQL Server box, because the first one doesn't belong
to me.
Is this not the way SQL Server 2005 does things? Am I going about this the
wrong way?
Any assistance gratefully received.
Mark"Mark Rae" <mark@.markN-O-S-P-A-M.co.uk> wrote in message
news:uACp2i4NGHA.2816@.TK2MSFTNGP15.phx.gbl...
No-one got any ideas...?

DTS in SQL Server 2005

Hi,
I'm in the process of migrating several systems from SQL Server 2000 to SQL
Server 2005.
On the existing SQL Server 2000 server, I have several DTS packages, one of
which uses the Copy Database feature to synchronise data on the live box
with my development box. This is an ad-hoc function e.g. a user reports an
error, so I use the DTS package to copy the live data from the particular
database in question down to my development machine so that I can compare
"like for like", if you see what I mean. Often, errors like this are
data-specific, so I need to see exactly what the user is seeing.
This DTS package contained as many Copy Database steps as there are
databases on the server(s). This means that I could open the package and
execute one or more of them as required.
I migrated this (and the other) DTS packages from SQL Server 2000 into SQL
Server 2005 without error. In SQL Server 2005 Management Studio when I
connect to Integration Services and open up Stored Packages\MSDB\DEV, all of
the packages are there. However, when I open the one in question, I don't
appear to see the individual Copy Database components so that I can run them
individually.
Is this not the way SQL Server 2005 does things? Am I going about this the
wrong way?
Any assistance gratefully received.
Mark"Mark Rae" <mark@.markN-O-S-P-A-M.co.uk> wrote in message
news:%23z5LgPsNGHA.2920@.TK2MSFTNGP10.phx.gbl...
No-one got any ideas...?

DTS in SQL Server 2005

Hi,
I'm in the process of migrating several systems from SQL Server 2000 to SQL
Server 2005.
On the existing SQL Server 2000 server, I have several DTS packages, one of
which uses the Copy Database feature to synchronise data on the live box
with my development box. This is an ad-hoc function e.g. a user reports an
error, so I use the DTS package to copy the live data from the particular
database in question down to my development machine so that I can compare
"like for like", if you see what I mean. Often, errors like this are
data-specific, so I need to see exactly what the user is seeing.
This DTS package contained as many Copy Database steps as there are
databases on the server(s). This means that I could open the package and
execute one or more of them as required.
I migrated this (and the other) DTS packages from SQL Server 2000 into SQL
Server 2005 without error. In SQL Server 2005 Management Studio when I
connect to Integration Services and open up Stored Packages\MSDB\DEV, all of
the packages are there. However, when I open the one in question, I don't
appear to see the individual Copy Database components so that I can run them
individually.
I tried to use the SQL Server 2005 Copy Database Wizard, but was unable to
because the live server is actually hosted by my ISP. This means that the
Copy Database Wizard errors when it tries to iterate through the various
databases on my ISP's SQL Server box, because the first one doesn't belong
to me.
Is this not the way SQL Server 2005 does things? Am I going about this the
wrong way?
Any assistance gratefully received.
Mark
"Mark Rae" <mark@.markN-O-S-P-A-M.co.uk> wrote in message
news:uACp2i4NGHA.2816@.TK2MSFTNGP15.phx.gbl...
No-one got any ideas...?

DTS in SQL Server 2005

Hi,
I'm in the process of migrating several systems from SQL Server 2000 to SQL
Server 2005.
On the existing SQL Server 2000 server, I have several DTS packages, one of
which uses the Copy Database feature to synchronise data on the live box
with my development box. This is an ad-hoc function e.g. a user reports an
error, so I use the DTS package to copy the live data from the particular
database in question down to my development machine so that I can compare
"like for like", if you see what I mean. Often, errors like this are
data-specific, so I need to see exactly what the user is seeing.
This DTS package contained as many Copy Database steps as there are
databases on the server(s). This means that I could open the package and
execute one or more of them as required.
I migrated this (and the other) DTS packages from SQL Server 2000 into SQL
Server 2005 without error. In SQL Server 2005 Management Studio when I
connect to Integration Services and open up Stored Packages\MSDB\DEV, all of
the packages are there. However, when I open the one in question, I don't
appear to see the individual Copy Database components so that I can run them
individually.
I tried to use the SQL Server 2005 Copy Database Wizard, but was unable to
because the live server is actually hosted by my ISP. This means that the
Copy Database Wizard errors when it tries to iterate through the various
databases on my ISP's SQL Server box, because the first one doesn't belong
to me.
Is this not the way SQL Server 2005 does things? Am I going about this the
wrong way?
Any assistance gratefully received.
Mark"Mark Rae" <mark@.markN-O-S-P-A-M.co.uk> wrote in message
news:uACp2i4NGHA.2816@.TK2MSFTNGP15.phx.gbl...
No-one got any ideas...?