none
Theory questions RRS feed

  • Question

  • Hello!

    Help me please clarify the following questions:

    1)

     Q1: On which server this WIF service is located - on the SQL Server computer itself?

    2) To determine which tokens are being used by which login the following code may be used:

    SELECT sys.login_token.principal_id as TOKEN_PrincipalID, sys.login_token.name as TOKEN_Name, sys.login_token.type as TOKEN_Type, sys.server_principals.principal_id as SERVERprincipal_PrincipalID,  sys.server_principals.name as SERVERprincipal_NAME, sys.server_principals.type_desc as SERVERprincipal_Type from sys.server_principals
    INNER JOIN sys.login_token
    ON sys.login_token.principal_id = server_principals.principal_id
    go

    ...and if connect to my SQL Server as entadmin it produces the following output:

    - as I'm the only one who has connected to the server this is the expected result, but when I make the second connection under m_firsov login I see that the new session does get created...

    ...but the code still shows only entadmin's tokens:

    Q2 - why (select * from sys.login_token doesn't display any output for m_firsov either)?

    Thank you in advance,
    Michael


    • Edited by MF47 Wednesday, May 29, 2019 11:03 AM typo
    Wednesday, May 29, 2019 11:02 AM

Answers

  • Master is always owned by sid 0x01, which by default goes under the name sa.

    The fact that SQL authentication is not enabled does not preclude the existience of SQL logins or the creation of them. They are only prevented from connecting.


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

    • Marked as answer by MF47 Thursday, May 30, 2019 12:55 PM
    Wednesday, May 29, 2019 9:19 PM

All replies

  •  Q1: On which server this WIF service is located - on the SQL Server computer itself?

    I know little about WIF but my understanding it it's all application layer modules so no software needs to be installed on the database server to support it. 

    The DMV query will return the tokens for the current session, not other sessions. You'll need to run the query in the context of the desired SQL Serve session.


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

    Wednesday, May 29, 2019 12:00 PM
    Moderator
  • "...no software needs to be installed on the database server to support it. " - I thought WIF just asks AD ~"which groups this user belongs to?" but according to this http://aka.ms/D08uj4 WIF service seems do not work with AD so I wonder how in this case Windows Authentication works if no process asks AD about the connecting user...
    Wednesday, May 29, 2019 12:27 PM
  • P.S. ...one more question please: why does the dbo database user in any user-created database is of the WINDOWS_USER type while the dbo for the master database is of the SQL_USER type (SQL Server uses Windows authentication)?

    Regards,
    Michael

    Wednesday, May 29, 2019 2:32 PM
  • Master is always owned by sid 0x01, which by default goes under the name sa.

    The fact that SQL authentication is not enabled does not preclude the existience of SQL logins or the creation of them. They are only prevented from connecting.


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

    • Marked as answer by MF47 Thursday, May 30, 2019 12:55 PM
    Wednesday, May 29, 2019 9:19 PM
  • Thank you for the help, Erland!

    Regards,
    Michaek

    Thursday, May 30, 2019 12:55 PM