Solved

Connecting to the SSIS

Posted on 2014-03-30
14
402 Views
Last Modified: 2016-02-10
I get a fresh installation of SQL server 2012 with database engine and SSIS component, and after the installation is done, I found that I can't connect to the SSIS and I got the following error:

SSIS-open-error.jpg
how to fix it ?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
14 Comments
 
LVL 9

Assisted Solution

by:edtechdba
edtechdba earned 250 total points
ID: 39964886
When you launched Integration Services, did you try right-clicking on the SSIS icon and running as administrator?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39964924
"When you launched Integration Services,"


it launch from SQL configuration manager automatically ,right?

or the account which use to start it has to be local/domain administrator ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39964926
Currently the account use as the start up account for the integration serve is not an administrator ! why need? too much power on that machine, right?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 1

Author Comment

by:marrowyung
ID: 39964937
one thing is, even the startup account for the integration service is a local account, not local administrator, that service can start ! so this mean the startup is ok .
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39966016
I found it is only happen on SQL server 2012 on Windows 8, I have 2x Windows 8.1 machine with latest update, they gives the same error.

The account the SSIS start with already local administrator account, so this is not admin right problem !

the SQL server 2012 on Windows 7 do not have this problem.
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 250 total points
ID: 39970332
As the error message states, the account you're using to run SQL Management Studio needs to not only be a local administrator on the server hosting SSIS, but also much have been launched via the "Run as Administrator" escalation (via Right-click when you launch it). Without those two things, you will be unable to connect to and manage SSIS.

I'm not sure why it needs the escalated rights, but it does - there's an article that gives you the instructions to let a non-administrative user connect and manage SSIS, but I've never been able to get it to work without the user being a local administrator.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39971141
"As the error message states, the account you're using to run SQL Management Studio needs to not only be a local administrator on the server hosting SSIS, but also much have been launched via the "Run as Administrator" escalation (via Right-click when you launch it). Without those two things, you will be unable to connect to and manage SSIS."

I read some article too ! but the method they said is not correct and cant' fix the problem, I am talking about the DCOMcfg one, it just doens't work.

yeah, right lick the SQL SSMS and run as administrator can do it! it is the SSIS design by default!

but just can't see why ! MS SQL server don't need to be secure?

" but I've never been able to get it to work without the user being a local administrator. "

then we keep silence on this as this make hte non DBA one can't touch the SSIS, right? but we can't control the developer on touching that as they can connect to it using SQL data tools! right?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39973076
Right. They don't have to be a sysadmin on the database server where you're keeping packages, but they will have to be in the local administrators group on the actual SSIS server. These are commonly the same physical server, but they don't have to be - even if they are, you can revoke SQL Server rights from "BUILTIN\Administrators" (who have sysadmin by default) and grant them to your actual DBA group. While local admin rights on the SSIS server still aren't ideal, this will help keep your sql server itself safe.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39975021
sorry, other than right click on the SSMS and run as administrator, what else I can do ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39975159
"They don't have to be a sysadmin on the database server where you're keeping packages, but they will have to be in the local administrators group on the actual SSIS server."

the account of that SSIS service has been assigned the local administrator right, but it doesn't help on this !
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39975342
The service running SSIS doesn't matter in this case - it's the rights of the person connecting to the SSIS service to manage it. Permissions are really odd in the case of users interacting with SSIS, but that's the way they are, unfortunately.

It seems set up to require your DBAs, not your developers, to deploy and manage SSIS packages.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39977101
"Permissions are really odd in the case of users interacting with SSIS, but that's the way they are, unfortunately."

Yes, I can't see why only secure SSIS but not MS SQL in this way ! funny ! probalby SSIS has very special logic that even DBA don't have access.

"The service running SSIS doesn't matter in this case - it's the rights of the person connecting to the SSIS service to manage it"

I don't think so, BEFORE we can connect to it, who can connect to it ?

"It seems set up to require your DBAs, not your developers, "

I am the DBA and learning SSIS. that's why I am wondering !
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question