Post reply

Authentication Mode ??

  • October 14, 2013 at 12:42 am

    #299182

    I have a sql server 2008 r2 install with Windows Authentication mode selected.

    However I am seeing SQL accounts connect - how is this possible ??

    Thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • October 14, 2013 at 4:27 am

    #1658224

    Just to verify your settings and active connections; can you post the results of the query below?

    select

    case when serverproperty('IsIntegratedSecurityOnly') = 0 then 'SQL' else 'Windows' end as security_setting

    , sum(case when coalesce(nt_username, '') <> '' then 1 else 0 end) as nr_of_windows_logins

    , sum(case when coalesce(nt_username, '') = '' then 1 else 0 end) as nr_of_sql_logins

    , count(*) as total_logins#

    from master..sysprocesses

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 14, 2013 at 5:49 pm

    #1658378

    security_setting nr_of_windows_logins nr_of_sql_logins total_logins#

    Windows 47 24 71

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • October 14, 2013 at 6:11 pm

    #1658380

    You might want to put this on the end of that query:

    where hostprocess<>''

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • October 14, 2013 at 6:17 pm

    #1658383

    Are they all 'sa'? Are they all low session_id? If you query sys.dm_exec_sessions, what do they show for 'IsUserProcess'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 14, 2013 at 6:27 pm

    #1658387

    None of them are 'sa', they are all a sql account 'red' using an odbc connection.

    here is one line from the select

    session_id login_time host_name program_name host_process_id client_version client_interface_name security_id login_name nt_domain nt_user_name status context_info cpu_time memory_usage total_scheduled_time total_elapsed_time endpoint_id last_request_start_time last_request_end_time reads writes logical_reads is_user_process text_size language date_format date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority row_count prev_error original_security_id original_login_name last_successful_logon last_unsuccessful_logon unsuccessful_logons group_id

    102 10/15/2013 8:25:37 AM xxxxSAPDCI01 R3B12 [1]unc rd ODBC 3660 6 ODBC 0x2D220E9B08C8284CB972665E2AC166CD red sleeping 0x 16 3 9 9 2 10/15/2013 8:25:46 AM 10/15/2013 8:25:46 AM 0 0 468 1 -1 us_english mdy 7 1 0 1 0 1 1 1 1 1 -1 0 0 0 0x010500000000000515000000CC98BBF9DA303149DE4CA1FFA2060000 xxxx\SAPServiceRED 2

    Apologies for the mess - not sure how would be the best way to represent this information here.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • October 15, 2013 at 12:48 am

    #1658407

    If I interpred your output correctly the value in column "original login" is xxxx\SAPServiceRED. This looks like a Windows account/AD-group to me. Can you check the value Red in column "login_name" is a really a login and not a credential?

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 15, 2013 at 12:55 am

    #1658409

    the 'red' is a sql account.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

    • Viewing 8 posts - 1 through 7 (of 7 total)

    You must be logged in to reply to this topic. Login to reply