Cannot login to SQL Server 2008, in Windows 7 x64

Question

Alright wasted hours and hours trying to get MSSQL Server 2008 to login via SQL Server Management Studio. It works fine in Windows XP computer, but in Windows 7 it does not work.

Steps done:

1. Install MSSQL, add logged-in administrator user in installation.
2. type the correct hostname in the "login window" of SQL Server Management Studio.
3. Error 18456 is returned with Windows authentication.
4. SQL authentication, with username and password matching my windows account, does not work.
5. Created a new user called it 'SQLServer', set the SQL account in 'Computer > manage > SQL Server > Properties' to 'use this account'. Restarted Service.
6. Added SQLServer to Administrator group, and all the 'MSSQL' groups as well.
7. Tried logging in with .\SQLServer and password that I set for SQL authentication method, no luck.
8. Tried logging in with SQLServer and password, no luck either.

I also tried installing "Service Pack 1 of SQL Server 2008", but no luck.

Essentially, I cannot figure out how to login to my own local MSSQL server.

Solution 1

Follow the steps in Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out:

• Start the instance of SQL Server in single-user mode by using either the -m or -f options.
• Start command prompt as local administrator
• Connect to the server: sqlcmd -E -S .

• Add yourself (computername\username) to the sysadmin SQL group:

  create login [computername\username] from windows;
exec sp_addsrvrolemember 'computername\username', 'sysadmin';

• Restart the server in normal mode

And next time pay attention to the options you click during Setup.

Solution 2

Just to clear something up. The service account you keep changing is the account that SQL Server's services are running under on the computer and has nothing o do with what accounts are allowed to access it (this would be like changing the MySQL service to a root account and then a non-root account, so not an MS issue). As this has nothing to do with your ability to login (unless you have disabled the service somehow by giving it an account with limited rights), I would verify it is working and then leave that dialog alone.

Solution 3

I would say take a look at this post http://sqlblog.com/blogs/andy_leonard/archive/2008/07/15/installing-sql-server-2008-rc0.aspx

and pay attention to this...notice the add current user and add... options??

Also notice mixed mode...it is all there

You can also change the account after the fact in the service itself

Comments

[Dexter]

So you recommend reinstalling? Because I keep changing the account in the service itself, and it does not work. I added a new user 'SQLServer', made it administrator, added it to all MSSQL groups. Then I click "use this account" enter SQLServer and my pass. Then it tells me to restart service. Then it says login as ".\SQLServer" And I do using "SQL Authentication" doesn't work. Then I try windows authentication, and that doesn't work. I wouldn't post this post if I hadn't tried for several hours.

[SQLMenace]

is this a named instance?..run SQL Server Configuration Manager and change the account there then make sure it is running

[Dexter]

Yes it's running. If I entered the wrong "server name" it gives a different error and takes a long time to respond. But it responds quick and says 18456 when I try on the correct hostname. MSSQL Server service is running. And I restart it every time to try tons of different accounts. The same settings works fine in Windows XP.

[Dexter]

I reinstalled SQL Server, at the end it 'failed because of access denied'. But it still installed the service and it looks like it works. I followed the steps on your website. Windows authentication still doesn't work. As you can see it is not my fault.

[Dexter]

No, I did pay attention to options I clicked in setup, I told you I chose Local system. Then next, I reinstalled again, and chose my test SQLServer Account with the correct password, still didn't work.

[Remus Rusanu]

Local System perhaps you choose as the service account. Denis points in his picture to the step where you choose the administrators. If you choose LocalSystem as administrator, then no wonder you're locked out.

[Dexter]

For 'specify SQL administrators' I definitely selected my user and the 'sqlserver' user.

[Dexter]

Oh ok. Then I don't understand how else to login to it. In MySQL, usually there was an easy way to change the root login of the server. I'm going to try what Remus attempted.

[Dexter]

Also I did try your steps, and it didn't work, when I try to connect to sqlcmd. This is what I get: C:\Windows\system32>sqlcmd -E -S . Msg 18456, Level 14, State 1, Server MSSQLhost, Line 1 Login failed for user 'DEX\Dexter'.

[Dexter]

@Remus, Ok I restarted the service in /m single user mode, and I was able to get into sqlcmd ( stackoverflow.com/questions/2833373/… ) . However, after I did alter login sa. I am still unable to connect to SQL Management Server Studio using Windows authentication OR SQL authentication.

[Dexter]

Hmm. This time I did "create login" and "exec" commands as you mentioned. Then I had to type "go" apparently. Then the windows authentication became working now. I don't understand why the installation didn't do this on its own.

[sfuqua]

As someone who had never used sqlcmd before, it took me a little while to figure out the GO statement (and StackOverflow was hiding @Dexter's comment initially.