Post reply

Managing SSIS Packages on Remote database from SSMS on Remote Client.

  • October 16, 2012 at 9:40 am

    #283096

    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?

  • October 16, 2012 at 9:48 am

    #1549653

    I think you need to enable remote SSIS connections via DCOM. see

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

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

  • October 16, 2012 at 9:55 am

    #1549657

    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. :unsure:

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply