• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

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?
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your sql server is configured to only allow windows authentication.
change that setting to "mixed authentication", restart the sql server, and the login should work better
mph23Author Commented:
I already changed the setting but how do I restart the sql server?

Reboot the server, that will do it.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

mph23Author Commented:
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?
mph23Author Commented:
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.
mph23Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now