3

I am using sql server 2005. TCP/IP is enabled. windows authentication is being used. while trying to connect the server with the Database Engine as server type, I am getting error message "Login failed for user 'name-pc\account 2'(Microsoft SQL Server, Error:18456)" .

user3422209
  • 195
  • 3
  • 17

2 Answers2

4

You may be missing this setting. SQL Server authentication mode is not enabled on my machine by default. (Remember to re-start the SQL server after this change)

enter image description here

Joe Lau
  • 439
  • 5
  • 10
1

This error means that given windows user 'name-pc\account 2' does not have access to given instance. You can login with 'sa'(if it is enabled otherwise login with other sysadmin account) and check if 'name-pc\account 2' is exists and enabled.

You can also read more about this error and gain more information HERE

Please read the comments too, some important tips and points reached in comments

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
  • Going along with reading the documentation above: I know you said TCP/IP is enabled and you are using Windows Authentication--please make sure you also have the option "Allow Remote Connections" selected. I'm assuming you are still connecting to a server that is not on your local box, hence requiring this option be checked off. So long a you personally have access, log in with the main admin account and check the user you are having issues with settings. – Fernando Rodriguez Jun 11 '16 at 07:44
  • May I know where can I see this option "Allow Remote Connection" – user3422209 Jun 11 '16 at 08:04
  • @user3422209 I do not think this error is because of remote connection, because in taht case you would get error:40, not this error. – Vahid Farahmandian Jun 11 '16 at 08:59
  • @user3422209 would you please tell me that you are going to login to a instance in your operating system or you are going to connect to a remote instance? if this is a remote login, does 'name-pc\account 2' a user account in your operating system or in remote operating system? – Vahid Farahmandian Jun 11 '16 at 09:01
  • in my operating system. i am using my laptop with windows 7 os. i am trying to connect sql server in it – user3422209 Jun 11 '16 at 09:38
  • @user3422209 I found this video, which is illustrating your problem and try to solve it:http://itproguru.com/expert/2014/09/how-to-fix-login-failed-for-user-microsoft-sql-server-error-18456-step-by-step-add-sql-administrator-to-sql-management-studio/ – Vahid Farahmandian Jun 11 '16 at 11:00
  • As shown in the video, there is nothing in my object explorer tab. in the video there is already login folder in the object explorer – user3422209 Jun 11 '16 at 11:23
  • I am having object explorer. Can connect * analysis services, * integration services, *reporting services, * SQL server mobile services but not database engine. – user3422209 Jun 11 '16 at 11:44
  • @user3422209 Is the DB service is up and running?(Services/SQL Server[your instance name]) – Vahid Farahmandian Jun 11 '16 at 11:46
  • No. When I right click and try to start it is giving error – user3422209 Jun 11 '16 at 11:52
  • @user3422209 ok, now the problem source found. Please share us your error while starting the service – Vahid Farahmandian Jun 11 '16 at 12:26
  • Error is: The request failed or the srvice did not respond in a timely fashion – user3422209 Jun 11 '16 at 15:10
  • @user3422209 check the windows EventViewer for possible logs. If you see any log related to SQL Server that says the service is unable to start due to logon failure, then check the service's Logon info. read more here:http://blog.sqlauthority.com/2011/03/29/sql-server-fix-error-the-request-failed-or-the-service-did-not-respond-in-timely-fashion-consult-the-event-log-or-other-applicable-error-logs-for-details/ – Vahid Farahmandian Jun 11 '16 at 15:24
  • if all the above discussions not helpful in your case then you need to have a look on this thread: http://stackoverflow.com/questions/1617402/the-request-failed-or-the-service-did-not-respond-in-a-timely-fashion it might be helpful for you. – Jason Clark Jun 13 '16 at 06:57
  • @ Vahid Farahmandian... there are no logs related to SQL server in the windows event viewer – user3422209 Jun 13 '16 at 07:55
  • @user3422209 Go to Services, Choose SQL Server(XXX) or MSSQLSERVER(XXX), right click, choose properties, go to LogOn tab and check the account info provided there. – Vahid Farahmandian Jun 13 '16 at 08:04
  • @user3422209 read more about changing service's account info here:https://technet.microsoft.com/en-us/library/aa197919(v=sql.80).aspx – Vahid Farahmandian Jun 13 '16 at 08:06
  • Built-in account-> local system – user3422209 Jun 13 '16 at 08:25
  • @user3422209 Change it to Network service and check if it helps or not? read more https://msdn.microsoft.com/en-us/library/ms143504.aspx and https://msdn.microsoft.com/en-us/library/jj219062.aspx – Vahid Farahmandian Jun 13 '16 at 09:15
  • @user3422209 take a look at this article too: http://relyonsoft.com/techsupport/?p=127 – Vahid Farahmandian Jun 13 '16 at 09:17
  • As mentioned in the article I have uninstalled the software. now while installing back, it is asking for installation media folder. I am not getting it. – user3422209 Jun 15 '16 at 07:42
  • @user3422209 check if this thread solve it: http://stackoverflow.com/questions/2979425/sql-server-installation-what-is-the-installation-media-folder If this does not help, then try to change your setup file and try again – Vahid Farahmandian Jun 15 '16 at 08:06
  • how to change setup file? – user3422209 Jun 15 '16 at 09:05
  • @user3422209 just simply re-download it – Vahid Farahmandian Jun 15 '16 at 09:16