错误：使用SqlCredential对象登录用户'domain \ username'失败

I am using below mentioned code when I tried to connect with SQL server. I am geeting Login failed for user 'domain\username'. I have checked that the user having sysadmin permission in SQL Server & I am using SQL server Express edition.

当我尝试连接SQL服务器时,我正在使用下面提到的代码。我正在为用户'domain \ username'登录失败。我已经检查过用户在SQL Server中具有sysadmin权限并且我正在使用SQL Server Express版本。

connection string I used for this "Initial Catalog=Employee;Server=serverName"

我用于此“初始目录=员工;服务器= serverName”的连接字符串

 public static bool connectSqlClient(string connecton)
     {
         bool isConnect = false;
         try
         {
             string username = @"domain\username";
             string initString = "abcpassowrd";
             // Instantiate the secure string.
             SecureString testString = new SecureString();

             // Use the AppendChar method to add each char value to the secure string. 
             foreach (char ch in initString)
                 testString.AppendChar(ch);

             testString.MakeReadOnly();
             SqlCredential cred = new SqlCredential(username, testString);
             SqlConnection conn = new SqlConnection(connecton, cred);
             conn.Open();
             isConnect = true;
         }
         catch (Exception)
         {
             throw;
         }

         return isConnect;
     }

Let me know if I missed something.

如果我错过了什么,请告诉我。

3 个解决方案

#1

Typically, when you add a Login to Sql Server, there are 2 modes.

通常,当您向Sql Server添加登录时,有2种模式。

Sql Server Authentication (which is the "old school" user-name and pwd scenario)

Sql Server身份验证(这是“旧学校”用户名和密码子方案)

and

"Windows Authentication". which is where you find a user (domain\username) on your network and add the login. THIS SCENARIO DOES NOT REQUIRE A PASSWORD TO BE SET/SENT.

“Windows身份验证”。您可以在网络上找到用户(域\用户名)并添加登录信息。这个场景不需要设置/发送密码。

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

That is a typical "Windows Authentication" connection string. You don't set the username/pwd, because the IIDentity is "picked up" from who/whatever is logged into or whose credentials the application is running.

这是典型的“Windows身份验证”连接字符串。您没有设置用户名/密码,因为IIDentity是从登录的人/任何人或者运行应用程序的凭据中“拾取”的。

I ~think you want to use Windows-Authentication (since you mention 'domain/username')....but you're setting the pwd as if you were using Sql-Server-Authentication. Thus "mixing" the 2 models.

我认为你想要使用Windows身份验证(因为你提到'域名/用户名')....但你正在设置pwd,就像你使用Sql-Server-Authentication一样。因此“混合”了2个模型。

as mkross mentions, just because you add the LOGIN, you still need to "link in" the database itself. If you go to the Security/Logins/ (properties) and go the "User Mapping" selection, you can "Map" to the database, and select a role_membership like "db_datareader" as a low rights role.

正如mkross提到的那样,仅仅因为你添加了LOGIN,你仍然需要“链接”数据库本身。如果转到Security / Logins /(属性)并选择“User Mapping”选项,则可以“映射”到数据库,并选择role_membership,如“db_datareader”作为低权限角色。

APPEND:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcredential%28v=vs.110%29.aspx

 SqlCredential provides a more secure way to specify the password for a login attempt using SQL Server Authentication.

SqlCredential is comprised of a user id and a password that will be used for SQL Server Authentication.

So yeah, that's for Sql-Server-Authentication. Not Windows-Authentication.

所以是的,这是针对Sql-Server-Authentication的。不是Windows身份验证。

you probably just need

你可能只需要

string myConnectionString = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;"

#2

While you may have created the user did you add that particular user to your actual database?

虽然您可能已创建用户,但您是否已将该特定用户添加到实际数据库中?

This tripped me up for a while after I inherited a demo that already had the user created. When I backed it up and restored it the user was not included and needed to be added back in.

在我继承了已经创建用户的演示之后,这让我失去了一段时间。当我备份并恢复它时,用户不包括在内,需要重新添加。

Here you can see the folder Security with Logins as a sub folder: http://i.stack.imgur.com/oiHrb.png

在这里,您可以看到Security with Logins文件夹作为子文件夹:http://i.stack.imgur.com/oiHrb.png

Add the user here first.

首先在此处添加用户。

Once the login is created you then need to expand your actual database itself and add the login there as well.

创建登录后,您需要扩展实际的数据库本身并在那里添加登录。

#3

After creating the account MAKE SURE TO RESTART THE SERVER INSTANCE!!

创建帐户后确保重新启动服务器实例!

I have come across this problem so many times. You can do this either in the management studio by right clicking the server instance and choosing restart.

我已经多次遇到过这个问题了。您可以在管理工作室中通过右键单击服务器实例并选择重新启动来执行此操作。

or you can do it through the SQL Server Configuration Manager. Other than that you code looks fine.

或者您可以通过SQL Server配置管理器执行此操作。除此之外你的代码看起来很好。