SQL OLEDB Connection with provided windows service account credentials
SQL Server 2008
SSBIDS 2008
SSIS 2008
I need to provide a specific windows service acccount to make a connection to a SQL OLEDB source. The source SQL Server is 2008 and set at Integrated Security. The service account has all the proper read credentials as I can "run as" SSMS and see the table we need to pull.
Package protection level is set to "Encrypt Sensitive with Password"
The problem is that when the package runs as the SQL Proxy, the agent is attempting to make the connection using the Proxy credentials, not the saved service account credentials.
We cannot have a service account with credentials to multiple datasources. This seems like it should be so easy, yet I'm having a bugger of a time trying to source data from a SQL Server.
SSBIDS 2008
SSIS 2008
I need to provide a specific windows service acccount to make a connection to a SQL OLEDB source. The source SQL Server is 2008 and set at Integrated Security. The service account has all the proper read credentials as I can "run as" SSMS and see the table we need to pull.
Package protection level is set to "Encrypt Sensitive with Password"
The problem is that when the package runs as the SQL Proxy, the agent is attempting to make the connection using the Proxy credentials, not the saved service account credentials.
We cannot have a service account with credentials to multiple datasources. This seems like it should be so easy, yet I'm having a bugger of a time trying to source data from a SQL Server.
ASKER
Thank you, but that isn't even remotely related to my issue.
The proxy is established on the target server and runs all the jobs. The issue is I need to create a connection in the SSIS package that uses as specific Windows account to connect to a SQL server source.
The proxy is established on the target server and runs all the jobs. The issue is I need to create a connection in the SSIS package that uses as specific Windows account to connect to a SQL server source.
If you've set up an agent job proxy account, it will be used to run all your jobs - if you want more control, you should be setting up multiple proxy accounts and assigning your package to run as one of the other accounts. If you're saying that you want most of your package to run as one account and only this single data source to connect using a different account, I don't believe that's possible.
In the case of what you're doing, you have two choices:
- Set your connection to use SQL authentication and then provide the SQL login details manually in your package
- Create a separate package that runs via your service account and stages the required data locally, and then connect to that data from your second package.
Your package will run as a single account, and all the connections (that user integrated authentication) will authenticate as that single user.
In the case of what you're doing, you have two choices:
- Set your connection to use SQL authentication and then provide the SQL login details manually in your package
- Create a separate package that runs via your service account and stages the required data locally, and then connect to that data from your second package.
Your package will run as a single account, and all the connections (that user integrated authentication) will authenticate as that single user.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Same or different domains?
I think you're correct - the requirements you're giving don't allow for a solution that meets them all. I wish you luck finding a solution that works, but I think you're out of luck with the problem as you currently face it.
The only way around it would be fetching the data in one package and staging it locally somewhere, and then reading it from a second package (or setting up some kind of recurring data export), but that would require the account you use to fetch the data (the remote server windows account) having rights to persist the data on your local server, and that sounds like it's not allowed.
Hopefully you can point out to somebody the lunacy of not letting the same account have permissions in more than one place, as it completely prevents any cross-server-data-sourcing
The only way around it would be fetching the data in one package and staging it locally somewhere, and then reading it from a second package (or setting up some kind of recurring data export), but that would require the account you use to fetch the data (the remote server windows account) having rights to persist the data on your local server, and that sounds like it's not allowed.
Hopefully you can point out to somebody the lunacy of not letting the same account have permissions in more than one place, as it completely prevents any cross-server-data-sourcing
You can run packages using the DTS command, and from windows scheduler.
Then the runas with the switch netonly might be what youre looking for.
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/a4243cb5-3b86-4a75-b745-e4c6665faed8/
look at posting 'Tuesday, March 23, 2010 12:13 PM'
You should be able to use cartificates as authentication between different domains. Im including a link that shows how to set up mirrors. Just look at the security part and this might be sufficient.
Regards Marten
Then the runas with the switch netonly might be what youre looking for.
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/a4243cb5-3b86-4a75-b745-e4c6665faed8/
look at posting 'Tuesday, March 23, 2010 12:13 PM'
You should be able to use cartificates as authentication between different domains. Im including a link that shows how to set up mirrors. Just look at the security part and this might be sufficient.
Regards Marten
My comment above is NOT a out of the box ready solution for your problem.
Its meerely an attempt of 'thinking outside of the box', and hopefully it useful for you and might inspire into some acceptable way of solution.
Thought of Another, You could (given it's a Windows 2008r2 or better domain) let the bank put a RODC (Read Only Domain Controller) and let this one verify your Connection.
Just Another outside of the box thought.
Regards Marten
Its meerely an attempt of 'thinking outside of the box', and hopefully it useful for you and might inspire into some acceptable way of solution.
Thought of Another, You could (given it's a Windows 2008r2 or better domain) let the bank put a RODC (Read Only Domain Controller) and let this one verify your Connection.
Just Another outside of the box thought.
Regards Marten
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for STHOMP02169's comment #a39171114
for the following reason:
Came up with my own solution.
Accepted answer: 0 points for STHOMP02169's comment #a39171114
for the following reason:
Came up with my own solution.
Why not share your solution?
Regards Marten
Regards Marten
If you came up with your own solution and are asking to close your question, please post the solution as part of your answer. If you don't want to post your solution, you should instead request that your question be deleted, because it won't provide value to future visitors without an attached solution.
ASKER
Once upon a time, Experts Exchange was a valuable blogspot where developers were able to get and share "real-life" solutions to issues they encountered. Now it has become a haven for do-nothings who just do key word searches on MSDN and post unrelated links to KB articles.
The source sql server now runs a job that extracts the data to an ftp site and the target server picks it up.
Please delete my question.
The source sql server now runs a job that extracts the data to an ftp site and the target server picks it up.
Please delete my question.
check out this link.
http://www.mssqltips.com/s
it might helps