Problem logging on to Integration Services

  • 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.

  • 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" 😉

  • 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?

  • 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" 😉

  • 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".

  • 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.

  • 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

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

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

  • 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

  • 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

  • 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"?>

    <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>

    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

  • 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".

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

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

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

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