c# - 使用 Dynamics GP 用户名和密码的 SqlConnection -- 用户登录失败
问题描述
我正在为 Dynamics GP 编写加载项，并且正在尝试运行自定义存储过程，但是当我尝试打开连接时，我收到一条错误消息，提示“用户登录失败：”。
如果我手动使用系统管理员用户名和密码，一切都会正确执行。但是，我想使用登录 Dynamics GP 的人的用户名和密码。
string gpUsername = Dynamics.Globals.UserId.Value;
string gpPassword = Dynamics.Globals.SqlPassword.Value;
string companyDb = Dynamics.Globals.IntercompanyId.Value;
string dataSource = "sql-server-name";
SqlConnectionStringBuilder constringBuilder = new SqlConnectionStringBuilder();
constringBuilder.UserID = gpUsername;
constringBuilder.Password = gpPassword;
constringBuilder.InitialCatalog = companyDb;
constringBuilder.DataSource = dataSource;
constringBuilder.WorkstationID = Environment.MachineName;
constringBuilder.MultipleActiveResultSets = true;
constringBuilder.ApplicationName = $"{FileVersionInfo.GetVersionInfo(Assembly.GetExecutingAssembly().Location).ProductName} (GP)";
constringBuilder.IntegratedSecurity = false;
string constring = constringBuilder.ConnectionString;
SqlConnection con = new SqlConnection(constring);
con.Open();
这是完整的堆栈跟踪：
Dynamics.exe Warning: 0 : Exception occurred while updating Item class 1255. Review the error message below.
System.Data.SqlClient.SqlException (0x80131904): Login failed for user '<username>'.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Dyn2Bib.GPAddIn.ProcessTransaction(String srs, String srsName, String changeType)
ClientConnectionId:1a4c93c1-0efd-4590-8c9f-98265eaafaa2
Error Number:18456,State:1,Class:14
如果我将DataSourcein更改constringBuilder为 use Dynamics.Globals.SqlDataSourceName.Value，它看起来根本无法在网络上找到数据源并给出错误：
Dynamics.exe Warning: 0 : Exception occurred while updating Item class 1255. Review the error message below.
System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The network path was not found
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Dyn2Bib.GPAddIn.ProcessTransaction(String srs, String srsName, String changeType)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:53,State:0,Class:20
如果我将连接字符串打印到屏幕上，它看起来是正确的，正如我所提到的，如果我手动使用系统管理员帐户，它就可以工作。
对我来说，我可以使用 Dynamics GP 帐户直接连接到 SQL 服务器吗？服务器上有相应的登录名，所以看起来应该已经正确配置了。有谁知道我在这里做错了什么？我是否需要对 Dynamics GP 帐户进行任何更改以允许它使用 SQL 身份验证进行连接？
解决方案
我假设登录到 Dynamics GP 的人的用户名和密码只是 Dynamics GP 应用程序用户，而不是实际的数据库用户。
您可以通过将身份验证更改为 Windows 身份验证来解决此问题
这是通过以下方式实现的：
- 创建一个窗口组
- 使用应该能够运行您的自定义 SP 的每个 Windows 用户填充此 Windows 组
- 使用 SQL Server Management Studio，将此 windows 组添加为 SQL Server 登录
- 在您的代码中，
删除这些行：
constringBuilder.UserID = gpUsername;
constringBuilder.Password = gpPassword;
并像这样更改此行：
constringBuilder.IntegratedSecurity = true;
这意味着将使用他们的 Windows 凭据建立连接，而不是使用（无效的）SQL 凭据
这些 Windows 凭据之前已通过完成步骤 1-3 获得服务器登录访问权限
这不是故事的结局。现在您需要使该组只能访问您的 SP
- 使用存储过程在数据库中创建数据库角色
- 授予此角色对您的自定义 SP 的执行权限
- 最后，要将其全部链接起来，请编辑您在步骤 3 中创建的登录名，授予其访问数据库的权限并将其添加到您在步骤 5 中创建的角色中
复杂？是的。但是授予用户访问数据库的权限是一件大事。这边走
- 用户可以登录并运行 SP，仅此而已。
- 要授予和删除用户，您可以在 windows 组中添加或删除他们（如果您愿意，可以重用预先存在的 GP windows 组）
- 如果您创建他们需要访问的其他数据库对象，则授予数据库角色对它们的访问权限
如果此解决方案对您有吸引力，如果您愿意，我可以对其进行扩展。
推荐阅读
- r - 如何获得跨多个列组的差异
- excel - 从 VBA 打开电子表格比较与要比较的工作表
- logic - 嵌套级别逻辑
- r - 将 list1 的整个单元格匹配到 list2 的所有单元格的开头
- javascript - 我可以在 HTML 表格中插入一行或单元格，然后可以通过单击单元格进行编辑吗？
- windows - SHA256 使用 Windows 加密文件中的行
- reactjs - react-admin：在没有 http 请求的情况下从商店更改列表
- smtp - 转发电子邮件（带附件）而不下载实际附件
- javascript - 需要获取输入的值，是否有 getElementById 反应等效？
- stackdriver - 高级过滤器不能表达ISNULL？