Question about OLE DB conn mgrs

Hi,

I just realized I have this question. Let's say you are building a simple package that just loads some data from a flat file into a database table.

And let's say that many people will be logging on to the server to run this package from the file system, (so each person will have their own Window's login.) Some people will have rights to read/write to tables database, but some will not. They just need to be able to run the package.

So, when you set up the OLE DB Destination conn mgr, should you choose Windows Authentication or SQL Server login?

Because what if someone does not have permission to write to the database? Won't the package fail to execute since it's relying on the Window user's login to for the OLE DB conn mgr?

Thanks

Why not schedule the job via SQL Server Agent, and then give the users permissions to execute the Agent job? Then you can use whichever login method you like.|||

My question is more hypothetical, as I am just trying to understand how Windows login works vs. SQL Server login in a package.

But from your answer it seems that my assumption is correct, in that the OLE DB conn mgr does rely on the the window user's login to run the package? And if the permission isn't there, no dice.

|||

sadie519590 wrote:

My question is more hypothetical, as I am just trying to understand how Windows login works vs. SQL Server login in a package.

But from your answer it seems that my assumption is correct, in that the OLE DB conn mgr does rely on the the window user's login to run the package? And if the permission isn't there, no dice.

Correct. That's what Windows Authentication is. It passes your logged-in user credentials on to SQL Server. So yes, if the logged-in user account doesn't have that same account setup in SQL Server, no dice.

SQL Server authentication is specifically set inside SQL Server. A user account, local to SQL Server is created. You can setup the package to use SQL Server authentication, and as long as you have the protection level set to EncryptSensitiveWithPassword or similar (don't use UserKey), you should be okay using that.|||

Ok, thanks. I just wanted to make sure my thinking was correct.

But back to your original answer, how would one schedule a file system package in a job - using DTExec? Would you put the password in a config file?

Also, how do you give permission to run an agent job?

Thanks

|||

sadie519590 wrote:

Ok, thanks. I just wanted to make sure my thinking was correct.

But back to your original answer, how would one schedule a file system package in a job - using DTExec? Would you put the password in a config file?

Also, how do you give permission to run an agent job?

Thanks

When you create an Agent Job, you can specify the location of the package. If you use EncryptSensitiveWithPassword, you can alter the command line that Agent uses to issue the /DECRYPT flag. You don't necessarily have to worry about *how* to run the package.

Try it. Just try creating an Agent job and walk yourself through it.

Giving permission to an Agent job is the role of the SQL Server DBA. Are you this person?|||

Thanks. I'm sure it's very straightforward.

I have full permissions in the db, just not sure how to assign someone the permission to run a job. Is it done a job level? I've never set this permission as I only work with scheduled jobs.

|||

sadie519590 wrote:

Thanks. I'm sure it's very straightforward.

I have full permissions in the db, just not sure how to assign someone the permission to run a job. Is it done a job level? I've never set this permission as I only work with scheduled jobs.

You'll need to read on Agent jobs and security. There are three roles that you can use out of the box to specify security permissions for a given user.sql