August 12, 2014 at 5:06 pm
We are running SQL Server 2012 on Windows 2008 Server. I created a credential with a proxy account. In creating the credential, it asked for an Indentity and Secret. I used my windows login and password. Now, I have tested the credential and proxy account by executing a Job which calls a SSIS Package. What is the 'best practice' to use when creating a credential? Should the credential be created with another windows login, created with the same abilities as my windows login, with a non-expiring password? Should that new windows login be used as the owner of my job with the Agent?
Thanks in advance, Kevin
August 12, 2014 at 5:13 pm
One other question, could the same account (generic windows login) be used across servers (testdbserver, proddbserver)?
Thanks, Kevin
August 13, 2014 at 7:57 am
I don't know about best practice, hope you get some other replies, I'd like to see them. But I think not good to use your acct--probably too many rights, and potential problems with password expiry and/or employee termination. I think better to create specific acct that only has rights needed for the job(s), and ensure it is considered to be a service acct by your AD admins so pwd won't expire.
August 13, 2014 at 8:01 am
For 2nd question I'm sure best practice is "no", but pretty sure we've done this before. I think it comes down to potential for danger/misuse/tragic mistakes. If you feel this risk is low enough, go ahead and use same acct.
August 14, 2014 at 1:14 pm
Thanks for all the responses.
I hope this helps someone. I got the network group to create a windows service account (non-expiring password). I used this account/password for the Indentity/Secret in creating the credential. In trying to execute the Job (which executes the ssis package), I experienced a few more errors. To resolve everything I had to do the following:
1. Create a trusted windows login for the windows service account.
2. Create a database user in the SSISDB for the windows login.
3. Add the database user to the ssis_admin database role in SSISDB.
Just curious, it seems like I had to pull information from different sources (googling), work through trials/errors to create an SSIS Catalog (which automatically creates the SSISDB), deploy a package, create a windows service account and execute the above 3 steps for the this account, create a credential, create a proxy account and create a job to execute the ssis package. Now, I have to make sure I have back up and restore procedures in place for SSIS Packages, IS Catalog, SSISDB, etc.
Again, I have the job working but still there are other things I am not sure about:
Do I need to assign any permissions (under Project Folder Properties Dialog) within the IS Catalog?
Do I need to assign any permissions (under Project Properties) within the IS Catalog?
Has anyone found one document to explain all the steps in deploying/implementing ssis packages?
Thanks, Kevin
August 14, 2014 at 4:51 pm
Just want to chime in and say thanks for posting, because I'm currently stuck in this same nightmare and it's taking multiple departments here and a ton of googling and trial and error to make any incremental steps. It's INSANE how complicated this is.
August 18, 2014 at 3:50 pm
kevinsql7 (8/14/2014)
Thanks for all the responses.I hope this helps someone. I got the network group to create a windows service account (non-expiring password). I used this account/password for the Indentity/Secret in creating the credential. In trying to execute the Job (which executes the ssis package), I experienced a few more errors. To resolve everything I had to do the following:
1. Create a trusted windows login for the windows service account.
2. Create a database user in the SSISDB for the windows login.
3. Add the database user to the ssis_admin database role in SSISDB.
Just curious, it seems like I had to pull information from different sources (googling), work through trials/errors to create an SSIS Catalog (which automatically creates the SSISDB), deploy a package, create a windows service account and execute the above 3 steps for the this account, create a credential, create a proxy account and create a job to execute the ssis package. Now, I have to make sure I have back up and restore procedures in place for SSIS Packages, IS Catalog, SSISDB, etc.
Again, I have the job working but still there are other things I am not sure about:
Do I need to assign any permissions (under Project Folder Properties Dialog) within the IS Catalog?
Do I need to assign any permissions (under Project Properties) within the IS Catalog?
Has anyone found one document to explain all the steps in deploying/implementing ssis packages?
Thanks, Kevin
Just checking again to see if anyone has any answers to the above questions.
Thanks, kevin
November 3, 2014 at 6:36 pm
The thing to keep in mind is why proxy accounts exist. When you schedule a package, the agent account (by default) on the machine you deployed to runs your IS package. If that account does not have access to, example, a file directory on a remote server then you have an issue. So proxies fill the gap to where your agent account falls short permissions wise.
So keep things simple, use a adminstrator or like account as the identity of the credential. You already made sure the identity's password does not expire so that is a check. When you then create the proxy (defines how the credential is to be used) you select sql server integration services package in the 'active to the following subsytems' section of the new proxy dialogue. So it is only used for that. Further, you can even select which logins may use the proxy in the Principals section for further restriction. You'll want to have the account that runs the agent listed here.
I dont think you need to touch the IS catalog unless there is a restriction there keeping the identity account from executing a package in the store there. Package stores permissions is so other administrators dont peek into your packages if you dont want them to. So if there is one then someone put it there previously.
From here when you define the job step, just in the Run As drop down use the proxy account. Whenever Windows authentication are required by a connection , you'll be good to go.
I hope this makes sense.
----------------------------------------------------
November 3, 2014 at 7:18 pm
In my organization, the whole SQL Agent/proxy account thing never panned out for me. We ended up using Windows Scheduler instead.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply