Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Managing SSIS Packages on Remote database from SSMS on Remote Client. Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 9:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:46 AM
Points: 47, Visits: 264
I'm in the process of setting up a new SQL environment.

Server SQL1 is hosting SQL Server.
Server SSIS1 is running Integration Services.
Client 1 is running SSMS.

SQL Server and Integration Services are both running as the same active directory user: DBServiceAcct.

DBServiceAcct is a local admin on both servers, and is explicitly defined as a sysadmin on SQL1.

I have modified MsDtsSrvr.ini.xml on the SSIS1 to point to SQL1.

I have configured SQL1 to automatically create SPN's on startup in order to connect with kerberos authentication and I have verified it is working properly.

If I RDP with my own AD Credentials to SSIS1, launch SSMS "as administrator", and connect to Integration services on SSIS1 I can expand out the MSDB folder. A SQL Trace verifies that I am connecting to SQL1 as myself.

If I launch SSMS on my local PC (Client 1) logged in as myself, and connect to SSIS1 I get a failure when I expand the MDSB folder.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server Native Client 11.0)




Any thoughts?
Post #1373364
Posted Tuesday, October 16, 2012 9:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
I think you need to enable remote SSIS connections via DCOM. see

http://msdn.microsoft.com/en-us/library/aa337083(v=sql.100).aspx


---------------------------------------------------------------------

Post #1373367
Posted Tuesday, October 16, 2012 9:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:46 AM
Points: 47, Visits: 264
Hmm, from the bottom of that article I found this paragraph.

SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.

So it sounds like I can't do what I'm trying to do. The only way to manage packages in this configuration is to RDP to the SSIS server.

Bummer.
Post #1373371
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse