Questions about permissions in jobs, etc

[katbear]

Hi,

I have some questions about SSIS package and SQL Server job permissions, and it's driving me kinda nuts

Here are my questions:

1.) If you rely on SQL Server storage for roles and access control for your package - what does mean? I don't get it.

2.) Regarding the job owner (in my case, sa) - how does this differ from the job STEP owner (which in my case is the agent account)?

3.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account??

Thanks much!

 

[SQLBill]

1) Microsoft, in their infinite wisdom, has decided that passwords and connection strings used in SSIS packages must be encrypted. The 'default' encryption uses a key based on your login and the machine name (I believe). The problem is that if you move the package to another machine, the encryption key is not the same and the package can't be run. The issue is the same if someone else tries to run the package. Your options are to move the passwords/connection strings to a config file and call them from there, encrypt the password/connection string with a password that anyone running it will know. OR (drum beat) save the package as a SQL Server store and allow the server to handle the security. This means that SQL Server's security protects the passwords and connection strings. It also means that it accepts that you will only give access to the appropriate logins.

-SQLBill

Posting advice: FAQ481-4875

 

[katbear]

Thanks

I am also really wondering about #3:

3.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account??

 

[SQLBill]

Depends. From what I can tell, it uses the Windows Authentication for whomever/whatever runs the job. So, if it is run as scheduled, it runs as the service account. If you run it manually, it runs as you. But I could be wrong.

-SQLBill

Posting advice: FAQ481-4875

 

[katbear]

I found the answer, I believe.

Because the SQL Agent account owns the job step that runs the SSIS package, the server login will be the SQL Agent account.

In addition, if the connections in the package itself use windows authentication, they too will also use the SQL Agent account login for their connections.