Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: SQL Server authentication

    I can't get SQL Server authentication to work! Here's what I'm doing

    1) In the Object Explorer, I right-click on the "Security" tab and choose New > Login.
    2) For "Login name" I enter "testuser"
    3) I choose "SQL Server Authentication" instead of "Windows Authentication"
    4) For the password I enter "test"
    5) I uncheck all 3 checkboxes (Enforce password policy, enforce password expiration, and user must change password at next login)
    6) Hit OK

    7) Right-click on the server in the Object Explorer and choose "disconnect"

    8) Now I try re-connecting by right-clicking the server in the object explorer and choose "Connect"

    9) I set the Authentication to "SQL Serever Authentication" then enter my credentials "testuser" and "test" for password.

    It denies me access!

    Anyone know what I"m doing wrong?

  2. #2
    Join Date
    Jan 2008
    Posts
    186
    Really, what I'm trying to do is be able to connect using .NET's SqlConnection object. I would like to be able to connect with a custom username and password. How can I do this??

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    is your server set to use Mixed Mode Authentication?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jan 2008
    Posts
    186
    Hmmm... I'm really not sure. How can I check that?

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    If I right-click my server in the Object Explorer, and choose Properties, I see a listing on the left-side with things like: General, Memory, Processors, Security, etc.

    Now if I choose security, there is a section called "Server authentication" and I can only choose "Windows Authentication Mode" or "SQL Server and Windows Authentication mode".

    I have the 2nd option (SQL Server and Windows Authentication mode) selected.

  6. #6
    Join Date
    Jan 2008
    Posts
    186
    The actual error I get is: "Cannot open user default database. Login failed." and the error # is 4064

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    Okay, I think I'm getting somewhere now...

    If, in the properties for the user, I choose "master" as the default database, then my login succeeds.

    However, I don't want this user to connect to the "master" database, I want the user to connect to my custom database ("db_test"). And when I set the default database to "db_test" for that user, then the login fails..... Any ideas?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That error message means that your login did successfully connect to the SQL Server, but that it couldn't successfully map to a user in that login's default database.

    Did you possibly set a default database, but forget to create a user for this login in the default database?

    -PatP

  9. #9
    Join Date
    Jan 2008
    Posts
    186
    Ahhhhh okay, I think I got it now

    So, I right-click on a user, then select "Properties" then choose the "User Mapping" section. Then on the right, there's a listing of all the db's on the server. I check off the box corresponding to my db (db_test) and choose "dbo" as the default schema.

    This seems to work!

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •