Question Regarding Job Schedules

Hi

My instance of SQL Server 2005 is installed on a Win Server 2003 box. I am trying to schedule an SSIS job. I have created a proxy using my NTLogin as Credential. When I run the job, it returns with the following error:

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'XXXXXXXX', error code 0x54b. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
where XXXXXXXX is my Domain\NTLogin

I access the Server box and discovered that the Windows Task Scheduling Service is not running. Does SQL Server Scheduling depend on this?

I'm quite sure that SQL Server Agent has nothing to do with Windows Task Scheduler. However I have no idea about your error...|||

Hi

Thanks for the reply. Here is what I am doing to test this:

I have a Test SSIS package that has a singe Execute SQL Task which connects to a SQL 2000 db and makes an insert into a table. This works fine when I run it through the VS Studio IDE.

After this, I registered the Package with the Integration Services and when I execute the package by right clicking and selecting the Execute Package option it executes again.

I restarted the SQL Server Agent to run under my NT account. Now, the first piece of the puzzle is that when I try to create a new credential with my NT Account, it throws an error saying that :
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during encryption. (Microsoft SQL Server, Error: 15467)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=09.00.1116&EvtSrc=MSSQLServer&EvtID=15467&LinkId=20476
I ran a Create Credential Script to insert the credential and created a proxy using that. Then I created a job with a new SSIS step that runs under the proxy. Now when I try to run the job it fails to execute with the following two entires in the log:

Message
[298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000] (ConnIsLoginSysAdmin)

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'CSFB\jgattani', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Can I provide you with any more information that might assist you in trying to figure out whats going on?

Regards
Jay

|||Hi

I had posted my question a couple of days back and did not get a response. Is the question vague or does it need more information. Please let me know if I could provide more details in understanding the problem I am facing.

We are stuck at this point and would like to get this resolved and use the SQL Server 2005 instead of good old SQL Server 2000.

Thanks
|||Hi,

just an idea... Changing the service login wasn't supported in older builds of SQL Server 2005. Perhaps there is still a problem with that... I would not change the service's credentials...

Encryption problem can also occur when you store "save" data (like database logins) within the package and encrypt the package with the user key. You should use a password for the package instead and use it when you run the package...

Again: just an idea...|||Hi

Thanks for the response. I think that the root cause of the issue is my inability to create the credential. If I have the SQL Server Agent running under my NT Acount and then the job Steps running under the "SQL Agent Service Account" and my nt account as the owner of the job, everything works perfect.

sql

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
Paul G
Software engineer.
Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
Paul G
Software engineer.
"Chris Wood" wrote:

> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL process
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to SQL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>
>
|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...[vbcol=seagreen]
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
--
Paul G
Software engineer.Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
--
Paul G
Software engineer.
"Chris Wood" wrote:

> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL proces
s
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to S
QL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>
>|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...[vbcol=seagreen]
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:
>

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
--
Paul G
Software engineer.Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
--
Paul G
Software engineer.
"Chris Wood" wrote:
> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL process
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to SQL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
> >I am a recent user of sql2005 but have worked with sql2000 for a few years.
> > I noticed under the sql server agent there is a listing for proxy. I have
> > used the sql server agent to set up jobs to run packages developed with
> > Integration services. I was wondering if anyone had a simple explination
> > of
> > proxys and how/why to set them up.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:
>> Paul,
>> My take on the proxy is if you need to run something from inside SQL that
>> needs to go outside of SQL you need the proxy account. So if an SQL
>> process
>> needs to run xp_cmdshell then you need the proxy account. We have cases
>> where we start inside SQL and go outside to another server that runs a
>> process inside another SQL server. Here the proxy is needed to go outside
>> and needs to be defined on the other server and possibly given access to
>> SQL
>> resources inside the other SQL Server otherwise it will not work.
>> Clear as mud!
>> To use xm\p_cmdshell you need to make it available SAC and give
>> permission
>> to run it and then either create the proxy Under
>> Server/Properties/Security/Server proxy Account or by using the system sp
>> sp_xp_cmdshell_proxy_account. They both do the same thing.
>> HTH
>> Chris
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>> >I am a recent user of sql2005 but have worked with sql2000 for a few
>> >years.
>> > I noticed under the sql server agent there is a listing for proxy. I
>> > have
>> > used the sql server agent to set up jobs to run packages developed with
>> > Integration services. I was wondering if anyone had a simple
>> > explination
>> > of
>> > proxys and how/why to set them up.
>> > Thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>