Connecting to Server using SQL Server Authentication

Posted on 2007-08-01
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
    LVL 27

    Expert Comment

    Do you allow remote connections on the server?

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

    LVL 11

    Accepted Solution

    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.
    LVL 1

    Author Comment

    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

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 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