Unable to Connect to Local SQL Server with Windows Authentication using SSMS

Asked 26/1, 2012 at 10:14 Answered 11/6 at 9:16

Solved sql-server database sql-server-2005 ssms windows-authentication

I'm trying to login to my local SQL Server 2005 installed on Windows7 using SSMS using Windows authentication.

I tried using various server names like ., localhost, .\SQL, ANANTH-PC etc.

I get this error when I try . or localhost

enter image description here

And I get this error when I try .\SQL

. enter image description here

I had gone for default instance and Windows authentication when I installed SQL Server.

I've checked the service in the SQL Server Configuration manager and find that MSSQLSERVER is running.

How can I find the server name that I should enter to login to SQL Server? Any help ?

Error is loged as

2012-01-26 15:07:16.02 Logon       Login failed for user 'Ananth-PC\Ananth'. [CLIENT: <local machine>]
2012-01-26 15:08:51.06 Logon       Error: 18456, Severity: 14, State: 11.

Peccary answered 26/1, 2012 at 10:14 Comment(6)

The first error message indicates that your . is correct - just your user doesn't have any permission to log on..... I would try to log on to . with the sa account and the password you gave that account when installing.... the default instance of SQL Server can be accessed via . (or (local)) - if you installed SQL Server Express, the default instance name would be .\SQLEXPRESS (or again: (local)\SQLEXPRESS) – Cara 26/1, 2012 at 10:19

Thanks marc. But I havnt given any password during SQLServer installation as I'd gone for Windows Authentication (not Mixed). So how do I go ahead from here ? – Peccary 26/1, 2012 at 10:22

@Cara - I tried .\SQLEXPRESS as well. Its not working as I dont have SQLEXPRESS – Peccary 26/1, 2012 at 10:29

Under which user account did you do the installation?? Your own user, or did you run as Administrator?? That user account should automatically be granted access to the SQL Server instance, I believe – Cara 26/1, 2012 at 10:38

@Cara . Thanks . But there is just a single user ("Ananth") in my machine. But the error message that I get is "Login failed for Ananth-PC\Ananth. – Peccary 26/1, 2012 at 10:47

@marc_s. Did you mean to try with SQLServerAuthentication with username as "Ananth" ? – Peccary 26/1, 2012 at 10:48

That should help you find reason. Click: "Show technical details" and look at State number.

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

Alexandra answered 26/1, 2012 at 11:26 Comment(3)

Thanks. As per the link, the error is logged as Severity 11. Error Discription is "Valid login but server access failure". – Peccary 26/1, 2012 at 12:46

Hmm. I think about login as admin: ADMIN:INSTANCE. More information here:msdn.microsoft.com/en-us/library/ms178068.aspx Or just try to restart service:) – Alexandra 26/1, 2012 at 13:39

Thanks devarc. The answer guided me to the solution. I ran SSMS as admin and it worked fine. – Peccary 14/2, 2012 at 10:3

Ran SSMS as Administrator and issue was fixed.. Accepting the previous answer as it guided me to this solution

Peccary answered 14/2, 2012 at 10:4 Comment(0)

That should help you find reason. Click: "Show technical details" and look at State number.

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

Alexandra answered 26/1, 2012 at 11:26 Comment(3)

Thanks. As per the link, the error is logged as Severity 11. Error Discription is "Valid login but server access failure". – Peccary 26/1, 2012 at 12:46

Hmm. I think about login as admin: ADMIN:INSTANCE. More information here:msdn.microsoft.com/en-us/library/ms178068.aspx Or just try to restart service:) – Alexandra 26/1, 2012 at 13:39

Thanks devarc. The answer guided me to the solution. I ran SSMS as admin and it worked fine. – Peccary 14/2, 2012 at 10:3

For SQL Server 2019 Express on SSMS, the default format of MSSQL Server Name is:

<Your PC Name>/SQLEXPRESS

This is in my case below:

DESKTOP-HN1U0ME/SQLEXPRESS

For SQL Server 2019 Developer on SSMS, the default format of MSSQL Server Name is:

<Your PC Name>

This is in my case below:

DESKTOP-HN1U0ME

Abessive answered 2/9, 2022 at 8:54 Comment(1)

Thank you so much for this! My problem was I was used to using <Your PC Name>/SQLEXPRESS and when I installed the developer version I was trying to use <Your PC Name>/MSSQLSERVER when I needed to just use <Your PC Name> – Sixfooter 9/12, 2023 at 2:22

Please enable the administrator Account throngh this command secpol.msc and enable admin and log the PC and Login the Administrator account and run SQL Server 2005.

Ammon answered 13/5, 2013 at 7:54 Comment(0)

Give server name as "(LocalDb)\v11.0" by selecting windows authentication. Please see the image.

It worked for me with SQLServer 2014.

Marquismarquisate answered 10/8, 2017 at 7:53 Comment(1)

Even I also was not convinced when I got this solution from some source. But it worked for me wioith MS SQL 2014 . For that you need have installed SQL Server not only server management studio. – Marquismarquisate 10/8, 2017 at 8:16

I reset my local SQL server with the following CMD commands:

sqllocaldb stop mssqllocaldb
sqllocaldb delete mssqllocaldb
sqllocaldb start "MSSQLLocalDB"

Sectarianize answered 11/6 at 9:16 Comment(0)

Recommended topics

Hot tags