question

JamesSQL-7436 avatar image
0 Votes"
JamesSQL-7436 asked JohnsonThomas-5434 commented

SSMS 16.5.3 unable to connect to Integration Services 13.0 on another server

I recently had to reimage my machine. I've installed SSMS 16.5.3. I cannot connect to Integration Services on another machine that is also running SQL Server 2016 and Integration Services 13.0.

So far I have:

  1. Verified Integration Services 13.0 are running on the server I'm trying to connect to.

  2. Verified host is running SQL Server 2016.

  3. Re-installed SSMS 16.5.3 and SSDT for VS2015

  4. I realize going forward it's preferred to use the SSISDB in Integration Services Catalogs, however these are legacy apps and we have no plans to move them. I need to connect this way.

  5. I can connect to Integration Services from another VM. Just not on my local machine any longer.

  6. UPDATE - I disabled the local firewall and still get the same error.

I used to connect to the server through Integration Services before the re-image. If I remote into another machine and connect, it works fine. What else can I try to connect to Integration Services? Could it be something with the version of Windows from the re-image? Am I forgetting to install something else?


31704-errormessage.png

31705-about.png


31706-version.png


sql-server-generalsql-server-integration-services
errormessage.png (27.5 KiB)
about.png (26.0 KiB)
version.png (9.8 KiB)
· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JamesSQL-7436,

Please update your post with the exact error message.

0 Votes 0 ·
JamesSQL-7436 avatar image JamesSQL-7436 YitzhakKhabinsky-0887 ·

@YitzhakKhabinsky-0887 I have updated my post with the error screenshot.

0 Votes 0 ·

Please share two more things:

  • SSMS, Help => About (screen shots)

  • SQL Server version on the server with SSIS: SELECT @@VERSION;

Microsoft is really pushing for SSISDB Catalog: 36926791-ssis-13-0-4474-does-not-work-in-ssms-after-v17-2


0 Votes 0 ·
Show more comments
JamesSQL-7436 avatar image
0 Votes"
JamesSQL-7436 answered JohnsonThomas-5434 commented

Here is the resolution I received from my Microsoft ticket.

Cause:

The latest updates on Windows 10 is causing the RPC to NOT ignore the LocalService value. So it is looking locally for the Integrations services rather than contacting the remote machine.


Resolution:

Search the System Registry Computer\HKEY_CLASSES_ROOT\ hive [make sure you check under the 32bit system hive ( e.g. Computer\HKEY_CLASSES_ROOT\WOW6432Node)] as well, for this respective search key MsDtsServer130. Changes made to one registry location may update the values under the other in most cases. Usually MsDtsServer130 would be available under 2 locations e.g. (Computer\HKEY_CLASSES_ROOT\AppI & Computer\HKEY_CLASSES_ROOT\WOW6432Node\AppID ).
Note: MsDtsServer120 for SSIS 2014, MsDtsServer130 for SSIS 2016, MsDtsServer140 for SSIS 2017, MsDtsServer150 for SSIS 2019.

Spot the LocalService entry in the registry for this identified hive and rename the registry entry from LocalService to LocalService_A.
No reboot is required. Relaunch SSMS and try connecting. This registry change will prevent SSMS to look for local installation of SSIS and thus initiate a RPC call to remote SSIS.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I do not find MsDtsServer130 when I search my regedit. I am trying to connect from my laptop to SSIS on a VM.

0 Votes 0 ·

This worked for me. Thanks a lot.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered JamesSQL-7436 commented

See Connect to a remote SSIS server, there are several limitations.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@OlafHelper-2800 Thank you. I've gone over that article.

II am not getting the permission denied error and I can connect to Integration Services from another machine. I'm not sure what could be going on.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft commented

Hi @JamesSQL-7436,

May I know if you Install Integration Services (SSIS) in the remote server?

Best Regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv, yes. It is installed an running. It was the first thing I checked and mentioned in my question under #1.

0 Votes 0 ·

Hi @JamesSQL-7436,

Hope the following link will be helpful:

Connecting to Integration Services Access is Denied in SQL Server 2016 or 2017

Best Regards,
Mona


0 Votes 0 ·

Thanks @Monalv-msft I don't believe permissions are my issue as I can use another machine using the same SSMS 16.5.3 and connect to our remote SQL Server and SSIS.

0 Votes 0 ·
Show more comments
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered JamesSQL-7436 commented

Hi @JamesSQL-7436,

Please try to follow steps outlined here: connecting-to-integration-services-access-is-denied-in-sql-server-2016-or-2017

Additionally, launch your SSMS 'as Administrator'.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @YitzhakKhabinsky-0887 I've verified I have permissions via your article and I am able to connect to it from another machine.
I have tried running SSMS as an Admin but that still returns the error message "The specified service does not exist as an installed service".

I've verified it is installed on the server and it is running. If I log into a machine that wasn't re-imaged, I can connect fine to SSIS using my credentials.

0 Votes 0 ·
JamesSQL-7436 avatar image
0 Votes"
JamesSQL-7436 answered JamesSQL-7436 edited

Here is the detailed error message if it may help. It doesn't make sense to me as my computer is using SSMS 16.5.3 and the server is running SQL Server 2016 and SSIS 13.0.

===================================

Cannot connect to XXXXXXXXX.

===================================

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


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


Program Location:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Connecting to the Integration Services service on the computer "XXXXXXXXXX" failed with the following error: "The specified service does not exist as an installed service.".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.



For help, click: http://go.microsoft.com/fwlink/?LinkId=506689


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)

===================================

Connecting to the Integration Services service on the computer "XXXXXXXXXXXXXXX" failed with the following error: "The specified service does not exist as an installed service.".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.



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)

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The error message:

"Cannot connect to XXXXXXXXX."

If it is a server name, could you please try its IP address instead while connecting to the Integration Services service.

0 Votes 0 ·
JamesSQL-7436 avatar image JamesSQL-7436 YitzhakKhabinsky-0887 ·

@YitzhakKhabinsky-0887 Thank you for the suggestion but I get the same error.

0 Votes 0 ·

Two more things to check:

  • Can you connect in SSMS to the SQL Server Engine on the same server where SSIS is installed?

  • Can you ping that server from your machine?

0 Votes 0 ·
Show more comments
JamesSQL-7436 avatar image
0 Votes"
JamesSQL-7436 answered

Here are my local installed programs. Is anything missing that I should have to connect to the remote SSIS 13.0?

31361-local-programs.png



local-programs.png (36.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.