I am trying to host a SQL server database, but whenever I try to connect to it I get this error:
我正在尝试建立一个SQL server数据库，但是每当我尝试连接到它时，我就会得到这个错误:
The login is from an untrusted domain and cannot be used with Windows authentication
登录来自一个不受信任的域，不能与Windows身份验证一起使用。
I am connecting through Matlab using the following command:
我通过Matlab连接，使用以下命令:
conn = database('Clinical_Data','DoyleLab07\Acc','','com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://DOYLELAB07\SQLEXPRESS:54287;database=Clinical_Data;integratedSecurity=true;').
Connecting to the database using matlab worked fine as long as I was using matlab on the computer which I was using to host the server. However, when I use another computer and the same Matlab command I get the error I showed above.
使用matlab连接数据库，只要我在计算机上使用matlab，就可以正常工作。但是，当我使用另一台计算机和相同的Matlab命令时，我得到了上面显示的错误。
When I look under control panel\system. I notice that no domain is listed on my host PC or the PC I am using to connect to the host, but both computers are in the same workgroup. Would I be able to fix my problem by creating a domain and adding the foreign PC and the host to that domain? If so, how can this be accomplished?
当我查看控制面板\系统。我注意到在我的主机PC或我用来连接主机的PC上没有列出域，但是这两台计算机都在同一个工作组中。我能否通过创建一个域并将外国PC和主机添加到该域来解决我的问题?如果是这样，该如何实现呢?
Any suggestions will be very much appreciated. Thank you for reading my post.
我们将非常感谢您的建议。谢谢你阅读我的文章。
7 个解决方案
#1
19
In order to use Windows Authentication one of two things needs to be true:
要使用Windows身份验证，有两点必须是正确的:
- You are executing from the same machine as the database server.
- 您从与数据库服务器相同的机器执行。
- You have an Active Directory environment and the user the application is executing under (usually the logged in user) has rights to connect to that database.
- 您有一个活动目录环境，应用程序执行的用户(通常是登录用户)有权连接到该数据库。
If neither of those are true you have to do one of two things:
如果这两个都不是真的，你必须做两件事之一:
- Establish a Windows Domain Controller, connect all of the relevant machines to that controller, then fix SQL server to use domain accounts; OR,
- 建立一个Windows域控制器，将所有相关的机器连接到该控制器，然后修复SQL server使用域帐户;或者,
- Change SQL server to use both Windows and SQL Server accounts.
- 更改SQL server以同时使用Windows和SQL server帐户。
By FAR the easiest way is to change SQL Server to use both Windows and SQL server accounts. Then you just need to create a sql server user on the DB server and change your connection string to do that.
到目前为止，最简单的方法是将SQL Server更改为同时使用Windows和SQL Server帐户。然后，您只需在DB服务器上创建一个sql server用户，并更改连接字符串来执行该操作。
Best case option 1 will take a full day of installation and configuration. Option 2 ought to take about 5 minutes.
最佳案例选项1将花费一整天的安装和配置。选项2大约需要5分钟。
#2
15
Getting rid of Integrated Security=true worked for me.
摆脱集成安全性=true对我很有效。
#3
4
If your SQL Server is on one domain controller and you are trying to connect to it from another domain controller then you will get this error when
如果您的SQL服务器位于一个域控制器上，并且您正在尝试从另一个域控制器连接到它，那么您将在什么时候得到这个错误
IntegratedSecurity = true;
This will happen even if you include a valid SQL Server username and password in your connection string as they will automatically be over-written with your windows login and password. Integrated security means simply - use your windows credentials for login verification to SQL Server. So, if you are logged in to a different domain controller then it will fail. In the case where you are on two different domain controllers then you have no choice but to use
即使在您的连接字符串中包含有效的SQL Server用户名和密码，也会发生这种情况，因为它们将自动被您的windows登录和密码覆盖。集成安全意味着简单地——使用您的windows凭证登录到SQL Server。因此，如果您登录到另一个域控制器，那么它将失败。如果您在两个不同的域控制器上，那么您别无选择，只能使用
IntegratedSecurity = false;
Now, when Integrated security is false SQL Server will use the SQL Server login and password provided in your connection string. For this to work, the SQL Server instance has to have its authentication mode configured to mixed mode, being, SQL Server and Windows Authentication mode.
现在，当集成安全性为false时，SQL Server将使用连接字符串中提供的SQL Server登录和密码。为此，SQL Server实例必须将其身份验证模式配置为混合模式，即SQL Server和Windows身份验证模式。
To verify or change this setting in SQL Server you can open the SQL Server Management Studio and right-click on your server name and then select Properties. On the pop-up that appears select Security and you will see where to alter this setting if you need to.
要在SQL Server中验证或更改此设置，可以打开SQL Server Management Studio，右键单击服务器名，然后选择Properties。在弹出的“选择安全性”窗口中，如果需要，您将看到在何处修改此设置。
#4
3
Why not use a SQL Server account and pass both the user name and password?
为什么不使用SQL Server帐户并同时传递用户名和密码呢?
Here is the reason why.
这就是原因。
In short, it looks like you have an authentication issue.
简而言之，看起来您有一个身份验证问题。
The problem with workgroups is there is no common Access Control List like Active Directory (AD). If you are using ODBC or JDBC, the wrong credentials are passed.
工作组的问题是没有像Active Directory (AD)这样的公共访问控制列表。如果您正在使用ODBC或JDBC，则传递错误的凭据。
Even if you create a local windows account (LWA) on the machine (SE) that has SQL Express installed (SE\LWA), the credentials that will be passed from your client machine (CM) will be CM\LWA.
即使您在已经安装了SQL Express (SE\LWA)的机器上创建了一个本地windows帐户(LWA)，您的客户端机器(CM)所传递的凭据也将是CM\LWA。
#5
3
I've had this same issue when using DNS aliases and hosts files to connect to a machine using a different domain name.
当使用DNS别名和主机文件连接到使用不同域名的机器时，我也遇到过同样的问题。
Say you have a SQL server called sql1 on mydomain.com - which is an Active Directory domain - and you also have a DNS zone for mydomain.net, and - for consistency - you set up a DNS alias (CNAME) record for database.mydomain.net --> sql1.mydomain.com
假设您在dommyain.com上有一个名为sql1的SQL服务器—它是一个活动目录域—您还为mydomain.net设置了一个DNS区域，为了一致性，您为database.mydomain.net—> sql1.mydomain.com设置了一个DNS别名(CNAME)记录
You'll be able to connect to sql1.mydomain.com using Windows integrated security, but won't be able to connect to database.mydomain.net even though it's the same server because the domain name doesn't match your AD domain.
您将能够使用Windows集成安全性连接到sql1.mydomain.com，但不能连接到database.mydomain.net，即使它是同一个服务器，因为域名与您的AD域不匹配。
#6
2
This error message can also occur if the account you are using to access the SQL server is locked out by the domain.
如果您用于访问SQL服务器的帐户被域锁定，也可能出现此错误消息。
#7
0
In my case the Aliases within SQL Native Client 11.0 Configuration were pointing to invalid server/IP. Once updated it worked correctly.
在我的例子中，SQL本地客户端11.0配置中的别名指向无效的服务器/IP。一旦更新，它就能正常工作。
To check: 1. Start "SQL Server Configuration Manager" 2. Navigate to "SQL Native Client 11.0 Configuration" and then "Aliases" 3. Ensure "Alias Name" and "Server" match correctly for TCP/IP
检查:1。启动“SQL Server Configuration Manager”2。导航到“SQL本机客户端11.0配置”，然后“别名”3。确保TCP/IP的“别名”和“服务器”匹配正确
#1
19
In order to use Windows Authentication one of two things needs to be true:
要使用Windows身份验证，有两点必须是正确的:
- You are executing from the same machine as the database server.
- 您从与数据库服务器相同的机器执行。
- You have an Active Directory environment and the user the application is executing under (usually the logged in user) has rights to connect to that database.
- 您有一个活动目录环境，应用程序执行的用户(通常是登录用户)有权连接到该数据库。
If neither of those are true you have to do one of two things:
如果这两个都不是真的，你必须做两件事之一:
- Establish a Windows Domain Controller, connect all of the relevant machines to that controller, then fix SQL server to use domain accounts; OR,
- 建立一个Windows域控制器，将所有相关的机器连接到该控制器，然后修复SQL server使用域帐户;或者,
- Change SQL server to use both Windows and SQL Server accounts.
- 更改SQL server以同时使用Windows和SQL server帐户。
By FAR the easiest way is to change SQL Server to use both Windows and SQL server accounts. Then you just need to create a sql server user on the DB server and change your connection string to do that.
到目前为止，最简单的方法是将SQL Server更改为同时使用Windows和SQL Server帐户。然后，您只需在DB服务器上创建一个sql server用户，并更改连接字符串来执行该操作。
Best case option 1 will take a full day of installation and configuration. Option 2 ought to take about 5 minutes.
最佳案例选项1将花费一整天的安装和配置。选项2大约需要5分钟。
#2
15
Getting rid of Integrated Security=true worked for me.
摆脱集成安全性=true对我很有效。
#3
4
If your SQL Server is on one domain controller and you are trying to connect to it from another domain controller then you will get this error when
如果您的SQL服务器位于一个域控制器上，并且您正在尝试从另一个域控制器连接到它，那么您将在什么时候得到这个错误
IntegratedSecurity = true;
This will happen even if you include a valid SQL Server username and password in your connection string as they will automatically be over-written with your windows login and password. Integrated security means simply - use your windows credentials for login verification to SQL Server. So, if you are logged in to a different domain controller then it will fail. In the case where you are on two different domain controllers then you have no choice but to use
即使在您的连接字符串中包含有效的SQL Server用户名和密码，也会发生这种情况，因为它们将自动被您的windows登录和密码覆盖。集成安全意味着简单地——使用您的windows凭证登录到SQL Server。因此，如果您登录到另一个域控制器，那么它将失败。如果您在两个不同的域控制器上，那么您别无选择，只能使用
IntegratedSecurity = false;
Now, when Integrated security is false SQL Server will use the SQL Server login and password provided in your connection string. For this to work, the SQL Server instance has to have its authentication mode configured to mixed mode, being, SQL Server and Windows Authentication mode.
现在，当集成安全性为false时，SQL Server将使用连接字符串中提供的SQL Server登录和密码。为此，SQL Server实例必须将其身份验证模式配置为混合模式，即SQL Server和Windows身份验证模式。
To verify or change this setting in SQL Server you can open the SQL Server Management Studio and right-click on your server name and then select Properties. On the pop-up that appears select Security and you will see where to alter this setting if you need to.
要在SQL Server中验证或更改此设置，可以打开SQL Server Management Studio，右键单击服务器名，然后选择Properties。在弹出的“选择安全性”窗口中，如果需要，您将看到在何处修改此设置。
#4
3
Why not use a SQL Server account and pass both the user name and password?
为什么不使用SQL Server帐户并同时传递用户名和密码呢?
Here is the reason why.
这就是原因。
In short, it looks like you have an authentication issue.
简而言之，看起来您有一个身份验证问题。
The problem with workgroups is there is no common Access Control List like Active Directory (AD). If you are using ODBC or JDBC, the wrong credentials are passed.
工作组的问题是没有像Active Directory (AD)这样的公共访问控制列表。如果您正在使用ODBC或JDBC，则传递错误的凭据。
Even if you create a local windows account (LWA) on the machine (SE) that has SQL Express installed (SE\LWA), the credentials that will be passed from your client machine (CM) will be CM\LWA.
即使您在已经安装了SQL Express (SE\LWA)的机器上创建了一个本地windows帐户(LWA)，您的客户端机器(CM)所传递的凭据也将是CM\LWA。
#5
3
I've had this same issue when using DNS aliases and hosts files to connect to a machine using a different domain name.
当使用DNS别名和主机文件连接到使用不同域名的机器时，我也遇到过同样的问题。
Say you have a SQL server called sql1 on mydomain.com - which is an Active Directory domain - and you also have a DNS zone for mydomain.net, and - for consistency - you set up a DNS alias (CNAME) record for database.mydomain.net --> sql1.mydomain.com
假设您在dommyain.com上有一个名为sql1的SQL服务器—它是一个活动目录域—您还为mydomain.net设置了一个DNS区域，为了一致性，您为database.mydomain.net—> sql1.mydomain.com设置了一个DNS别名(CNAME)记录
You'll be able to connect to sql1.mydomain.com using Windows integrated security, but won't be able to connect to database.mydomain.net even though it's the same server because the domain name doesn't match your AD domain.
您将能够使用Windows集成安全性连接到sql1.mydomain.com，但不能连接到database.mydomain.net，即使它是同一个服务器，因为域名与您的AD域不匹配。
#6
2
This error message can also occur if the account you are using to access the SQL server is locked out by the domain.
如果您用于访问SQL服务器的帐户被域锁定，也可能出现此错误消息。
#7
0
In my case the Aliases within SQL Native Client 11.0 Configuration were pointing to invalid server/IP. Once updated it worked correctly.
在我的例子中，SQL本地客户端11.0配置中的别名指向无效的服务器/IP。一旦更新，它就能正常工作。
To check: 1. Start "SQL Server Configuration Manager" 2. Navigate to "SQL Native Client 11.0 Configuration" and then "Aliases" 3. Ensure "Alias Name" and "Server" match correctly for TCP/IP
检查:1。启动“SQL Server Configuration Manager”2。导航到“SQL本机客户端11.0配置”，然后“别名”3。确保TCP/IP的“别名”和“服务器”匹配正确