0

I'm trying to connect to a local SQL Server database but it gives me this error:

Login failed for user 'DOMAIN\Username'.

When I open SQL server and look in the Security\Logins folder then I do see the user DOMAIN\Username. This is also the user that I use to login into Windows with.

My connection string looks like this:

<add name="ServerConfiguration"
       connectionString="server=localhost; database=BN_Configuration; 
       Integrated Security=false; User ID=DOMAIN\Username; Password=123456;" 
       providerName="System.Data.SqlClient" />

Anyone any idea why I can't login with these credentials?

--

Note I wish to authenticate with a user that exists in the SQL Server database. So I do NOT want to do Windows authentication with Integrated Security set to false.

Vivendi
  • 20,047
  • 25
  • 121
  • 196
  • Try looking in Windows' Event Log in the Security section on the Database server for a more detailed error message. – maaarghk Jul 16 '14 at 10:25
  • 1
    You cannot use domain users this way. Either create a SQL Server user or `Integrated Security=true, remove `User ID` and `Password` and it will use the user running the process. – DavidG Jul 16 '14 at 10:31
  • @DavidG I just created a new user in SQL Server. But when I try to login with the new credentials then it still says that I can't login. – Vivendi Jul 16 '14 at 10:33
  • 1
    Read this http://stackoverflow.com/questions/750303, then clarify your question – podiluska Jul 16 '14 at 10:35
  • Your edit shows you still don't quite get how SQL security works. When you created the SQL user, did you create it from the domain or did you give it a new username/password? – DavidG Jul 16 '14 at 10:44
  • @DavidG I gave it a new username/password. It's a SQL Server account. – Vivendi Jul 16 '14 at 11:03
  • @Vivendi Then try my answer, it may help. – DavidG Jul 16 '14 at 11:37
  • Try `User ID=Username; Password=123456;` – Jesuraja Jul 16 '14 at 11:45
  • @Jesuraja That's what I have for the new SQL user, but it's not working either: `connectionString="server=localhost; database=BN_Configuration; Integrated Security=false; User ID=ConfigUser; Password=SecretPass1;"` – Vivendi Jul 16 '14 at 12:16

2 Answers2

1

It looks like you are using a Windows credential as SQL Server credential. Try integrated security = true, and not to specify user ID and password.

Xeon
  • 156
  • 8
0

In your connection string Integrated Security=false is saying user ID and password are specified in the connection for an account that exists in SQL Server but is NOT a domain user. When Integrated Security=true, the current Windows account credentials are used for authentication. If it's an application it will use the user who is currently logged in, and for a web application it all depends on how your application pool is set up.

You are mixing up the definitions by saying ``Integrated Security=false` but passing domain credentials which is not possible.

Using a domain account

  1. Set Integrated Security=true
  2. Remove the user id and password sections.
  3. Map the domain account in SQL Server making sure to set Windows authentication
  4. If it's a web application, make sure your application pool is set to run under that domain account.

Using a SQL Server Account

  1. Set Integrated Security=false
  2. Create a SQL login, making sure it uses SQL Server authentication
  3. Set the User ID and Password properties of your connection string to be the same details you created above.

Note: Final point, make sure the user also has access to the database you are connecting to (in your case BN_Configuration).

DavidG
  • 113,891
  • 12
  • 217
  • 223