SSIS:动态连接字符串集成安全模式的配置 [英] SSIS: Configuration of Dynamic Connection String Integrated Security Mode

问题描述

在本地环境中，我们使用带有用户名和密码的 SQL 身份验证连接到数据库.我创建了一个项目连接管理器，其中的表达式绑定到项目属性，用户名和密码设置为敏感.

In the local environments, we're using SQL Authentication with Username and Password to connect to the databases. I created a Project Connection Manager that has expressions bound to Project Properties, Username and Password being set to sensitive.

在开发服务器上，SSIS运行时，需要使用AD账号.我可能需要为 SQL 代理创建一个凭据/代理，但现在我以用户身份登录并通过 SQL 执行包.

On the dev server, when the SSIS run, it needs to use an AD account. I might need to create a Credential/Proxy for the SQL Agent, but for now I'm logged in as the user and I execute the packag through SQL.

在 SSIS 项目本身中，我尝试配置动态连接字符串以在一种情况下使用集成安全性，而在另一种情况下使用 SQL 帐户.我只是不知道该怎么做.我尝试过的事情:

In the SSIS project itself, I'm trying to configure a dynamic connection string to use Integrated Security in one case, and SQL Account in another. I just can't figure out how to do it. Things I tried:

1- 创建一个布尔值UseIntegratedSecurity"；范围.在连接字符串中，使用 bool 来设置 IntegratedSecurity=SSPI 是否与表达式一起使用，并使用表达式单独设置连接字符串的其他属性.没用，说无法建立连接字符串.

1- Created a boolean "UseIntegratedSecurity" parameter. In the connection string, use that bool to set IntegratedSecurity=SSPI or not with the expression, and also use expressions to set the other attributes of the connection string individually. It didn't work, said the connection string could not be built.

2- 创建一个布尔值UseIntegratedSecurity"；参数，并将我的连接字符串写成(丑陋的)像: @[$Project::IntegratedSecurity] ?"数据源="+@[$Project::SqlServerName]+"初始目录="+@[$Project::SqlServerDatabase]+";Provider=SQLNCLI11.1;Auto Translate=False;Integrated安全=SSPI;": "Data Source="+@[$Project::SqlServerName]+";Initial Catalog="+@[$Project::SqlServerDatabase]+"Provider=SQLNCLI11.1;Auto Translate=False;用户 ID="+@[$Project::SqlServerUsername]+";密码=";+ @[$Project::SqlServerPassword]

2- Created a boolean "UseIntegratedSecurity" parameter, and write my connection string as something (ugly) like: @[$Project::IntegratedSecurity] ? "Data Source="+@[$Project::SqlServerName]+";Initial Catalog="+@[$Project::SqlServerDatabase]+";Provider=SQLNCLI11.1;Auto Translate=False;Integrated Security=SSPI;" : "Data Source="+@[$Project::SqlServerName]+";Initial Catalog="+@[$Project::SqlServerDatabase]+";Provider=SQLNCLI11.1;Auto Translate=False;User ID="+@[$Project::SqlServerUsername]+";Password=" + @[$Project::SqlServerPassword]

它不起作用，因为 SqlServerUserName 和 SqlServerPassword 是敏感的，它拒绝.

It didn't work because since SqlServerUserName and SqlServerPassword are sensitive, it refuses.

3- 尝试使用连接字符串、服务器、数据库、用户、密码的项目参数并将它们全部设置.在本地工作，但在服务器上，我收到无效的授权规范".

3- Tried having Project Parameters for ConnectionString, Server, Database, User, Password and setting them all. Works locally, but on the server, I get "Invalid Authorization Specifications".

想法?谢谢

推荐答案

当在一个环境中必须使用 SQL 身份验证，而在另一个环境中 - AD 身份验证时，您需要处理这种情况.
这可以在 SSIS 目录环境 变量.创建项目文件时，Visual Studio 会自动为每个 OLEDB 连接管理器创建以下所谓的项目连接参数:

You need to handle situation when in one environment you have to use SQL Authentication, and on the other - AD Authentication.
This can be done with help of SSIS Catalog Environment variables. When you create a Project file, Visual Studio automatically creates the following so called project connection parameters for each OLEDB connection manager :

• CM..ConnectionString
• CM.<连接管理器名称 >.InitialCatalog
• CM..Password 创建为敏感参数
• CM..ServerName
• CM..UserName

OLEDB 就是一个例子，SSIS 为其他连接管理器类型创建了类似的参数.重要事实，您不必创建额外的项目参数.提到的参数是在正在构建的项目中创建的，并且存在于所有项目中.

OLEDB is an example, SSIS creates similar parameters for other connection manager types. Important fact, you do not have to create additional project parameters. The parameters mentioned are created on project being built and are present on all projects.

我们创建环境变量，指定连接字符串、数据库名称(初始目录)、服务器名称等.好东西 - 首先应用连接字符串变量，然后用其他变量修改.
有关这些参数的更多详细信息，请参见 MS 文档.

We create environment variables which specify connection string, DB name (initial catalog), Server Name etc. Good thing - Connection string variable is applied first, and then amended with the other variables.
More details on these parameters is in MS Docs.

如果与您的情况类似，在开发环境中 - 使用 SQL Auth 为 SQL Auth 定义 Conn 字符串并在相应的变量中指定用户名和密码.在使用 SSPI 的 QA 环境中 - 为 SSPI 重新设计了连接字符串，用户名和密码环境变量为空.

In case similar to yours, in Dev environment - using SQL Auth define Conn string for SQL Auth and specify username and password in corresponding variables. In QA env where SSPI is used - the Connection string is reworked for SSPI, UserName and Password environment variables are empty.

这篇关于SSIS:动态连接字符串集成安全模式的配置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！