如何在AWS中运行SSIS包？能否通过SQL身份验证替代Windows身份验证运行？

Great questions! Let's walk through how to run SSIS packages in AWS and how to use SQL Authentication instead of Windows Auth for those packages.

There are a few practical options to run SSIS packages in AWS, depending on your workload needs:

• Windows EC2 Instance: This is the most direct approach since SSIS is tightly tied to Windows environments. Spin up a Windows-based EC2 instance, install SQL Server with the SSIS component, and you can run packages just like you would on an on-premises Windows server. Use SQL Server Agent for scheduled runs, the SSIS Catalog (SSISDB) for centralized management, or command-line tools like dtexec.exe for ad-hoc execution.
• Windows Containers on AWS Fargate: If you prefer containerized workflows, AWS supports Windows containers in Fargate. Package your SSIS runtime into a Windows container image, then deploy it to Fargate for on-demand execution. This is perfect for workloads that don't need a persistent server, helping you cut down on unnecessary costs.
• AWS Glue (Migration/Alternative): If you're open to moving beyond traditional SSIS, AWS Glue is a managed ETL service with tools to migrate SSIS packages to Glue jobs. This isn't running SSIS natively, but it's a strong option if you want to leverage fully managed AWS services.

Absolutely—you can run SSIS packages using SQL Authentication instead of Windows Authentication, and it works across most deployment scenarios:

1. SSIS Catalog (SSISDB) Setup #

• First, make sure your SQL Server instance has mixed authentication mode enabled (this is mandatory to allow SQL logins).
• Create a SQL Server login and map it to the SSISDB database with appropriate roles: assign ssis_executor for basic execution access, or ssis_admin if you need full management rights.
• When connecting to SSISDB (via SSMS, dtexec, or your application), use the SQL login credentials instead of Windows Auth to execute packages.

2. Command-Line Execution (dtexec.exe) #

• You can specify SQL Authentication directly in the dtexec command when targeting SSISDB or package data sources. Example command:

  dtexec /ISServer "\SSISDB\MyETLProject\MyDataLoadPackage.dtsx" /Server "my-aws-sql-instance.cxxxxxx.us-east-1.rds.amazonaws.com" /U "etl_sql_user" /P "your-secure-password"
  

• For packages stored locally or in file systems, use the /Connection parameter to override data source authentication with SQL credentials.

3. Package Connection Manager Configuration #

• Inside your SSIS package, edit each data source connection manager and switch the authentication mode to SQL Server Authentication. Enter the SQL username and password (or use a configuration file/environment variable to avoid hardcoding sensitive info).
• Note: If your package accesses Windows-specific resources (like on-premises file shares), SQL Authentication accounts won't have native access to those. But for AWS resources (like S3 buckets, RDS databases), you can pair SQL Auth with IAM roles or AWS access keys where needed.

AWS-Specific Tips #

• If running on EC2 or RDS SQL Server, double-check that mixed authentication is enabled: for EC2, use SQL Server Configuration Manager; for RDS, enable it via the RDS console parameter group.
• For sensitive credentials (like SQL passwords), avoid hardcoding them in packages. Use AWS Secrets Manager to store and retrieve credentials at runtime, or leverage SQL Server's credential store for more secure management.

内容的提问来源于stack exchange，提问作者MJ8