Problem logging on to Integration Services

Question

Problem logging on to Integration Services

k.arkenau

Valued Member

Points: 59
More actions
February 17, 2014 at 9:20 am

#396277

Hello,
I got a problem with a user who needs to log on to the integration services.
The user ist is "sysadmin" on the SQL Server and I granted him rights in the DCOM configuration for MSSQL Integration Services (remote and local log-on and remote and local execute and start).
But if the user trys to log-on to the integration services it says "Access denied" and I have no idea why.
Is anyone here who might have a clue what to do?
Many thanks
Yours
k.arkenau
P.S.: I attached screenshots of the settings I made, but they are in german. Perhaps someone can do something with then even though they are not in english.

Viewing 15 posts - 1 through 15 (of 20 total)

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

k.arkenau (2/17/2014)
Hello,
I got a problem with a user who needs to log on to the integration services.
The user ist is "sysadmin" on the SQL Server and I granted him rights in the DCOM configuration for MSSQL Integration Services (remote and local log-on and remote and local execute and start).
But if the user trys to log-on to the integration services it says "Access denied" and I have no idea why.
Is anyone here who might have a clue what to do?
Many thanks
Yours
k.arkenau
P.S.: I attached screenshots of the settings I made, but they are in german. Perhaps someone can do something with then even though they are not in english.

Firstly, don't grant the user directly to the DCOM permissons in Component services. There is a local Windows group called "Distributed COM Users", use this local group to secure the required permissons and add domain users or groups to the local group.

On the Security tab you need to set permissions in the Launch and Activation Permissions and the Access Permissions. Restart the service after change these permissions

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

"Ya can't make an omelette without breaking just a few eggs" 😉

k.arkenau Valued Member Points: 59 More actions · Answer 2

Ok, I changed that and added the User to the DCOM User group.

Nevertheless, the user still cannot logon to the information services.

Any idea?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

k.arkenau (3/19/2014)
Ok, I changed that and added the User to the DCOM User group.
Nevertheless, the user still cannot logon to the information services.
Any idea?

If you used distributed com users group ensure the user is a member of this group.

Did you set both sets of permissions?

Do you still get the same error?

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

"Ya can't make an omelette without breaking just a few eggs" 😉

k.arkenau Valued Member Points: 59 More actions · Answer 4

Hello,

I just checked it once more:

Yes, the User is member of the group Distributed COM-User and the group has the permissions you talked about.

But if the user trys to log on to "Integration Services" it says "Access denied".

P Jones SSChampion Points: 12361 More actions · Answer 5

In the properties of his login in Security in Management Studio, user mapping tab, msdb database, I think he should have one of the db_ssis..... roles ticked, probably db_ssisadmin.

Being sysadmin doesn't give it by default. Books Online will tell you more.

grantbruce SSC Enthusiast Points: 133 More actions · Answer 6

I found this link helpful for that same scenario, the permission changes you need are defined in the section Eliminating the "Access is Denied" Error:

http://technet.microsoft.com/en-us/library/aa337083.aspx

k.arkenau Valued Member Points: 59 More actions · Answer 7

As the user did a lot of programming on the Database, he is sysadmin already.

k.arkenau Valued Member Points: 59 More actions · Answer 8

k.arkenau

Valued Member

Points: 59

March 20, 2014 at 8:28 am

#1698708

That is what I already did...als told above.

grantbruce SSC Enthusiast Points: 133 More actions · Answer 9

This process worked for me:

Verify account is member of Distributed COM Users

@ Component services, My Computer, DCOM Config, Microsoft SQL Server Integration services 11.0, Properties, Security, add account

Edit <Install path; mine is D>:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml, add new instance(s)

Regedit HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile

<Installpath: mine is D>:\Program Files\Microsoft SQL Server\110\DTS\Binn\ MsDtsSrvr.ini.xml

Grant account db_ssisadmin permissions in MSDB

Restart IS

k.arkenau Valued Member Points: 59 More actions · Answer 10

grantbruce (3/20/2014)
This process worked for me:
Verify account is member of Distributed COM Users
@ Component services, My Computer, DCOM Config, Microsoft SQL Server Integration services 11.0, Properties, Security, add account
OK
Edit <Install path; mine is D>:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml, add new instance(s)
What to edit where?
Regedit HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile
<Installpath: mine is D>:\Program Files\Microsoft SQL Server\110\DTS\Binn\ MsDtsSrvr.ini.xml
OK
Grant account db_ssisadmin permissions in MSDB
Already granted.
Restart IS

grantbruce SSC Enthusiast Points: 133 More actions · Answer 11

This process worked for me:

Verify account is member of Distributed COM Users

@ Component services, My Computer, DCOM Config, Microsoft SQL Server Integration services 11.0, Properties, Security, add account

OK

Edit <Install path; mine is D>:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml, add new instance(s)

What to edit where? The xml file needs to be edited to reflect instance name(s):

<?xml version="1.0" encoding="utf-8"?>

</Folder>

<Name>File System</Name>

<StorePath>..\Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

From: http://technet.microsoft.com/en-us/library/ms137789.aspx

Regedit HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile

<Installpath: mine is D>:\Program Files\Microsoft SQL Server\110\DTS\Binn\ MsDtsSrvr.ini.xml

OK

Grant account db_ssisadmin permissions in MSDB

Already granted.

Restart IS

k.arkenau Valued Member Points: 59 More actions · Answer 12

What to edit where? The xml file needs to be edited to reflect instance name(s):
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

So you are saying I have to enter the configuration here? I am sorry, I am a little confused because originally you said something about "instances".

grantbruce SSC Enthusiast Points: 133 More actions · Answer 13

No problem, just meant that if you are doing named instances, have to add them in the xml file.

k.arkenau Valued Member Points: 59 More actions · Answer 14

I do named instances...just where to add then in this file?