为什么我必须在网站上添加“工作站ID”和“应用程序名称”数据库连接字符串中的属性，以便正确查询视图？ [英] Why do I have to include the “workstation id” and "application name" attributes in the Database Connection String in order to query a view properly?
问题描述
在我们的工作场所中，我们有一个ASP.NET应用程序，该应用程序使用Microsoft SQL Server 2016 Web Edition数据库服务器作为后端数据库。
At our workplace, we have an ASP.NET application that uses Microsoft SQL Server 2016 Web Edition Database Server for the back-end database.
首先，在在ASP.NET应用程序web.config中，我将数据库连接字符串配置为：
At first, In the ASP.NET application web.config, I configured the Database Connection String to be:
Password=blahPasswordblah;
Persist Security Info=True;
User ID=sa;
Initial Catalog=yadaDatabaseNameyada;
Data Source=blahDNSServerNameblah\yadaSQLServerInstanceNameyada
似乎连接正确。但是，当ASP.NET应用程序中的代码尝试查询特定视图时，我注意到了一些奇怪的事情。查询将返回空结果集。
It seemed to connect properly. However, I noticed something strange when the code in the ASP.NET application would try to query a particular view. The query would return an empty result set.
但是，当我更改连接字符串以包括工作站ID和应用程序名称时：
However, when I changed the connection string to include the workstation id and the application name:
Password=blahPasswordblah;
Persist Security Info=True;
User ID=sa;
Initial Catalog=yadaDatabaseNameyada;
Data Source=blahDNSServerNameblah\yadaSQLServerInstanceNameyada;
workstation id=blahWorkStationIDblah;
application name=blahApplicationNameBlah
在相关视图上的查询将返回正确的数据，即我期望如此。
the query on the view in question would return the proper data that I expected.
以下是关于视图的元数据：
Here is the metadata about the view:
USE yadaDatabaseNameyada;
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE table_name = 'blahViewOfInterestblah'
TABLE_CATALOG
yadaDatabaseNameyada
TABLE_SCHEMA
dbo
TABLE_NAME
blahViewOfInterestblah
VIEW_DEFINITION
Create View dbo.blahViewOfInterestblah ..........
CHECK_OPTION
NONE
IS_UPDATABLE
NO
当我在数据库连接字符串中包含工作站ID和应用程序名称时，为什么有关视图上的查询返回实际的正确结果？
Why does the query on the view in question return the actual proper results when I include the workstation id and the application name in the Database Connection String?
我问的原因是，当我们最终将ASP.NET应用程序部署到生产环境时，拥有工作站ID属性和
The reason why I’m asking is because when we ultimately deploy the ASP.NET application to production, it would be poor practice to have a "workstation id" attribute and the "application name" attribute in the Database Connection String.
为什么在没有工作站ID属性和应用程序名称属性的情况下不能使用？
Why won't it work withOut the "workstation id" attribute and the "application name" attribute?
推荐答案
尽管这是一个古老的问题，但我一直在寻找有关此主题的信息，因此可以部分解答该问题，并且在不了解更多有关数据库，服务器和应用程序用户帐户的配置的情况下，这是我的简要答案：
Though this is an old question, I was looking for info on this very subject, so as a partial answer to this question, and without knowing more about the configuration of the database, server and application user account, here's my brief answer:
根据 Microsoft ，应用程序（APP）和工作站ID（WSID）都是可选的：
According to Microsoft, both Application (APP) and Workstation ID (WSID) are optional:
APP 调用 SQLDriverConnect （可选）。如果指定，则此值存储在master.dbo.sysprocesses列program_name中，并由 sp_who 和 APP_NAME 函数。
APP Name of the application calling SQLDriverConnect (optional). If specified, this value is stored in the master.dbo.sysprocesses column program_name and is returned by sp_who and the APP_NAME functions.
WSID 工作站ID。通常，这是应用程序所在的计算机的网络名称（可选）。如果指定，则此值存储在master.dbo.sysprocesses列主机名中，并由 sp_who 和 APP_NAME 和 HOST_NAME 函数。
WSID The workstation ID. Typically, this is the network name of the computer on which the application resides (optional). If specified, this value is stored in the master.dbo.sysprocesses column hostname and is returned by sp_who and the APP_NAME and the HOST_NAME function.
您是否尝试过更改应用程序SQL用户的特权或将其添加到DBO角色？
Have you tried changing the privileges of the application SQL user or adding them to the DBO role?
这篇关于为什么我必须在网站上添加“工作站ID”和“应用程序名称”数据库连接字符串中的属性，以便正确查询视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
