December 21, 2007 at 3:23 am
I have installed a SQL2005 SP2 DB instance.
Would it make sense to install SSIS on another server and manipulate the MsDtsSrvr.ini.xml so it points to this DB istance ?
The goal is to separate DB from Appl.
I'm able to connect to the SSIS (installed on a second server), but cannot browse the msdb.
SSMS prompts an error "unable to connect Nt Authority\Anonymous" allthough the SSIS service uses an authorized service account.
I am connected using Windows auth and I am member of the sqlserver sysadmin group.
Still testing, but maybe it's considered a NONO.
In that case, we'll move to a second db-server instance (std-edtn) to host all the packages and to be used as an application server, just to run these packages and maybe even some other propriatary applications and hosing no userdb, except for maybe an SSIS-logging db.
Any hints :discuss:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 21, 2007 at 6:58 am
... SSIS service uses an authorized service account
What does that mean?
Are all services on both machines running under domain accounts?
Norman
DTS Package Search
December 21, 2007 at 7:32 am
Indeed, domain service accounts are in use.
The SSIS service account has been granted privileges on MSDB.
My windows account has sysadmin auth at the db-server (sqlserver).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 21, 2007 at 11:35 am
Is your only goal to off load the overhead of SSIS from your production SQL Server (where your applications live)? If so you could simplify your problem by running database engine on the separate server your wanting to host SSIS service on and just letting it use that instance. Your already eating the cost of a SQL license for it, and if the engine will only be supporting SSIS you're aren't looking at much additional overhead. You start getting into black box land when you try to break the two apart. Unfortunately, MS hasn't given us a configuration tool like they did with reporting services allowing to separate the service from it's DBs.
Hope this helps...Happy Holidays,
Eric
December 21, 2007 at 12:02 pm
You need to enable delegation for the host running the SSIS service for kerberos authentication to work correctly. Also, verify SPN(s), etc. For more information, refer to the following MS KB:
http://support.microsoft.com/kb/319723
Tommy
Follow @sqlscribeDecember 24, 2007 at 12:49 am
Thanks for yoru replies.
- Indeed the goal is to offload the applications from the db server.
- the spn's should be ok for now as we've set it up using a domainadmin account (auto spn registration (according to my domain admin)) to overcome this issue.
We're only performing a proof of concept at this time !
The actual SSIS will have a proppre service account with minimal authorities.
Happy Holidays !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2008 at 2:18 pm
FYI
Actualy with SQL2005 this would not make sence at all !
Reason ?
The ssis service only fulfills the role of "gateway" to if you want to manage the packages via Management Studio or if you want to use the packages through the SSIS-instance.
To use SSISpackages, you only need DTEXEC or DTEXEUI.
You can store the packages as well in the filesystem, or at a server's msdb.
Indeed that has some consequences regarding who would need the rights to the package store filesystem, or to the msdb db.
So just running the packages at another server with a full sqlclient install , would offload it from the dbserver.
DTExec and DTExecUI come with the SQLClient install.
That's the most important thing I learned in SSIS class today :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply