question

RNA avatar image
0 Votes"
RNA asked CarrinWu-MSFT commented

SSIS to SQL Server permissions

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-generalsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RNA-9746, we have not get a reply from you. Could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.