Solved

Problems connecting to SQL Server 2005 after clean install...???

Posted on 2007-03-25
14
10,535 Views
Last Modified: 2008-01-09
I've just installed SQL Server 2005 and I'm trying to get connected to it so I can play with creating/editing databases.  I open up the SQL Server Management Studio but take a look at what I get: http://angelleye.sytes.net/temp/SQLServer.jpg.  As you can see, the username and password fields are both greyed out and I cannot edit them.  The user name field does have COMPUTERNAME\User Name but the password field is blank.  Then, when I try and connect I get the following error:

TITLE: Connect to Server
------------------------------

Cannot connect to OFFICESERVER.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'OFFICESERVER\Drew Angell'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Of course the help link doesn't give me any information.  So, any information I can get on how to fix this would be greatly appreciated.  Thanks!
0
Comment
Question by:Andrew Angell
  • 8
  • 5
14 Comments
 
LVL 39

Expert Comment

by:appari
ID: 18790334
try changing the authentication to SQLServer authentication, you can be able to enter username and password.
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18790453
Ok, I tried that and I used my Windows login information.  With that I get the following error:

TITLE: Connect to Server
------------------------------

Cannot connect to OFFICESERVER.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

So then I just literally hit the connect button again and now I get:

TITLE: Connect to Server
------------------------------

Cannot connect to OFFICESERVER.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'Drew Angell'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------



So now I have a few questions.  I read in a help file somewhere that windows auth. is more secure and should be used whenever possible.  Do I have to be on a domain for that to work or something?  Why would it not be possible in my situation?  Also, how do I setup my user as a trusted SQL Server user so I can connect using SQL auth?  

0
 
LVL 8

Expert Comment

by:Julianva
ID: 18792485
The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

the above is windows  Authentication error.

You dont need to be on the Domain.
You can create a local system account on the local machine and use that to login in as a windows account.
first create a local system account

then create a login with the same system account.

when you start windows with the login you created then sql server be able to auth you to the account you created

instead of using named pipes as your protocol  use tcp/ip with the windows account.



0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18795082
Ok, actually, I've started this stuff over from scratch.  I re-installed Windows Server and went ahead and implemeneted Active Directory just cuz I wanted to play with that anyway.  I'm gonna be installing SQL Server again tonight.  I'll update ya when I get there.
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18797846
Ok, now I've installed SQL Server on a domain controller machine and selected to use a domain user during install for authentication.  I also installed SP2 (which I'm 99.9% sure I did the first time too because I have to for it to work on Server Longhorn).  Upon installing SP2 it gave me some information about it does not give my user admin rights to SQL server by default and I had to assign groups to the user.  I added both groups that it allowed, which were SysAdmin and one other, but I can't remember what it was called now.

Now when I try and connect i'm getting:

TITLE: Connect to Server
------------------------------

Cannot connect to OFFICESERVER.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'AD\Drew Angell'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Message ID: 18456
LinkID: 20476

Where do I create logins for SQL Server?  You mentioned creating a mataching one but I can't even get logged into SQL server..??
0
 
LVL 8

Accepted Solution

by:
Julianva earned 500 total points
ID: 18798173
you configured sql server to use both windows and sql authentication. when you installed sql 2005 it did ask for an SA password

you must first login with the sa account create the windows login l in management studio.
you will see
security tab - expand
logins - right click - new login - search - it will find the domains - then choose your windows account.

then close management studio - open again then login in with windows auth - make sure that you are using the correct instance name of sql server that you installed.





0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18798244
Ok, now I selected SQL Auth and put in SysAdmin for the un and the password I set.  Now I'm getting the following error:

TITLE: Connect to Server
------------------------------

Cannot connect to OFFICESERVER.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Message ID: 233
Link ID: 20476

I thought this was suppose to easier than this..??  :)
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 11

Author Comment

by:Andrew Angell
ID: 18798246
I'm actually getting ready to wipe this out yet again, because I just realized that Exchange Server 2007 is only available for 64 bit version of Windows.  I'll come back when I get to this point again.
0
 
LVL 8

Expert Comment

by:Julianva
ID: 18798282
username = sa
password = password you set
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18798347
that gave me the same error it did when i used SysAdmin
0
 
LVL 8

Expert Comment

by:Julianva
ID: 18798401
open sql configuration manager -
click on protocols for sql server - make sure that
shared memory
tcp/ip
named pipes
are all enabled

0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18798443
Ok, I enabled everything in there and then restarted the SQL Server service.  I then tried to login using sa/password and I get the same error:

TITLE: Connect to Server
------------------------------

Cannot connect to OFFICESERVER.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Message ID: 233
LinkID: 20476

:(
0
 
LVL 8

Expert Comment

by:Julianva
ID: 18799530
what is the instance name , did you configure it as default instance or a named instance.
if you installed as a named instance then please make sure you are using the correct instance name.

you can find the instance name in the control panel - services - find mssql service.


0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 18801181
I used default.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now