Run an SSIS Package Under a Different Account

Question

Post reply

Run an SSIS Package Under a Different Account

Grey Cat

Mr or Mrs. 500

Points: 524
More actions
March 24, 2010 at 9:46 pm

#231125

Comments posted to this topic are about the item Run an SSIS Package Under a Different Account

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Food for thought, I can see many uses for this.

Thanks

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

spellbydevil SSC Journeyman Points: 89 More actions · Answer 2

spellbydevil

SSC Journeyman

Points: 89

March 25, 2010 at 4:19 am

#1139490

It was very useful! Thank you for the article.

Vitor

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 3

"The ETLadmin account, which is a domain account that we use it to run SSIS packages, does not have the right permission to access views in that database even if it has sysadmin permission on that SQL Server instance."

Could the author please explain this a bit further? How can a database user with sysadmin permissions on the instance not be able to read any data it wants? I admit that with only 4 years DBA experience I'm not a SQL Server guru, but I find it hard to believe that this is truely the case.

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 4

Short, sweet and to the point! My favorite type of article.

I would also like an explanation as to how can sysadmin account not be able to read certain database tables?

Grey Cat Mr or Mrs. 500 Points: 524 More actions · Answer 5

It is true that a sysadmin loin has the permission to read all database objects. But many applications use views to control the data access.

For example, user logins are saved in a table, and grouped by permissions or positions. A view displays different employee data according to the current login. If the login is a department supervisor, then the view only return employees from this department. If the login is CEO, then it returns all employees. Or only if the login is HR user, the review returns salary, otherwise salary is null.

This permission control has nothing to do with the database permission.

If you know the database structure, you can run a query against tables instead of views. But unfortunately sometime purchased third party applications are not allowing you to know the inside of the database. And in most cases, the queries can be very complicated.

To make it worse, after an upgrade, tables could be changed.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Useful article on the use of Proxy accounts.

Thanks for the article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grey Cat Mr or Mrs. 500 Points: 524 More actions · Answer 7

Grey Cat

Mr or Mrs. 500

Points: 524

March 25, 2010 at 12:14 pm

#1139919

Thanks for all the comments. It's encouraging.:-)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Grey Cat (3/25/2010)
Thanks for all the comments. It's encouraging.:-)

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Nitya Say Hey Kid Points: 699 More actions · Answer 9

The facet thing works only on SQL 2008.

If you were to run it in SQL2005, you'll have to use runas along with other parameters

sqlusers SSCrazy Points: 2463 More actions · Answer 10

sqlusers

SSCrazy

Points: 2463

March 29, 2010 at 10:12 am

#1141540

Nice Article!!! Thanks for posting...

hzhu Old Hand Points: 305 More actions · Answer 11

I have one question about this section:

Enter the credential name - Enter the domain account, and password. Repeat the password in the 'Confirm password'. This account should have the right access to the application database.

We use windows authentication to connect to the database. So if I put individual account, I can use network password. What if I need to add a group for credential, and the group doesn't have a network password. What should I put in password field?

Grey Cat Mr or Mrs. 500 Points: 524 More actions · Answer 12

I don't think a group account without password will work this way. The originally purpose of doing that is to bypass different accounts that do not having access...

min 30087 Grasshopper Points: 22 More actions · Answer 13

One question for this:

What if the credential's password get changed? I had been using one credential and created a proxy to run the SSIS package in SQL Agent jobs, but the job failed after the password for the account changed.

any comments will be appreciated.

cheers,

Min

Grey Cat Mr or Mrs. 500 Points: 524 More actions · Answer 14

Please check the ProtectionLevel of the SSIS package.

If it's not DontSaveSensitive, change it to.

Hope that helps.