Cannot connect 18456 - Workgroup 1

[MattSTech]

Good day,

I am near my breaking point here.
I installed MSSQL Server 2008 Enterprise Evaluation on my "server" yesterday. Made sure I had get a clean install with no issues. The "server" is a Win XP SP3 machine not connected to a domain.
All services run fine on the server side. I enabled TCP/IP protocol in the configuration manager and allowed sqlsrvr.exe in the windows firewall.
...two days later...
I installed sql management studio 2008 express on another PC to begin tinkering with the connection and several million attempts later I am nowhere. I have tried so many things to simply get a connection. I am now to the point where I can see the server
telnet myserver 1433 in a command line gives me a blank screen so I think I am good there.
Now I just need to get past the login error 18456 login failed.
What am I doing wrong. Please help if you can, my kids are wondering why their father is bursting into tears at random moments of the day. If I can get past this and begin coding my software I will be back to normal. This database admin stuff is destroying me.

 

[mrdenny]

What are the words that go with the error message? Are you trying to log into the SQL Server with Windows Auth or SQL Auth?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[MattSTech]

When it is configured without TCP/IP enabled, it sits and thinks a bit then the message is

Cannot connect to MyServer The client was unable to establish a connection because of an error during connection initialization process before login.  Possible Causes are the following...

When I enable TCP/IP:

Cannot connect to MyServer - Additional Information->Login Failed for User 'Myserver/User'. (Microsoft SQL Server, Error: 18456)

I am sure this is a permissions thing, but I just don't have enough experience with SQL Server to be any good at troubleshooting. Thank you so much for the help.

 

[mrdenny]

When you installed SQL Server did you tell it what accounts you wanted to have administrative rights? Did you set it up for Mixed Mode, or Windows Only Authentication?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[MattSTech]

I set it up in mixed mode per a reply of yours to a post explaining that in a workgroup setting it was more appropriate due to the user changing passwords on different machines.
I did not give anyone admin rights during setup (It never asked???) If you could tell me how to do this I would appreciate it. I haven't found any method of adding roles that works. The only options I have are db specific and I can't simply add a username without it saying "object not found"
I apologize, but you might have to be fairly remedial in your response. This is the first install of SQL I have ever done.
Thanks again. Matt

 

[mrdenny]

When going through the install on the screen where you set the Authentication mode to either Windows or Mixed Mode, below that you specify which accounts will have Administrative rights on the machine.

As you used Mixed mode you can login with the sa account using the password you specified during the install, then create new Logins (under Security > Login in the object explorer) and grant those logins the rights that they need.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[MattSTech]

Woohoo!!! I can connect!!!
Ok, I am starting to see the bigger picture here. I see the logins and server roles. Perhaps I needed to dig more before I posted...
I look at the login I used when I installed. When I open the properties of the login under the general tab, the users I set up at installation are all grayed out. The only one with active controls is the SA login. Why would this be?

Thanks so much for the help! Now it is back to coding land where I can sleep well at night...

 

[mrdenny]

You can't set those settings for a Windows account, as those settings are controlled by Windows not SQL Server.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[MattSTech]

Slightly off topic... Since I am enabling TCP/IP and telling Windows Firewall to allow the exception of sqlserver.exe. Should I be uber concerned that my server could be at risk? After a night of coding, I disable TCP/IP in the configuration console. Should I be doing anything else? Is this even necessary?

 

[mrdenny]

Are you connecting to the server over the Internet, or over the company/home network only?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[MattSTech]

I will be using the server on a home network only.

 

[mrdenny]

If you have a router at home you are fine. Nothing to worry about. If your computer is on the public internet directly then go get a router. Otherwise use the Windows firewall to block access from the Internet.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[MattSTech]

I have a router. Thanks again for the info. I will be back to learn more as time allows.
Matt

 

[mrdenny]

No problem. That's why we are all here.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog