Error : Login failed for user 'domain\username' using SqlCredential object

Question

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.

connection string I used for this "Initial Catalog=Employee;Server=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.

granadaCoder · Accepted Answer · 2014-06-19T19:12:22.563

2

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

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

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.

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.

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.

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.

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.

you probably just need

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

edited Jun 19 '14 at 19:12

answered Jun 19 '14 at 18:37

granadaCoder

23,729
8
95
129

Thanks for providing this information. Is there any way to pass the domain user and password? – Basant B. Pandey Jun 20 '14 at 03:32
Are you asking, can I "impersonate" another identity? (other than the identity running the application/process) ? – granadaCoder Jun 20 '14 at 13:02
Yes, You are right. I am asking about the impersonation without using the web.config like we are doing to put the tag in the web.config. can we using the same using code? – Basant B. Pandey Jun 20 '14 at 14:22
You need to impersonate the user in the APPLICATION, aka, your C# application. This isn't trivial. See : http://stackoverflow.com/questions/559719/windows-impersonation-from-c-sharp From that point, the simple "Trusted_Connection" connection string should work. – granadaCoder Jun 20 '14 at 14:26
Your real question is "how do I impersonate a user in a non asp.net c# application". As you've seen, its fairly simple in asp.net with a web.config setting, but not so much in a winforms/console/wpf application. – granadaCoder Jun 20 '14 at 14:28
But "SqlCredential" is for "Sql Authentication", now Windows-Authentication. SqlCredential will not work for what you are attempting. Not rubbing it in, "just sayin'", so you don't spend any more time on it. – granadaCoder Jun 20 '14 at 14:31
Thanks Yes, You are right I checked the same at my end the SqlCredential is used for SQL Authentication. – Basant B. Pandey Jun 20 '14 at 14:37

Caleb Palmquist · Answer 2 · 2014-06-19T17:57:58.033

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

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.

score 0 · Answer 3 · answered Jun 19 '14 at 19:05

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.

enter image description here

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

enter image description here

Error : Login failed for user 'domain\username' using SqlCredential object

3 Answers3