Unable to connect to Integration services

Question

Recently rebuilt my Windows 10 machine and I am no longer able to connect to Integration Services from SSMS when I was able to before. The error message I'm getting is the specified service does not exist as an installed service. From my searching, I'd have to use a different version of SSMS but it works on another machine with the same version I have.

I've attached the SQL Server info from both servers and they are identical for SSMS. Only the MDAC is different. I've attached two screenshots, the No version is my machine and the Yes version is the machine that works.

Is there something that I could be missing here?

Answer 1

Check if your SQL Server Integration Services is running from your Services window.

---

A Fan of SSIS, SSRS and SSAS

Answer 2

Hi JamesTJC,

Integration Services OS service is a leftover from the SSIS 2005 - 2008R2 era.

Starting from SSIS 2012 onwards, its architecture was changed, and it is using SQL Server Database Engine service, SSISDB Catalog, and SSISDB database.

That's why you are not missing anything in the SQL Server 2016 environment.

Useful links:

• https://social.technet.microsoft.com/Forums/SECURITY/en-US/89d0aff3-5ca2-4d20-bae0-2a5b999a3079/cannot-connect-to-integration-services-from-ssms?forum=sqlintegrationservices
• https://social.microsoft.com/Forums/partner/zh-CN/91cc795d-1557-4c8d-b443-20f6f40f22f1/cant-connect-to-ssis-error-message?forum=sqlintegrationservices

Answer 3

Go to all programs Click on Microsoft SQL Server 2012 folder Right click on SQL Server Management Studio Click on Run as Administrator

This should take care of problem for now. (With this you need to always repeat the same process). To avoid this every time and for a more persistent solution you need to get permission(s). Please do the following process and you should be good.

In previous versions of SQL Server, by default when you installed SQL Server all users in the Users group had access to the Integration Services service. When you install the current release of SQL Server, users do not have access to the Integration Services service. The service is secure by default. After SQL Server is installed, the administrator must grant access to the service.

To grant access to the Integration Services service

Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
On the Security tab, click Edit in the Launch and Activation Permissions area.
Add users and assign appropriate permissions, and then click Ok.
Repeat steps 4 - 5 for Access Permissions.
Restart SQL Server Management Studio.
Restart the Integration Services Service.

Answer 4

Hi James,

Please check if the Integration Services exists in SQL Server Configuration Manager.

If it doesn't exist, we can install Integration Services (SSIS).

Best Regards,

Mona

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 5

It is running from the server that I'm connecting to. It is not running on my desktop. All I have is SSMS installed to connect to another server.

Answer 6

It is running on the server I'm trying to connect to. I don't have SSIS installed on my desktop. I just use SSMS to connect to another server. Same set up on the machine that works.

Answer 7

Hi JamesTJC,

I hope you didn't miss my earlier reply.

There is no need in SSIS service.

Official Microsoft documentation: Integration Services Service (SSIS Service)

"...The topics in this section discuss the Integration Services service, a Windows service for managing Integration Services packages. This service is not required to create, save, and run Integration Services packages. SQL Server 2012 (11.x) supports the Integration Services service for backward compatibility with earlier releases of Integration Services..."

Answer 8

Yitzhak, I do need it as we have packages out there and I'm trying to download one of them to review/edit it. 

Answer 9

Hi JamesTJC,

You need to use Visual Studio to "...review/edit.." SSIS packages.

P.S. Please connect with me on LinkedIn.

Answer 10

Right, but I don't currently have this package that I need to edit. It currently sits in the Integration Services > Stored Packages > MSDB > xxxxx 

I've always downloaded files from Integrations Services to my desktop, edited in VS and then uploaded my changes. I'm unable to do that now as I can't connect on my desktop like I did before my rebuild.

Answer 11

Hi JamesTJC,

My understanding that you are on the SQL Server 2016.

It is highly recommended to abandon MSDB approach (2005 - 2008R2 obsolete era), and start using SSISDB Catalog.

Answer 12

I agree, but for the time being I need to get to MSDB via Integration Services. Any other thoughts on why I'm getting this error but on another machine with the same set-up, I can do it?

Answer 13

Hi JamesTJC,

What is your SQL Server instance version where you have the MSDB database with the SSIS packages?

There are two prerequisites::

• Both the SQL Sever Instance and the SSMS should be the same version.
  Though MS disconnected these two as of late. Another sign for you to abandon the old ways.
• Launch SSMS as Administrator.

Answer 14

When I connect to the database in SSMS it shows 13.0.5622

Answer 15

Hi JamesTJC,

• So it is 2016.

You can easily check by issuing the following T-SQL:

SELECT @@VERSION;

• Please check both prerequisites from my earlier reply.
  

Answer 16

I've tried running it as an Administrator.

My SSMS is the same version on another machine that works when it doesn't work on mine.

Answer 17

Hi JamesTJC,

Another reason could be that Windows 10 OS firewall is blocking ports that need to be opened.

Please compare firewall settings on both machines.

Answer 18

From SQL Server 2016 and later, you need to do additional manual work to grant permissions to connect to SSIS. The following article should give you a help.

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

---

A Fan of SSIS, SSRS and SSAS

Answer 19

Hi JamesTJC,

Another reason could be that Windows 10 OS firewall is blocking ports that needs to be opened.

Please compare firewall settings on both machines.

Thanks Yitzhak. I disabled my Windows 10 firewall and I'm getting the same error message.

Answer 20

From SQL Server 2016 and later, you need to do additional manual work to grant permissions to connect to SSIS. The following article should give you a help.

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

---

A Fan of SSIS, SSRS and SSAS

Guoxiong, I do have permissions set up. I can connect using my ID on another machine using the same version of SSMS. I am not getting an Access is Denied error.

Answer 21

What EXACTLY is the error message you are receiving?  What EXACTLY are you entering for the server name?

Answer 22

What EXACTLY is the error message you are receiving?  What EXACTLY are you entering for the server name?

I'm typing in the name of the server.  Typing in this name allows me to connect to the database but not Integration Services. On the other machine, I get to the DB and Integration Services by typing the same name.

Answer 23

What version of SQL Server is the server you are trying to connect too?

Answer 24

Everything I've seen says you have to use the version of SSMS that is aligned with your SQL Server, which I am.

SQL Server 2016 and SSMS 16.5.3.

Answer 25

I also suggest you download and install the current version of SSMS.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

Answer 26

Yes, what you describe is a symptom of not having the same version of SSMS than the target server SSIS version.

Answer 27

What is the version of SSIS installed on the server? 

Answer 28

What is the version of SSIS installed on the server? 

13.0

Answer 29

Hi JamesTJC,

Another reason could be that Windows 10 OS firewall is blocking ports that need to be opened.

Please compare firewall settings on both machines.

Yitzhak, I found another post elsewhere with someone having the exact same issue. You had commented on it and it appears they never got a solution to it. I have replied to RichWagg's post to see if they've made any headway as well.

Link to similar issue

Answer 30

Hi JamesTJC,

May I know if you have anything to update?

Best Regards,

Mona

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 31

Mona,

I'm still having the issue. I'm running the same version as the target server. I've disabled firewall on my machine but still get the error.

The exact same set up on a VM works just fine.

Answer 32

Hi JamesTJC,

Hope the following links will be helpful:

SQL Server Management Studio (SSMS) – Unable to Connect to SSIS – The Specified Service Does Not Exist as an Installed Service.

SSIS connection error with - "The Specified service does not exist as an installed service".

Best Regards,

Mona

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 33

Mona,

Thank you for your response. The links however, did not help. The first link, I've already verified I'm using 16.5.3 on my machine that does not work. Using 16.5.3 on another machine does work.

Your second link describes my situation perfectly. I need to access Integration Services. We do have some projects set up in SSISDB however, we have many that are still in Integration Services and need to be accessed that way. This link does not seem to offer any suggestion on how to connect to Integration Services.