[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2008-11-17
Medium Priority
Last Modified: 2012-05-05
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?
Question by:mph23
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 375 total points
ID: 22980655
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

Author Comment

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

LVL 17

Expert Comment

ID: 22981876
Reboot the server, that will do it.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 22982033
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.
LVL 17

Expert Comment

ID: 22983863
angelIII answered the question, why are you closing and not assigning points to angel?

Author Comment

ID: 22987308
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.

Author Closing Comment

ID: 31517679
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.

Expert Comment

ID: 23661508
Yeah the same worked for me even.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

831 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