将 SQL Server 2008 连接到 Java：因用户错误登录失败

Connecting SQL Server 2008 to Java: Login failed for user error

提问by jhaip

I am trying to connect my Java code to a Microsoft SQL Server 2008 R2 Express database. I have downloaded the Microsoft SQL Server JDBC Driver 3.0 and added the sqljdbc4.jar to my classpath. I am using Netbeans and have included the sqljdbc4.jar in my project also.

我正在尝试将我的 Java 代码连接到 Microsoft SQL Server 2008 R2 Express 数据库。我已经下载了 Microsoft SQL Server JDBC Driver 3.0 并将 sqljdbc4.jar 添加到我的类路径中。我正在使用 Netbeans，并且在我的项目中也包含了 sqljdbc4.jar。

I created a database in the SQL Server Management Studio called TestDB1 and added some columns and values that I will use for testing. I changed from Windows Authentication Mode by right clicking on the server JACOB=PC\SQLEXPRESS->Properties->Secuity and changing from Windows Authentication Mode to SQL Server and Windows Authentication Mode.

我在 SQL Server Management Studio 中创建了一个名为 TestDB1 的数据库，并添加了一些用于测试的列和值。我通过右键单击服务器 JACOB=PC\SQLEXPRESS->Properties->Secuity 并从 Windows 身份验证模式更改为 SQL Server 和 Windows 身份验证模式来从 Windows 身份验证模式更改。

I then created a new login by right clicking on the Login folder in the window explorer under JACOB-PC/SQLEXPRESS->Secuity Folder->Logins Folder and added a new login. I gave it the name jhaip2, switched to SQL Server authentication and the set the password to jacob. Enforce password policy and enforce password expiration are unchecked. The default database is set to TestDB1. Then under TestDB1->Secuity->Users->jhaip2->Database role membership I set jhaip2 to db_owner (I couldn't log in to the database in the management studio without doing this, probably not the right thing to do?). I then restarted the server.

然后，我通过右键单击 JACOB-PC/SQLEXPRESS->Secuity 文件夹->Logins 文件夹下的窗口资源管理器中的 Login 文件夹创建了一个新登录名，并添加了一个新登录名。我给它取名为 jhaip2，切换到 SQL Server 身份验证并将密码设置为 jacob。未选中强制执行密码策略和强制密码过期。默认数据库设置为 TestDB1。然后在 TestDB1->Secuity->Users->jhaip2->Database role members 下，我将 jhaip2 设置为 db_owner（如果不这样做，我无法登录管理工作室中的数据库，这可能不是正确的做法？）。然后我重新启动了服务器。

Now for my java code, it is basically a direct copy of the JDBC Driver 3.0 Sample code except without windows authentication.

现在对于我的 java 代码，除了没有 Windows 身份验证外，它基本上是 JDBC Driver 3.0 示例代码的直接副本。

package databasetest1;

import java.sql.*;

public class connectURL {

   public static void main(String[] args) {

      // Create a variable for the connection string.
      String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=TestDB1;";

      // Declare the JDBC objects.
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;

      try {
         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         System.out.println("Driver okay");
         con = DriverManager.getConnection(connectionUrl,"jhaip2","jacob");
         System.out.println("Connection Made");

      }

      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
      finally {
         if (rs != null) try { rs.close(); } catch(Exception e) {}
         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
         if (con != null) try { con.close(); } catch(Exception e) {}
      }
   }
}

When I run, it prints out "Driver okay" so I am assuming my driver is set up correctly. Then it prints the error:

当我运行时，它会打印出“驱动程序正常”，所以我假设我的驱动程序设置正确。然后它打印错误：

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'jhaip2'.

It does not matter what username I use, it always fails. I have a feeling I am setting up the user wrong. If anyone could give me some help on how to properly set up a user or any guidance in how to simply connect to a SQL Server database in Java, I would appreciate it.

我使用什么用户名并不重要，它总是失败。我有一种感觉，我设置的用户是错误的。如果有人能给我一些有关如何正确设置用户的帮助或有关如何简单地连接到 Java 中的 SQL Server 数据库的任何指导，我将不胜感激。

采纳答案by jhaip

When I looked in the SQL Server log files, it was saying that I couldn't log in because SQL Server was in Windows Authentication mode, even though in the program it was set to Mixed Authentication Mode.

当我查看 SQL Server 日志文件时，它说我无法登录，因为 SQL Server 处于 Windows 身份验证模式，即使在程序中它设置为混合身份验证模式。

When installing, I had set it up in Windows Authentication mode but changing the settings in the program would not change it from Windows Authentication mode.

安装时，我已将其设置为 Windows 身份验证模式，但更改程序中的设置不会将其从 Windows 身份验证模式更改。

I removed SQL Server 2008 and all related programs from my computer and installed a fresh copy, this time with SQL Server Authentication. Everything works correctly now. I don't know why SQL Server had a problem with changing the authentication mode, but it works now so I am happy.

我从我的计算机中删除了 SQL Server 2008 和所有相关程序，并安装了一个全新的副本，这次是使用 SQL Server 身份验证。现在一切正常。我不知道为什么 SQL Server 在更改身份验证模式时会出现问题，但它现在可以工作了，所以我很高兴。

回答by obscuredlogic

Would this other question possibly be the same issue: MS SQL Server 2005 Express x86 - its port is unreachable - help.

这个其他问题是否可能是同一个问题：MS SQL Server 2005 Express x86 - 其端口无法访问 - 帮助。

You have to make sure that SQL Server is listening on the correct port before you can connect via a TCP connection using JDBC.

您必须确保 SQL Server 正在侦听正确的端口，然后才能使用 JDBC 通过 TCP 连接进行连接。

回答by Mubasher

1. run this query by selecting your database

    CREATE LOGIN aaron792 WITH PASSWORD='12345'
    USE BudgetAuthorization
    CREATE USER aaron792
   

   i use aaron792 you should use yours

2. In sql server management studio go to object explorer and right click on (local)(sql server xxx) then go to properties it will open server properties and then go to security and change server authentication mode from windows only to sql server or windows (2nd option)

3. in server properties , go to permissions and select newly created user from Logins or rules list then below it check all options for grant in explicit rule

4. open SQL server configuration manager and -> sql server services at top row SQL server right click and open properties then change built in account to Local System

1. 通过选择您的数据库运行此查询

    CREATE LOGIN aaron792 WITH PASSWORD='12345'
    USE BudgetAuthorization
    CREATE USER aaron792
   

   我用 aaron792 你应该用你的

2. 在 sql server management studio 中，转到对象资源管理器并右键单击（本地）（sql server xxx），然后转到属性，它将打开服务器属性，然后转到安全性并将服务器身份验证模式从仅 Windows 更改为 sql server 或 windows（第二选项）

3. 在服务器属性中，转到权限并从登录名或规则列表中选择新创建的用户，然后在其下方检查显式规则中授予的所有选项

4. 打开 SQL Server 配置管理器和 -> SQL Server 顶行的 sql server services 右键单击​​并打开属性，然后将内置帐户更改为本地系统

then restart every thing and make sure it is running. then try your code

然后重新启动每件事并确保它正在运行。然后试试你的代码

回答by Alexander Poleschuk

I ran into the same issue. Thank you, jhaip, for pointing me to the right direction: SQL Server was in Windows Authentication mode.

我遇到了同样的问题。谢谢 jhaip，为我指明了正确的方向：SQL Server 处于 Windows 身份验证模式。

I'd like only to add that it is not necessary to reinstall the SQL server and all related programs. What's just needed is to change authentication mode. See this answer for more details: An attempt to login using SQL authentication failed

我只想补充一点，没有必要重新安装 SQL 服务器和所有相关程序。只需要更改身份验证模式。有关更多详细信息，请参阅此答案：尝试使用 SQL 身份验证登录失败

回答by Enoch

I know this has already being answered but would like to give a little twist to it without having to reinstallthe whole SQLSERVERRDBMS. The concept is to change the properties of the sqlserver instance you are trying to connect to.

我知道这已经得到了回答，但我想在不必重新安装整个SQLSERVERRDBMS 的情况下对其进行一些改动。这个概念是更改您尝试连接的 sqlserver 实例的属性。

1. Step one. Open SQL Management Studioand select the sql server instance from the object explorer.

   • Right click on the instance and select properties from the popup menu which will open a Server Property Window.
   • select Security option which is located at the right side of the page. Then change server authentication from Windows Authentication modeto SQL SERVER and Windows Authentication mode (mixed mode).
   • Select Permissionsoption afterwards. Then with Permissions selected, select the Login Account you would like to grant permission to. (Login Account should be created before this, check this tutorial out http://www.blackthornesw.com/robo/projects/blackthornepro/HOWTO_-_Creating_a_SQL_Server_Database_Login_Account.htm. The edition might be different from what you have but the concept or workflow remains the same.). Under the permissions, select the Explicittab, and under Grant, select all the options.
   • Click OK.

2. Step Two: to configure the RDBMS to use the internally created Login Accounts. Therefore:

   • Open/Start SQLSERVER configuration Manager.
   • Select SQL SERVER Servicesfrom the left pane.
   • With that selected, move to the right pane and right click on SQL Server (MSSQLSERVER)option and then select properties from the popup menu.
   • SQL Server (MSSQLSERVER) properties window appears, make sure Log Ontab is selected, then select the radio button named Built-in Accountunder Log on as:Option. Then select from the combo box, Local System.
   • Click OK. By clicking on ok, you will be asked to restart the instance, go ahead to restart it. If restart is not asked, then move to step three below.

3. Step Three: to restart the SQLSERVER RDBMS.

   • Go to Control Panel> Administrative Tools> Component Services.
   • Select Services from the left pane
   • Then select SQL Server (MSSQLSERVER)from the right pane, the click restart which is just a little to the top right side.

1. 第一步。SQL Management Studio从对象资源管理器中打开并选择 sql server 实例。

   • 右键单击实例并从弹出菜单中选择属性，这将打开服务器属性窗口。
   • 选择位于页面右侧的安全选项。然后将服务器身份验证从 更改Windows Authentication mode为SQL SERVER and Windows Authentication mode (mixed mode)。
   • Permissions之后选择选项。然后选择权限，选择您要授予权限的登录帐户。（应在此之前创建登录帐户，请查看本教程http://www.blackthornesw.com/robo/projects/blackthornepro/HOWTO_-_Creating_a_SQL_Server_Database_Login_Account.htm。版本可能与您拥有的版本不同，但概念或工作流程仍然存在相同。）。在权限下，选择Explicit选项卡，然后在 下Grant，选择所有选项。
   • 单击确定。

2. 第二步：配置 RDBMS 以使用内部创建的登录帐户。所以：

   • 打开/启动SQLSERVER configuration Manager。
   • SQL SERVER Services从左窗格中选择。
   • 选中后，移至右窗格并右键单击SQL Server (MSSQLSERVER)选项，然后从弹出菜单中选择属性。
   • SQL服务器（MSSQLSERVER）出现的属性窗口中，确保Log On标签被选中，然后选择名为单选按钮Built-in Account下的Log on as:选项。然后从组合框中选择Local System。
   • 单击确定。单击“确定”后，系统会要求您重新启动实例，请继续重新启动它。如果未要求重新启动，则转到下面的第三步。

3. 第三步：重新启动SQLSERVER RDBMS。

   • 转到Control Panel> Administrative Tools> Component Services。
   • 从左侧窗格中选择服务
   • 然后SQL Server (MSSQLSERVER)从右侧窗格中选择，单击右上角的重新启动。