none
Cannot connect with created new login

    Question

  • Hello,

    i have installed SQL Server 2008 R2. I don't have experience as administrator only as user.

    Administrator is set to "Current domain/user" with Windows authentication. I connect fine with this user to server.

    Then i need to create a user. Without domain name. For example "User1" with SQL Server authentication. I set a password. Login is created.

    But when i try to connect to server with SQL Server authentication and type Login and password i get error "Login failed for user "User1"" Error code 18456.

    Maybe this problem is caused by i have sysadmin set to connect to server with Windows authentication?

    Friday, September 17, 2010 7:36 AM

Answers

  • To login to the instance using SQL Login, you need to change the Authentication mode to SQL Authentication (Mixed-mode Authentication).

    1. Log on to the SQL Server, using Windows Authentication. Right click on the SQL Server Instance name, go to Properties.

    2. Go to the Security tab, select SQL Server and Windows Authenticatin mode under Server Authentication.

    3. Restart the SQL Server instance to make these changes harden.

    Then choose the authencation mode as SQL Server Authentication, and try to login.

    Also, I request to post the detailed error message, along with the state number to get a quick reply.

    You can have a look at the following article by SQL Server MVP Lekss about the details of various states of login failure: http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456

    Hope, this may help

     


    SKG: Please Marked as Answered, if it resolves your issue.
    • Marked as answer by -VB- Friday, September 17, 2010 8:01 AM
    Friday, September 17, 2010 7:52 AM

All replies

  • To login to the instance using SQL Login, you need to change the Authentication mode to SQL Authentication (Mixed-mode Authentication).

    1. Log on to the SQL Server, using Windows Authentication. Right click on the SQL Server Instance name, go to Properties.

    2. Go to the Security tab, select SQL Server and Windows Authenticatin mode under Server Authentication.

    3. Restart the SQL Server instance to make these changes harden.

    Then choose the authencation mode as SQL Server Authentication, and try to login.

    Also, I request to post the detailed error message, along with the state number to get a quick reply.

    You can have a look at the following article by SQL Server MVP Lekss about the details of various states of login failure: http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456

    Hope, this may help

     


    SKG: Please Marked as Answered, if it resolves your issue.
    • Marked as answer by -VB- Friday, September 17, 2010 8:01 AM
    Friday, September 17, 2010 7:52 AM
  • Man, you are amazing :))

    Thanks!!! It helped !!!

    Friday, September 17, 2010 8:01 AM
  • You are welcome, Happy Learning
    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, September 17, 2010 8:03 AM