SSIS to SQL Server permissions

Question

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

Answer 1

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.

Answer 2

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.