Running SSIS package through SQL Agent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Running SSIS package through SQL Agent

Author

Topic

ares
Starting Member

6 Posts

Posted - 2008-06-18 : 13:11:10

Hello,

I'm new here and hope you will be able to help me.

I have created several SSIS packages with Visual Studio 2005.
They all work fine in debug mode.
I have been able to make them work with a ODBC connection by using a ADO.NET connection.

Then I exported them to the file system in my SQL Server 2005 database and created a task in SQLAgent to run them.

All the packages using the ODBC connection fail with the following error :

Login failed for user XXX
Error : 18456; Severity : 14 , State : 8

This error is a password mismatch.

I tried several database users and checked the passwords multiple times.

It looks like SQL Agent is not able to retrieve the password although it is stocked in both the ODBC connection and the SSIS connection.

Can you help me?

Thanks,

Ares

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-18 : 15:16:40

Did you set protection level while you deployed the package to filesystem? Its better to deploy to MSDB since you are scheduling job? Also check whether SQL agent account has permission to access folders inside your SSIS package?

acbpriya
Starting Member

4 Posts

Posted - 2008-06-18 : 15:43:16

i have a similar kind of issue
I need help on this SSIS issue.
I have a SSIS package that imports data to Server A from a table from Server B
Server A is my Local server(DEMARIUS) and Server B is Development Server

on Server A I created a job to run this SSIS.
and I get the error

"Executed as user: DEMARIUS\SYSTEM. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:27:01 PM Error: 2008-06-18 12:27:02.14 Code: 0xC0202009 Source: escaladetolocal Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". End Error Error: 2008-06-18 12:27:02.14

On Server A where the job runs , SQL Server agent uses LOCAL System account. SSIS service uses NT AUTHORITY account

I created the package with "Do Not save.." option.

Please help me to fix this ASAP.

Please email me with your Help at
psubhadevi@acbcoop.com

Thanks in advance
priya

acbpriya
Starting Member

4 Posts

Posted - 2008-06-18 : 15:55:23

sodeep,
how do i check if SQL agent account has permission to access folders inside your SSIS package ??

thanks,
priya

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-18 : 16:09:05

Does your package run in BIDS? SQL Server agent runs on domain account. Check this out for protection level:
http://support.microsoft.com/kb/918760

acbpriya
Starting Member

4 Posts

Posted - 2008-06-18 : 16:39:52

yes . the package runs from BIDS

acbpriya
Starting Member

4 Posts

Posted - 2008-06-18 : 16:48:10

do you think if I need to change or add any accounts for running th SSIS as a job?
SQL Server runs with one account a
SQL Server agent runs with NT Authority account
The package is created with my domain account
any idea?. I added NT Authority as a user to SQL Server B. do you think this user needs to be in the admin group of that Server B?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-18 : 22:20:43

You can set proxy account for that, get details in books online.

ares
Starting Member

6 Posts

Posted - 2008-06-19 : 03:40:59

It is really not a authorization issue.
The package is launched by SQL Agent.
But it fails because when trying to connect to the source database the password doesn't match.
The error 18456 is not in the SQL AGent log but in the SQL Server log.

EDIT:
Oddly when I run the stored package directly in Management Studio, it works fine.
It is just SQLAgent that can't retrieve this password
The source database is QUAL1, the target database is DWH.
They are on the same server as the package.
The connection to DWH is made via OLEDB connectors and works fine.
The connection to QUAL1 is made via ODBC (long story...).

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-06-19 : 07:12:27

Am getting the same problem. So i did some testing on it.

I built a SSIS package only using a connection to my SQL2005 DB and i schedule the job to run under the NT Authority\Network Service login which has.

Server roles: “sysadmin”
User mapping: Msdb SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole

The job secheduled and work fine.

Then i changed my SSIS package and made a DB connection to a different server this time its a 2000 server DB and i left my job settings the same after deploying the job and the job failed.

I failing on the connection to the sql2005 server which is strange.

ares
Starting Member

6 Posts

Posted - 2008-06-19 : 08:20:12

Hi Rookie,

This may help you.
I'm not yet sure it will work for my problem but it looks taylor made for yours.

http://support.microsoft.com/kb/925744/en-us/

good luck.

ares
Starting Member

6 Posts

Posted - 2008-06-20 : 06:27:07

I have been able to work my way around this issue.

It took two modifications:

1°) Set ProtectionLevel of the package to
EncryptSensitiveWithPassword

2°) In SQL Agent, run the packages stored in the OS File system rather than in the SQL Server file system.

I have no idea why it works but it does.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-06-20 : 11:50:26

Hey where do you Set the ProtectionLevel of the package to
EncryptSensitiveWithPassword.

Can you do this in the SSIS package ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 15:16:54

http://msdn.microsoft.com/en-us/library/ms141747.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-20 : 16:28:35

quote:
Originally posted by rookie_sql

Hey where do you Set the ProtectionLevel of the package to
EncryptSensitiveWithPassword.

Can you do this in the SSIS package ?

It is done while you are saving your package .

loudbliss
Starting Member

37 Posts

Posted - 2008-06-23 : 17:19:46

Ares,

Did you set the protectionlevel in SQL when importing the Package?

I tried doing what you did and still cant get the package to run from sql agent

loudbliss
Starting Member

37 Posts

Posted - 2008-06-24 : 11:10:08

quote:
Originally posted by rookie_sql

Hey where do you Set the ProtectionLevel of the package to
EncryptSensitiveWithPassword.

Can you do this in the SSIS package ?

You Open the your package in desing mode, be sure to have the property tab available (F4) then click anywhere in the workspace so the project is selected and you will see ProtectionLevel property in the Property Tab

loudbliss
Starting Member

37 Posts

Posted - 2008-06-24 : 12:10:58

Guys,

Im having the same problem as ares but i havent been able to fix this.

I'm connecting to Sap with MySap .net Data Provider.
When i open the ConnManager in the package and i put the values for the connManager (username, password,server, etc) and test the connection it works fine. After that i go ahead and debug the Package it Works Fine.
Now, if i go ahead and open the ConnManager again i see there's no password there (as other have mentioned).
I tried with ares solution (EncryptSensitiveWithPassword + run from OS File System)
but it fails when running.

Has anybody got another solution?

loudbliss
Starting Member

37 Posts

Posted - 2008-06-25 : 11:33:04

Ok Guys, so im back with the solution.

This problem was posted at
http://www.sqlservercentral.com/Forums/Topic522743-148-1.aspx?Update=1
and from there an user called crever made a video with the solution.
The video can be found at his webpage: http://MidnightDBA.ITBookworm.com
or directly at: http://midnightdba.itbookworm.com/SSISConfigFileConnString/SSISConfigFileConnString.html

ares
Starting Member

6 Posts

Posted - 2008-06-27 : 09:28:51

Hi ,

I was away a few days and didn't realize the thread was living on.

To set the ProtectionLevel for the package you have to open it with Visual Studio, make sure you are in Control panel and have no object selected to be able to display the package properties. Then set the ProtectionLevel from the drop down list.

When using ProtectWithPassword, use the popup window when setting the package password because it will ask for password confirmation.

I now have a little more information on the mechanics of the problem.
It seems to be an encryption problem.
The connection data is managed as sensitive information by SQL Server. Hence it is encrypted when passing from SSIS to SQL Agent.
When using EncryptWithPassword, you can control the way it is encrypted/decrypted.
With other options you hand over that control to the Microsoft rights management system and it is generally bad news. There is probably another solution that is more orthodox but I don't know enough about the way SQL Server manages every user, application database rights.

I hope this helps.

Ares

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources