Link to home
Start Free TrialLog in
Avatar of mph23
mph23Flag for United States of America

asked on

Cannot login to SQL Server 2005 using SQL authentication mode (mixed mode).

I changed the Server authentication to SQL Server and Windows Authentication mode.

The MS help said I need to restart the service when changing the authentication mode, but when I look on the server under Adminstrative Tools, Services, I don't see any sql server services running.

After changing the authentication, I added a login (sqluser7) with SQL Server authentication and default database db7.  Then I added a user to database db7 (sqluser7) with default schema dbo, owned schemas db_owner and Role Members db_owner.

When I get out of SQL Server Management Studio and try to login using SQL Server Authentication abd sqluser7 I get an error message:

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)

If I try to do the login again, I get a different error:
Cannot connect to xxx.

Login failed for sqluser7. The user is not associated with a trust SQL Server connection. (Microsoft SQL Server, Error:  18452)

What step am I missing?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mph23

ASKER

I already changed the setting but how do I restart the sql server?

Reboot the server, that will do it.
Avatar of mph23

ASKER

I found out from another expert that to restart the server you just need to click the top of the object explorer in SSMSX, right click on the server and click stop, when stopped click start.

No need to reboot the server.
angelIII answered the question, why are you closing and not assigning points to angel?
Avatar of mph23

ASKER

Because angelll's comment was to change the setting to mixed authentication and restart sql server- but I already stated in the first line of my original post that I was on mixed authentication. As stated in the 2nd line of my original post, I knew I had to restart the service but didn't know how.

Angelll repeated what I already knew but didn't explain how to restart sql server. That is what I was asking.

Another expert on another question showed me how to restart the sql server within Sql Server Management Studio without rebooting the entire server. This was the response that actually helped me.

I will reward the points to angelll.
Avatar of mph23

ASKER

Sorry, maybe I wasn't clear in my question. I'm new to sql server so not sure how to do things. Your answer didn't help me because I didn't know how to restart the sql server.

I realize that if I did know how to restart sql server, your response would've been helpful. But as stated in my second paragraph, I didn't know how to restart the service (which I have learned from another expert).

Thanks for your help anyway. I have awarded you the points.
Yeah the same worked for me even.