none
cannot connect SSIS in a remote server

    Question

  • Server OS: Windows Server 2008 R2
    SQL Server Version: SQL Server 2008 SP1
    Client OS: Windows XP SP3

    I tried to use SSMS 2008 on a client to connect SSIS installed on a remote server but got the following error message. I can connect to SSIS on the server locally. Both machines are in a same domain and my domain account is in the Administrators group on the server. I set the windows firewall so anyone can access MsDtsSrvr.exe. I also found the security for MsDtsServer100 under DCOM Config are set correctly.

    Can anyone have idea about what causes the problem?


    TITLE: Connect to Server
    ------------------------------

    Cannot connect to DEVSQL.

    ------------------------------
    ADDITIONAL INFORMATION:

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

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

    A security package specific error occurred. (Exception from HRESULT: 0x80070721) (Microsoft.SqlServer.DTSRuntimeWrap)

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

    A security package specific error occurred. (Exception from HRESULT: 0x80070721) (Microsoft.SqlServer.DTSRuntimeWrap)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    Thanks,
    Gang
    Friday, April 23, 2010 2:31 AM

Answers

  • Our domain admin hasn't got chance to reset the SPN. However, I figured out what caused the problem. We created a domain user account and SSIS service runs under this account. When I changed it to built-in Network Service account, the problem is gone. Apparently our domain user account lacks of some kind of privileges and resetting SPN may resolve this as well.
    • Marked as answer by Gang.H Tuesday, April 27, 2010 7:17 PM
    Tuesday, April 27, 2010 7:16 PM

All replies

  • Hi Gang,

    Can you please check if the remote server that you are trying to connect has SQL server 2008 version of SSIS on it. If that has SQL server 2005 SSIS, then you may get similar error when you try opening it through SSMS 2008 on other server.

    -Amey

    Friday, April 23, 2010 4:58 AM
  • Hi Amey,

     

    Yes, it has SSIS 2008 installed and running and I can connect it locally.

     

    Thanks,

    Gang

    Friday, April 23, 2010 5:01 AM
  • Below are detailed information about the error message. Actually I got "RPC server is unavailable" error at the beginning. I soon realized it's caused by windows firewall. After I configured windows firewall, I received this error.

    ===================================
     
    Cannot connect to DEVSQL.
     
    ===================================
     
    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
     
    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
     
    ------------------------------
    Program Location:
     
      at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
      at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
      at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
     
    ===================================
     
    A security package specific error occurred. (Exception from HRESULT: 0x80070721) (Microsoft.SqlServer.DTSRuntimeWrap)
     
    ------------------------------
    Program Location:
     
      at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
      at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
      at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
      at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
      at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
      at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
     
    ===================================
     
    A security package specific error occurred. (Exception from HRESULT: 0x80070721) (Microsoft.SqlServer.DTSRuntimeWrap)
     
    ------------------------------
    Program Location:
     
      at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)
      at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
     

    Friday, April 23, 2010 5:29 AM
  • It looks the dcom is not configured correctly. Could you double check your DCOM permission setting?

    Does the credential that launch SSIS services have the permission to make the romote COM call?

    • Proposed as answer by Eric WisdahlModerator Friday, April 23, 2010 2:10 PM
    • Unproposed as answer by Gang.H Friday, April 23, 2010 2:56 PM
    Friday, April 23, 2010 1:07 PM
  • I'm in Administrators group in the server. Administrators has the following permissions on MsDtsServer100:

    • Local Launch, Remote Launch, Local Activation, Remote Activation 
    • Local Access, Remote Access
    How do I check if my credential makes the remote COM call?

    Thanks,
    Gang 

    Friday, April 23, 2010 1:27 PM
  • Not sure if this will help or not, but you might take a look at Remote SSIS vs Domain\User: Access is Denied

     


    Please mark answered posts. Thanks for your time.
    Friday, April 23, 2010 2:15 PM
    Moderator
  • I actually tried to add my account to Distributed COM Users group in the server but unfortunately I still received the same message.

    Microsoft SQL Server Management Studio			10.0.2531.0
    Microsoft Analysis Services Client Tools		10.0.1600.22
    Microsoft Data Access Components (MDAC)			3.85.1132
    Microsoft MSXML						2.6 3.0 4.0 5.0 6.0 
    Microsoft Internet Explorer				8.0.6001.18702
    Microsoft .NET Framework				2.0.50727.3607
    Operating System					5.1.2600
    
    Microsoft SQL Server Management Studio			10.0.2531.0
    Microsoft Analysis Services Client Tools		10.0.1600.22
    Microsoft Data Access Components (MDAC)			6.1.7600.16385
    Microsoft MSXML						3.0 6.0 
    Microsoft Internet Explorer				8.0.7600.16385
    Microsoft .NET Framework				2.0.50727.4927
    Operating System					6.1.7600
    I checked the versions of components on both client and server and found some of them are different. Is this correlative with the problem?

     

    Thanks,

    Gang

    Friday, April 23, 2010 2:36 PM
  • Tuesday, April 27, 2010 9:14 AM
  • I saw similar Kerberos error messages after I ran Network Monitor. I think the post could resolve my problem but I haven't verified yet because I need our domain admin to set up the SPN. I'll report later...

    Tuesday, April 27, 2010 2:31 PM
  • Our domain admin hasn't got chance to reset the SPN. However, I figured out what caused the problem. We created a domain user account and SSIS service runs under this account. When I changed it to built-in Network Service account, the problem is gone. Apparently our domain user account lacks of some kind of privileges and resetting SPN may resolve this as well.
    • Marked as answer by Gang.H Tuesday, April 27, 2010 7:17 PM
    Tuesday, April 27, 2010 7:16 PM
  • The resolution above brings a new question: what minimal privileges we should grant if we want to use a domain user account instead of the built-in Network Service account?

    Thanks,

    Gang

    Tuesday, April 27, 2010 7:21 PM
  • Thanks for posting the solution, Gang. I was getting the same error when remotely accessing SSIS. And this solution resolved the problem.

    Wednesday, August 10, 2011 7:08 PM
  • Are you facing the same issue when using a Windows 7 machine?

    If not, please try installing the following hotfix : http://support.microsoft.com/kb/969442 

    • Proposed as answer by Samudra Gogoi Thursday, August 25, 2011 11:10 PM
    Thursday, August 25, 2011 11:10 PM