none
Testing Login Using a Contained Database User Via SSMS RRS feed

All replies

  • You must select the database the user is defined in under the Options key in the Connection dialog.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 11, 2017 9:32 PM
  • Yes sir. I did use the concerned database name (typed in since Browsing will result in the same error message). No luck. Also, I did Connect permissions to the said user to the concerned database. Anything else needs to be done?

    Thanks.

    Victor


    Victor

    Wednesday, October 11, 2017 9:59 PM
  • Hi Victor,

    >>Login failed for user 'test123'. (Microsoft SQL Server, Error: 18456).

    What the error status, could you please use SQL Server error log to find out the complete error message? Also, have you enabled Mixed authentication mode so you can connect via SQL login?

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 12, 2017 2:05 AM
    Moderator
  • Run

    sp_configure 'contained database authentication'

    and check that run_value = 1.

    Check that test123 exists in sys.database_principals. Run

    EXECUTE AS LOGIN = 'test123'

    This should give you an error. Riun

    EXECUTE AS USER = 'test123'
    SELECT SYSTEM_USER
    go
    REVERT

    This should return test123.

    ...and of course, check that you are typing the password correctly!

    (For the latter using SQLCMD from the command-line helps.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 12, 2017 11:11 AM
  • Lin,

    Sorry, could not get back on this topic until now. Anyway, here is the error inside SQL server logs:

    -------------------

    Date  10/28/2017 2:57:47 PM
    Log  SQL Server (Current - 10/28/2017 7:38:00 AM)

    Source  Logon

    Message
    Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'myTestDB'. [CLIENT: xxx.xxx.x.x] 

    -------------------

    Date  10/28/2017 2:57:47 PM
    Log  SQL Server (Current - 10/28/2017 7:38:00 AM)

    Source  Logon

    Message
    Error: 18456, Severity: 14, State: 38.

    As said, I did try using the name of the concerned database under Options-->Connection Parameters section of Connect To Server window of SSMS while using SQL Server Authentication. Yes, the authentication is set to mixed.

    Also, I ran the script shown below with the result. Unfortunately, I do not see Connect permission because, that user (test123) is not supposed to be a SQL server login user. I tried to grant Connect permission, but resulted in the error "Cannot find the login 'test123', because it does not exist or you do not have permission."

    SELECT

    *FROMfn_my_permissions('test123','USER'); 

    GO

    entity_name subentity_name permission_name
    test123                    IMPERSONATE
    test123                    VIEW DEFINITION
    test123                    ALTER
    test123                    CONTROL

    I did give the user db_owner membership as well using the Properties of the said user.

    Thoughts please. Thanks.

    Victor


    Victor

    Saturday, October 28, 2017 8:17 PM
  • As said, I did try using the name of the concerned database under Options-->Connection Parameters section of Connect To Server window of SSMS while using SQL Server Authentication. 

    That should be Options-->Connection Properites (not Connection Parameters).

    Connection Properties dialog


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, October 28, 2017 9:37 PM
    Moderator
  • If it says Login failed for user 'NT AUTHORITY\SYSTEM', this would mean that the login comes from something that runs as a service under LocalSystem. Are you sure that you are looking at the correct entry in the errorlog?

    If you run SELECT * FROM fn_my_permissions('test123', 'USER') you see what permissions you have on that user.

    More meaningful would be to run

    EXECUTE AS USER = 'test123'
    SELECT * FROM fn_my_permissions(NULL, NULL)
    REVERT


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 28, 2017 9:57 PM
  • Erland,

    Thanks for your response. Your query result show nothing meaning, empty result.

    And, I have been trying using Dan Guzman's is showing (writing the concerned db name). No luck so far.

    Victor


    Victor

    Saturday, November 4, 2017 8:33 PM
  • Thanks for your response. Your query result show nothing meaning, empty result.

    Since you don't get an error message, this means that the user exists in the database where you tried this.

    But we still don't know if you are typing the password correctly. Nor do we know what error message you get in the SQL Server error log on the login error. (The one you posted, can reasonably not match your attempt to login with test123.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, November 6, 2017 9:55 AM
  • I know this is definitely a year old thread, and you prolly either figured out how to fix this issue, or just forgot about it, but you have to actually type in the name of the contained database you intend to connect to in 'Connect to database'. The <Browse server..> option sometimes won't work for no apparent reasons, and it'll always throw the error "18456" even if the login credentials you entered are valid.


    • Edited by Hirayoki22 Friday, September 28, 2018 11:52 PM
    Friday, September 28, 2018 11:51 PM
  • Great solution Hirayoki22, it worked 4 me.
    • Edited by jordanAz Tuesday, December 4, 2018 11:31 AM
    • Proposed as answer by Jacques Bosch Sunday, January 27, 2019 10:35 AM
    Tuesday, December 4, 2018 11:30 AM
  • Perfect 



    mohamed

    Thursday, December 20, 2018 6:34 AM
  • Hirayoki22 - thanks

    Your solution also works when setting up SQL ODBC Driver 17 connections to Azure SQL databases using a contained user SQL login.
    Wednesday, June 5, 2019 3:42 PM