SSIS to SQL Server permissions

RNA 1 Reputation point
2021-09-23T10:28:17.117+00:00

Hi,

Here is the setup:

SQL Server 2017 on a stand-alone machine
SQL Server Management Studio (SSMS) on a stand-alone machine
SSIS (SSDT) Server 2017 on a stand-alone machine

What permissions/credentials do we need when

  1. Connecting to SQL server using SSMS
  2. Deploying SSIS packages from SSDT server to SQL server

Please consider these two scenarios when providing answers to the above 2 questions.

1- When all above three machines are on the same Active Directory domain
2- When all above three machines are in a workgroup, no AD

Thanks and Regards

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,796 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,459 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,001 Reputation points
    2021-09-24T05:18:38.817+00:00

    What permissions/credentials do we need when

    Permissions, what else? And what for permission depends depends what the account should be allowed to do. Read, write, execute, ...

    2- When all above three machines are in a workgroup, no AD

    No AD, but Workgroup means you can not use Windows logon for authentication, you have to use SQL logons/user.

    0 comments
  2. CarrinWu-MSFT 6,856 Reputation points
    2021-09-24T06:02:51.927+00:00

    Hi @RNA-9746,

    Welcome to Microsoft Q&A!

    Connecting to SQL server using SSMS

    Connecting SQL server from a standalone machine, you should enable protocols, configure a fixed port, open ports in the fire wall, enable SQL Server Brower. For more information, please refer to Connecting from Another Computer.

    Deploying SSIS packages from SSDT server to SQL server

    Integration Services supports two deployment models, the project deployment model and the legacy package deployment model. If you would like to deploy packages to standalone SQL Server, you should choose the legacy package deployment model. For more information, please refer to Legacy Package Deployment (SSIS)

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments