Wednesday, May 09, 2012

Windows account has been assigned SQL sysadmin role but cannot connect to Integration services with SSMS

When a new Windows Account (user or a group) has been granted the sysadmin server role, it does not automatically follow that this account has full Integration Services administration privileges. To administer Integration Services you need a windows account, a SQL server account will not work.

  • In the database engine the account has to be added to the Security Logins with Server Roles public and Sysadmin.
    • Use SSMS to connect to the database engine of the server that also runs the integration services.
    • Under Security, right-click on Logins, select New Login…
    • In the Login-New dialog window
      • leave the option Windows authentication
      • enter the name of a Windows Account (can be a user or a group)
      • Under Server roles, check public and sysadmin
      • click OK to create the login.
  • In the windows server on which the Integration Services are running the account has to be added to the local system administrators group.
    • Log on to the windows server
    • under Start / Administrative tools, select Computer management
    • In the computer Management console
      • System tools / Local User and Groups / Groups
      • right click on Administrators in the right hand pane, select Add to group (or select properties)
      • In the Adminstrators properties dialog
        • click on the Add… button
        • enter the Windows Account name (the same as was used in the SQL logins)

This Windows account will now be able to connect with SSMS to Integration services server and perform all tasks necessary to deploy and run packages.

No comments: