July 29, 2009 at 3:01 pm
I will preface this by saying I have little knowledge of ssis .. we were recently tasked with bringing a sql2000 database with numerous dts packages and moving the DB's to a sql 2005 cluster. We are aware that microsoft does not recommend ssis on a cluster, and we have one more DB coming online shortly that also requires SSIS. What we want to do is install SSIS on a non-cluster Server, and be able to access the packages needed through there. What we have found out is there is a 2 hop authentication issue. and it looks like it is trying to use the NT SYSTEM/Anonymous connection to connect to the databases. Is there a way of setting this up without opening the anonymous account to be able to read and write DB's using these ssis packages?
July 29, 2009 at 6:06 pm
I believe you need to set up "delegation" in Active Directory. That's usually the issue when you see the anonymous login message and there is a double-hop in process.
July 29, 2009 at 6:07 pm
Hi
Firstly SSIS is a 32bit application, if you are setting up 64-bit sql server, I would highly recommend that you install the SSIS on a separate server.
SSIS doesn't have to sit on the same server where your main database lives. All you need to ensure is that you have enough permission for the SSIS to do the job and you've setup the Datasoure to point to the proper database location.
IT
July 30, 2009 at 6:33 am
Sometimes it's hard to convince AD admins to allow delegation. Are you scheduling jobs on that cluster? You could set up jobs on the same server as your ssis package so that you will only have 1 hop - ssis/agent server to cluster.
July 30, 2009 at 6:55 am
IT,
What kind of permissions does SSIS need? Do you run SSIS as a domain account? Also, what permissions are needed on the database side?
thanks
Irwan Tjanterik (7/29/2009)
HiFirstly SSIS is a 32bit application, if you are setting up 64-bit sql server, I would highly recommend that you install the SSIS on a separate server.
SSIS doesn't have to sit on the same server where your main database lives. All you need to ensure is that you have enough permission for the SSIS to do the job and you've setup the Datasoure to point to the proper database location.
IT
July 30, 2009 at 5:15 pm
Wade
I am using AD domain account to run my SSIS package, it uses the SQL Server Agent Security.
Just make sure that the SQL Server Agent AD Domain Account has access to the databases that you are making connection to.
You can test this by running your BIDS with "Run as..." the AD Domain account that you r using to test the connection.
IT
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply