Connecting to Server using SQL Server Authentication

Posted on 2007-08-01
Medium Priority
Last Modified: 2008-03-06
How can I set up SQL Server to use SQL Server Authentication and grant one user read only access to all the tables in a database and another user write access to all the tables in the database?
Note: I changed the Authentication in SQL Server 2005 Express from "Windows NT" to "SQL Server" Authentication and added a user to every server role but still get the following error message when I test the connection with that user: "Testing the registered server failed. Verify the server name, login credentials, and database, and then click Test again.  Login failed for user 'db1'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)".   Enabling the sa account and trying to log in under it didn't work either.
Question by:Declan_Basile
  • 2
LVL 27

Expert Comment

ID: 19611139
Do you allow remote connections on the server?

How are you connecting to the server? .NET, SQL Server Management Studio?

LVL 11

Accepted Solution

Goodangel Matope earned 1500 total points
ID: 19611533
If you are using SQL Server authentication, you will need to have a Login (see the security node on your SQL Management Studio )

Only when you have created a valid login will you be able to log in using SQL Server authentication.

Author Comment

ID: 19612181
I allow remote connections, but regardless, I can't login using SQL Server Management Studio on the same computer running the server.  I set up an account "db1" and assigned it to every server role.  Why can I not log in?
LVL 27

Expert Comment

ID: 19612552
What are your SQL Server Configuration Manager: client protocols?

You have performed the usual fix for 18452 - changing it to Mixed Mode. You have stopped and restarted the service (yes?). Maybe SQL Server is not listening to TCP/IP?

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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